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 result1ELSE 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 foroccurrence |
| 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) |
