Oracle Analytics and AI

INSTR function missing in Logical SQL provided by OBI

Kiran Shenvi

Description

We are recently working on OBI and found that a powerful string function of Oracle SQL is missing in logical SQL of OBI

Use Case and Business Need

Hi All,

In OBI, we are missing the powerful function on Oracle SQL

INSTR(string, substring,position, appearance)

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions068.htm

The 4th parameter is the key to its success after the nth apperance

But in OBI, we have only 2 functions

a) LOCATE(substring,string,position) -> gives the index where the substring occurs and start searches after the nth position

b) POSITION(substring,string) -> gives the index where the substring occurs

But it would be really useful if Oracle implements the INSTR function as part of logical SQL

There is also some active question around this function

https://community.oracle.com/thread/2265190

Regards,

Kiran Shenvi

Original Idea Number: 7ddbd831e9

Find more posts tagged with

Submitted

Status: Submitted

Comments

Rachel Martorelli-Oracle

You may use the following formula.

evaluate ('instr(%1, %2, %3, %4)', "Requisition Primary Location"."Location Level1 Name", 'a', 1, 2)

Kiran Shenvi

Thanks Rachel.. this works perfect :-)

maharani_1983

Hi,

i tried to use the EVALUATE , but it keeps giving me an error

my evaluate syntax : EVALUATE('instr(%1, %2, %3, %4)', "- Line Details"."Line Description", 'a', 1, 2)

error message :

Formula syntax is invalid.
[nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. EVALUATE_SUPPORT_LEVEL inside NQSConfig.INI is not set to support EVALUATE. (HY000)
SQL Issued: SELECT EVALUATE('instr(%1, %2, %3, %4)', "- Line Details"."Line Description", 'a', 1, 2) FROM "General Ledger - Journals Real Time"
OK (Ignore Error)

Rachel Martorelli-Oracle

To use Evaluate it has to be enabled by your admin, it seems like you can't use it in your instance.

Best,

Rachel

maharani_1983

Hi Rachel,

thanks for the quick reply. Actually, i have the admin roles.. could you please let me know how to enable evaluate function in my instance ?