Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Kamran Butt
    Syntax Error While Using Evaluate in OTBI Analysis
    Topic posted September 29, 2019 by Kamran ButtRed Ribbon: 250+ Points, tagged BI Publisher, Fusion Financial reporting, OBI Answers, OBIEE Answers, OTBI, Public Sector 
    50 Views, 7 Comments
    Title:
    Syntax Error While Using Evaluate in OTBI Analysis
    Summary:
    Evaluate function generates a syntax error when using REGXP_SUBSTR
    Content:

    I am attempting to trim the following column in the General Ledger - Journals Real Time subject area: "- Account"."Code Combination Description". By default, this column will result in a value that has all 7 segments concatenated together with a period (ex. General Fund.City Commission.Legislative.Equipment Rental and Lease.Default.Default.Default).

    From this concatenated string I want to parse out the values between the 3rd and 4th periods (ex. Equipment Rental and Lease). 

    I attempted to use the EVALUATE function with REGXP_SUBSTR to parse out only the necessary information but I continuously receive a syntax error even if I follow the example shown on the screen. I verified my account privileges and I have permission to use the EVALUATE function.

    Formula: EVALUATE('REGEXP_SUBSTR(%1, ''[^.]+,1,3'')', AS VARCHAR "- Account"."Code Combination Description"

    Any hints would be helpful. Thanks!

     

    Version:
    Oracle Cloud 19C (11.13.19.07.0)
    Code Snippet:

    Comment

     

    • Michael Cook

      Hi Have you looked at reporting structure in General Ledger - Transaction Real Time (OTBI) and General Ledger - Balance Real Time (Essbase) to see if the Seperate COA values have been brought across from the Essbase cube for reporting and you could use those instead??

    • Marybeth Snodgrass

      Can you upload a pic or share the text of the error message?

    • nathan morgan (at Client)

      Kamran,

      The bad news... I assume that you cannot use evaluate with ANY of the subject areas? EVALUATE is a "dangerous" feature because it allows you to break free from logical sql syntax to use whatever you want from the source physical database. I think it is disabled by default with a new install to protect from sql injection attackes etc. The feature is turned on using a parameter in the oracle business intelligence server configuration file on the host application server called NQSConfig.INI. As this is cloud I expect that any request to turn it on will be rejected.

      The good news... you can use the standard logical sql functions to achieve your goal. you do not need to use physical sql with evaluate.

       

    • nathan morgan (at Client)

      for example,

      using logical sql functions substring and locate to get the second word in a dot separated hello world string ...

      select all substring(
      (substring( 'hola.mundo' from ((locate('.','hola.mundo'))+(1)) )) 
      from 1 for 5 )
      as "s" 
      from "General Ledger - Journals Real Time"

      for more information see


      Oracle® Fusion Middleware
      User's Guide for Oracle Business Intelligence Enterprise Edition
      11g Release 1 (11.1.1) E10544-08 December 2014
      D Logical SQL Reference
      https://docs.oracle.com/middleware/11119/biee/BIEUG/appsql.htm#CHDDCFJI

       

    • Michael Cook

      Oh dear,

      The other bad news is when you are upgraded to 19D and onwards the Create Direct Database Query will go so that closes another door for you.

      Sorry for the bad news.

      M