Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

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

Received Response
36
Views
4
Comments
Anjali_Kumari4121
Anjali_Kumari4121 Rank 2 - Community Beginner
in Oracle Transactional Business Intelligence

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.

Answers

  • Rajasekhar Bandaru-Oracle
    Rajasekhar Bandaru-Oracle Rank 6 - Analytics Lead

    Hi

    Please try to use the cast() function as below

    Sample Query

    ===========

    select cast(result_value as integer) from PAY_RUN_RESULT_VALUES

    For any further information, Please follow up your query in this post
    https://community.oracle.com/products/oracleanalytics/categories/otbi

  • Anjali_Kumari4121
    Anjali_Kumari4121 Rank 2 - Community Beginner

    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.

  • Nathan CCC
    Nathan CCC Rank 7 - Analytics Coach

    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
    Anjali_Kumari4121 Rank 2 - Community Beginner

    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.