Oracle Transactional Business Intelligence Idea Lab

Home› Oracle Analytics› Oracle Analytics Idea Labs› More Solutions Idea Labs› Oracle Transactional Business Intelligence Idea Lab

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

INSTR function missing in Logical SQL provided by OBI

Submitted
54
Views
5
Comments
Kiran Shenvi
Kiran Shenvi Rank 4 - Community Specialist
in Oracle Transactional Business Intelligence Idea Lab

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

1
Up
1 votes

Submitted · Last Updated

Comments

  • Rachel Martorelli-Oracle
    Rachel Martorelli-Oracle Rank 4 - Community Specialist

    You may use the following formula.

     

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

  • Kiran Shenvi
    Kiran Shenvi Rank 4 - Community Specialist

    Thanks Rachel.. this works perfect :-)

  • maharani_1983
    maharani_1983 Rank 1 - Community Starter

    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
    Rachel Martorelli-Oracle Rank 4 - Community Specialist

    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
    maharani_1983 Rank 1 - Community Starter

    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 ?