How can we convert a String data type to number in BI/OTBI report

Question

We have a column (RESULT_VALUE) data type as string  from PAY_RUN_RESULT_VALUES table , our requirement is to put the condition on  RESULT_VALUE >1000. For achieving this condition we are trying to convert the RESULT_VALUE from string to number.

I tried TO_NUMBER(RESULT_VALUE)  to convert into number but it's not working.

Please suggest how we can convert a string to number to get the above condition.

Anjali_Kumari4121 · Answer

Hi Nathan CCC ,

In the query I am putting another condition PAY_INPUT_VALUES_TL.name = 'Amount' to fetch the amount from the column RESULT_VALUE from PAY_RUN_RESULT_VALUES table , I am expecting RESULT_VALUE is holding number in string data type and we could convert that string into number but it's not working.

Nathan CCC · Answer

Hi Anjali, That is the expected result if some of your values are not numbers! You can only convert a string to a number if the value in the string is in fact a number. If some of the rows have values in the string that are not numbers then you expect the system to throw this error.

Anjali_Kumari4121 · Answer

Hi Rajasekhar Bandaru-Oracle ,

Thanks for the response!

I tried this but when I am trying to see the data , getting below error as ORA-01722: invalid number.