Invalid number error

oracle

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.

Best Answer

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()

Related Question