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.
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.
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
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