Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

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

Extract the ] delimiter value using Evaluate function in OBIEE RPD and Analytics

Received Response
43
Views
4
Comments
SudheerK
SudheerK Rank 1 - Community Starter
in Oracle Analytics Cloud and Server

Hi All,

I Have below field called amount . Requirement is to extract each amount value in seperate columns like amount1,amount2,amount3…..etc

2686.319]2771.295]2601.976]2805.035]2721.763]2507.761
663.666]663.666]663.666]663.666]663.666]663.666
785.400]785.400]785.400]785.400]785.400]785.400
1569.180]1569.180]1569.180]1569.180]1569.180]1519.180
963.290]963.290]963.290]963.290]963.290]963.290
374.490]374.490]374.490]374.490]374.490]374.490
316.601]307.600
493.387]495.000]495.000]495.000]489.500]495.000
1361.170]1373.100]1351.390]1343.040]1376.440]1383.120
623.220]623.220

Need to extract the each value by using evaluate function in OBIEE RPD and analytics. Tried substr and instr and its not working.

Please provide inputs/suggestions on the same.

Thanks & Regards,

Answers

  • Ram-Oracle
    Ram-Oracle Rank 6 - Analytics Lead

    Hi @SudheerK As you already tried substr and instr ,could you also try regular expression substring function (
    REGEXP_SUBSTR)
    with in evaluate and see if it helps

    https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/REGEXP_SUBSTR.html

  • Gianni Ceresa
    Gianni Ceresa Mod
    edited Jan 29, 2025 9:43AM

    Hi,

    Tried substr and instr and its not working.

    What did you try exactly? Any particular reason for wanting to use EVALUATE?

    In simple SQL, with SUBSTR and INSTR you can do the job, adding some extra logic around it to manage boundaries.

    Also, if you have a fixed, constant number of "]" per row, it will be a lot simpler. If the number of "]" is dynamic and change row by row, it will take some extra conditional logic.

    All in all, using EVALUATE for this will be really poor to maintain. You could consider an opaque view in the RPD instead, it will at least keep the whole SQL query in a single place. And from a performance point of view, doing it in your ETL or database directly (maybe with a materialized view), will be the best for performance of your queries.

  • SudheerK
    SudheerK Rank 1 - Community Starter

    I have tried below in OBIEE RPD and ANalytics to get the Month1 and Month2 amount values

    --SUBSTRING("Core"."AMT" FROM 0 FOR cast(EVALUATE('instr(%1,%2)' , "Core"."AMT",']')as int)-1)--Month1
    --SUBSTRING("Core"."AMT" FROM 9 FOR cast(EVALUATE('instr(%1,%2,%3)',"Core"."AMT",']',2)as int))--Month2

    its working for to get the 1st and 2nd values but whereas while trying same logic for other values…its not giving results as expected.

    any further inputs extend to this.

  • Gianni Ceresa
    Gianni Ceresa Mod

    Ok, first thing: you can get rid of EVALUATE, you don't need it.

    The strange thing is that you have an hardcoded value for the position where to start the SUBSTRING for Month2, does this mean that you know the exact position of all the ']'? In that case remove EVALUATE and just enter the numbers directly.

    There is a LSQL function doing the same job as the SQL INSTR function: Locate(<search string>, <source string>, <start position>)

    This one will let you make a full LSQL dynamic formula (but as said: if your sizes are fixed, then enter those numbers instead of calling functions for no reason). Because LOCATE take as 3rd parameter the position from where to start looking, you will need to call it multiple time as parameter for LOCATE itself to move from the first ] to the second ], to the third ] etc.

    Again: if you do it with SQL, it's a lot easier, but real SQL, not just pieces of SQL added in many places with EVALUATE. I would highly suggest you consider an opaque view in your RPD (the "Table type" = "Select") or as a real database object.