Site navigation (main menu).

Blog

Oracle SQL Functions in OBI Answers

For those of you who use OBI Answers, you may have noticed that when defining column formulae in requests that the standard Oracle database function set is not used. This is likely to be a throwback to the pre-Oracle origins of the product, but it is most frustrating for those of us with a SQL background solely in the Oracle world.

Editing a Column Formula in OBI Answers

I have therefore put together the following table that compares some most-used Oracle SQL functions and their counterparts in OBI Answers.

Oracle SQLOBI Answers SQL equivalent
NVL(expr1,expr2) IFNULL(expr1,expr2)
DECODE(expr,search1,result1,default) CASE expr WHEN search1 THEN result1 ELSE default END
TO_CHAR(expr) CAST(expr AS CHAR)
TO_NUMBER(expr) CAST(expr AS INTEGER)
TO_DATE(expr,’DD-MON-YY’) CAST(expr AS DATE)
SUBSTR(expr, startPos, length) SUBSTRING(expr, startPos, length)
INSTR(expr,substring,startPos,occurrence) LOCATEN(substring, expr, startPos)Note: Not possible to search for occurrence
SUBSTR(expr, length)When length is negative it forces the function to start from the right of expr, hence the start position is not required RIGHT(expr, length)
SUBSTR(expr, startPos, length)Where startPos = 1 LEFT(expr, length)
INITCAP(expr) None
LTRIM(expr, unwanted)e.g. LTRIM(expr,’?!* ‘) TRIM(LEADING unwanted FROM expr)
RTRIM(expr, unwanted)e.g. RTRIM(expr,’?!* ‘) TRIM(TRAILING unwanted FROM expr)

Please let me know if there are any more frequently used functions that you would like to be included.

19 Apr, 2010 by

Business Intelligence

2 thoughts on: “Oracle SQL Functions in OBI Answers”

  1. Christien July 21, 2010 9:29 AM

    Hi,

    We use also BI Answers, I just did a SQL study, but I find it difficult to translate the several functions (GROUP BY, HAVING etc.) in BI Answers. When I use a filter for instance, which function do I use.

    So, I am interested in the translation of SQL to BI-answer, can You help me?

    Kind regards,

    Christien Breukink

  2. Marc McGurk August 5, 2010 2:56 PM

    Hi Christien, thanks for the question, there isn’t, to my knowledge, an extensive translation list and yes, it can be very confusing at times.

    If you can provide an example of the SQL/filter/report that you’re having an issue with, we’ll be happy to help your further.

    thanks

Comments are now closed... Please contact us if you have any queries.