Oracle Analytics Cloud and Server

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

Categories

Reg: LEAST and GREATEST function in OBIEE

Received Response
312
Views
6
Comments
User_JZKI6
User_JZKI6 ✭✭✭
edited August 13 in Oracle Analytics Cloud and Server

Hi Team,

We are using LEAST() and GREATEST() functions between 2 dates, for calculating date field in DB level need to do same logic in RDP/Report level.

Logic:

Case when to_char(LEAST(DATE1, DATE2),'YYYY') < 2018

then GREATEST(DATE1, DATE2) else LEAST(DATE1,DATE2) end as New Date

Can someone help how to use LEAST and GREATEST function in OBIEE RPD/Report level.

Version we are using: Oracle Business Intelligence 12.2.1.4.0

Thanks in Advance

Tagged:

Answers

  • Christian Berg-0racle
    Christian Berg-0racle ✭✭✭✭✭

    The OBI RPD works on the concept of "lowest common denominator" when it comes to functionality. It only has the functions built in which are supported by pretty much all sources you can attach to an RPD.

    If you want to use a specific database function that's not covered out of the box by the RPD, then please use the EVALUATE functionality.

    https://datacadamia.com/dat/obiee/obis/logical_sql/evaluate

  • Gianni Ceresa
    Gianni Ceresa mod

    What Christian said, or ... simple logic: what does the piece of code you posted do in your database?

    It's extremely simple and you can get the same result with some logical conditions and using '>' or '<' .

  • Christian Berg-0racle
    Christian Berg-0racle ✭✭✭✭✭

    Or that ;)

  • User_JZKI6
    User_JZKI6 ✭✭✭


    Thank you all for the response.

    We are able to find LEAST and GREATEST dates using evaluate function as below

    EVALUATE('LEAST(%1,%2)', "Task"."Date1","TasK"."Date2")

    EVALUATE('GREATEST(%1,%2)', "Task"."Date1","TasK"."Date2")

    But not able to extract year from evaluate LEAST function and compare with 2018 any help on this part.

    Case when to_char(LEAST(DATE1, DATE2),'YYYY') < 2018

    then GREATEST(DATE1, DATE2) else LEAST(DATE1,DATE2) end as New Date


    Thanks in Advance,

  • Christian Berg-0racle
    Christian Berg-0racle ✭✭✭✭✭

    Year(EVALUATE('GREATEST(%1,%2)', "Task"."Date1","TasK"."Date2")) will to the trick if "Task"."Date1" and "Task"."Date2" are real date data types.

  • Gianni Ceresa
    Gianni Ceresa mod

    The key point of Christian's reply is: you aren't writing a query or a view in your database, you are writing an expression in OBIEE. You should speak the OBIEE language as much as possible, or you are just wasting the money you spent on the tool and you could have stayed with SQL queries directly.

    TO_CHAR(<date>, 'YYYY') in SQL === YEAR(<date>) in OBIEE

    And you can do the same job for GREATEST and LEAST!

    If you write OBIEE expressions, the analysis/RPW will always work whatever is your source. Today you are using an Oracle database. What happen if tomorrow your datawarehouse is moved to SQL Server or another? All the places where you used EVALUATE(...) will not work anymore.

    Are you really supposed to write code that work only today knowing that it will take the same time to write OBIEE code instead and it will work whatever is your source?

    You aren't adopting the ideal approach in your usage of OBIEE, you are just trying to reproduce some SQL work without using all the tool can do.