0

Hi I'm doing a dimensional modeling exercise in college and I'm trying to convert the total_price entry in two of the database tables to euro so they will be in the dimensional table as euro. The two tables are tournament1 and tournament2 and the dimensional table is dim_tournament. Here is the code I am trying to run:

insert into Dim_tournament (Tournament_SK, total_price, Tournament_DESC)
Select 
  seq_Tournament.nextVal, 
  total_price, 
  t_descriprion  
From (
       select 
         t_id, 
         cast(to_char(total_price, 'C9,999,999.00')as integer), 
         t_descriprion 
       from Tournament1
       union all
       select 
         t_id, 
         cast(to_char(total_price, 'C9,999,999.00')as integer), 
         t_descriprion from Tournament2
     );

This is the error I keep getting when running this:

Error report -
SQL Error: ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

Any help would be very much appreciated.

2
  • I don't know Oracle, but it looks like you're converting/formatting total_price to a currency string ("$5.99"). If that is true then the currency symbol is probably your problem. – Erik Dec 12 '15 at 0:06
  • Is this query running finr? Select seq_Tournament.nextVal, total_price, t_descriprion From ( select t_id, cast(to_char(total_price, 'C9,999,999.00')as integer), t_descriprion from Tournament1 union all select t_id, cast(to_char(total_price, 'C9,999,999.00')as integer), t_descriprion from Tournament2 ); – Durga Viswanath Gadiraju Dec 12 '15 at 1:55
1

This:

cast(to_char(total_price, 'C9,999,999.00')as integer),

is wrong.

Assuming total_price is defined as number the result of converting e.g. 1234.56 will be:

EUR1,234.56 

(the exact output depends on your NLS settings).

Now the string EUR1,234.56 is not a valid number any more (at least not to a computer). So your cast to an integer fails.

If you only want to display the total price as a formatted string, only to_char() is required. Remove the cast()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.