Cannot function ship the following expression: Evaluate — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

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

Cannot function ship the following expression: Evaluate

Received Response
36
Views
6
Comments
2844292
2844292 Rank 4 - Community Specialist
edited Aug 13, 2024 7:02PM in Oracle Analytics Cloud and Server

Hi Experts ,

I want to generate a report using two oracle databases, am able to create report using two table between two different oracle databases , but one of my logical column is using the Evaluate function , while calling the evaluate function am getting below error

Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 42015] Cannot function ship the following expression: Evaluate( NVL(%1,%2),Evaluate( DB function,) , D904.c13) .

Please some one suggest how to fix this error .

Thanks in advance

Answers

  • 2844292
    2844292 Rank 4 - Community Specialist

    But same Evaluate function Logical column been used for some other reports and those are working fine.

    When i use the Evaluate logical column for one report which is basically using two tables between two oracle databases that report causing the issue as

    Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 42015] Cannot function ship the following expression: Evaluate( NVL(%1,%2),Evaluate( DB function,) , D904.c13) .

    Please suggest how to fix this issue . Is there any another way that i can call the Evaluate function ?

    Please suggest. this is the show stopper issue, need to be resolved .

    Thanks in advance for your suggestions

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    Hello,

    Try to understand your problem, you say the you have two different tables in two different databases, and you have an evaluate function in a logical column, with this formula


    Evaluate( NVL(%1,%2),Evaluate( DB function,) , D904.c13)

    And you get the error, so you have a nested function database NVL() and inside of this DB FUNCTION, .

    This logical column map for both two differents tables which are in two different database? are you sure that you have "DB FUNCTION" in both databases?

    However, I try to guess your scenario, give me more detail about it, to help you.

    Kind Regards,

  • 2844292
    2844292 Rank 4 - Community Specialist

    Hi ,

    Thank you for your response . Here my doubt is why i need to have the DB function in two databases ??

    DB function exist at one data base using this database table i derived the my Evaluate expression and my evaluate function is valid,which is working fine.

    I just want one column from Data base B and another column which is evaluate expression from Data base A. When am trying to create report with these two columns from Database A and B am getting error .

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    Hello,

    Please, we should understand something, oracle bi make a query based in your connection pool.

    EVALUATE is a particular function which takes the function of your databases, however, if you are using an EVALUATE FUNCTION for both databases (one existe in the other not exist),

    oracle doesnt find this function and give your issue, check your physical query, set up log level 2 and analyze your physical query, in other hand, if you want more help copy and paste here your physical query.

    Kind Regards,

  • 2844292
    2844292 Rank 4 - Community Specialist

    Error :

    [2016-04-13T03:39:29.000-07:00] [OracleBIServerComponent] [TRACE:7] [USER-34] [] [ecid: 08b36aff96406675:75e2d056:1538086e548:-8000-000000000024923f,0:1:9:5] [tid: 55fe0940] [requestid: 396d001c] [sessionid: 396d0000] [username: weblogic] -------------------- Query Status: [nQSError: 42015] Cannot function ship the following expression: Evaluate( %1,'XXXX.XXXXX_PKG.GET_ORGID') . [[

    Physical Log File:

    [2016-04-13T03:39:29.000-07:00] [OracleBIServerComponent] [TRACE:7] [USER-0] [] [ecid: 08b36aff96406675:75e2d056:1538086e548:-8000-000000000024923f,0:1:9:3] [tid: 55fe0940] [requestid: 396d001c] [sessionid: 396d0000] [username: weblogic] ############################################## [[

    -------------------- SQL Request, logical request hash:

    ccd355a0

    SET VARIABLE QUERY_SRC_CD='Report',SAW_SRC_PATH='/users/weblogic/Manufacturing/without nvl';SELECT

      0 s_0,

      "Manufacturing"." Lines"."Line Status" s_1,

      "Manufacturing"."Lines"."Pkg New" s_2,

      "Manufacturing"." Lines"."Org Id" s_3

    FROM "Manufacturing"

    ORDER BY 1, 3 ASC NULLS LAST, 2 ASC NULLS LAST, 4 ASC NULLS LAST

    FETCH FIRST 100001 ROWS ONLY

    ]]

    [2016-04-13T03:39:29.000-07:00] [OracleBIServerComponent] [TRACE:7] [USER-23] [] [ecid: 08b36aff96406675:75e2d056:1538086e548:-8000-000000000024923f,0:1:9:3] [tid: 55fe0940] [requestid: 396d001c] [sessionid: 396d0000] [username: weblogic] -------------------- General Query Info: [[

    Repository: Star, Subject Area: Manufacturing, Presentation: Manufacturing

    ]]

    [2016-04-13T03:39:29.000-07:00] [OracleBIServerComponent] [TRACE:7] [USER-2] [] [ecid: 08b36aff96406675:75e2d056:1538086e548:-8000-000000000024923f,0:1:9:3] [tid: 55fe0940] [requestid: 396d001c] [sessionid: 396d0000] [username: weblogic] -------------------- Logical Request (before navigation): [[

    RqList distinct

      0 as c1 GB,

      Mlc Iot Lines Dim. Status as c2 GB,

      Mlc Iot Lines Dim.Pkg New as c3 GB,

      Mlc Iot Lines Dim.Org Id as c4 GB

    OrderBy: c1 asc, c3 asc NULLS LAST, c2 asc NULLS LAST, c4 asc NULLS LAST

    ]]

    [2016-04-13T03:39:29.000-07:00] [OracleBIServerComponent] [TRACE:7] [USER-53] [] [ecid: 08b36aff96406675:75e2d056:1538086e548:-8000-000000000024923f,0:1:9:5] [tid: 55fe0940] [requestid: 396d001c] [sessionid: 396d0000] [username: weblogic] -------------------- List of attributes and their LTS sources: [[

    Column Mlc Iot Lines Dim.Line Status rendered via fact LTS [Logical table sources (Priority=0): ]

    ]]

    [2016-04-13T03:39:29.000-07:00] [OracleBIServerComponent] [TRACE:7] [USER-48] [] [ecid: 08b36aff96406675:75e2d056:1538086e548:-8000-000000000024923f,0:1:9:5] [tid: 55fe0940] [requestid: 396d001c] [sessionid: 396d0000] [username: weblogic] -------------------- The logical query block fail to hits or seed the cache in subrequest level due to [[

    only one subrequest

    ]]

    [2016-04-13T03:39:29.000-07:00] [OracleBIServerComponent] [TRACE:7] [USER-53] [] [ecid: 08b36aff96406675:75e2d056:1538086e548:-8000-000000000024923f,0:1:9:5] [tid: 55fe0940] [requestid: 396d001c] [sessionid: 396d0000] [username: weblogic] -------------------- List of attributes and their LTS sources: [[

    Select: Lines Dim.Line Status: Logical table sources (Priority=0): Mlc Iot Lines Dim.MLC_IOT_LINES2

    Select:  Lines Dim.Org Id: Logical table sources (Priority=0): Mlc Iot Lines Dim.MLC_IOT_LINES2

    Select:  Lines Dim.Mlc  Pkg New: Logical table sources (Priority=0): Mlc Iot Lines Dim.MLC_IOT_LINES2

    ]]

    [2016-04-13T03:39:29.000-07:00] [OracleBIServerComponent] [TRACE:7] [USER-51] [] [ecid: 08b36aff96406675:75e2d056:1538086e548:-8000-000000000024923f,0:1:9:5] [tid: 55fe0940] [requestid: 396d001c] [sessionid: 396d0000] [username: weblogic] -------------------- The logical plan contains non-cacheable node, plan [[

    RqList <<258535>> distinct

      0 as c1 GB,

      D1.c1 as c2 GB,

      D1.c2 as c3 GB,

      D1.c3 as c4 GB

    Child Nodes (RqJoinSpec): <<258578>>

      RqJoinNode <<258577>> []

      (

      RqList <<258542>>

      MLC_IOT_LINES2.LINE_STATUS as c1 GB,

      Evaluate( %1,'XXXX_XXXX_.GET_ORGIDN') as c2 GB,

      MLC_Order_Fulfill_Measure_V2_Fact.ORG_ID as c3 GB

      Child Nodes (RqJoinSpec): <<258565>>

      RqJoinNode <<258564>> []

      XXXXX_V2 AS MLC_Order_Fulfill_Measure_V2_Fact LeftOuterJoin <<258553>> On XXXXX_V2_Fact.LINE_ID = XXXXX.LINE_ID

      XXXXX_LINES2 T1174081

      ) as D1

    OrderBy: c1 asc, c3 asc NULLS LAST, c2 asc NULLS LAST, c4 asc NULLS LAST

    node Evaluate( %1,'XXXXX_XXXXX_PKG.GET_ORGIDN')

    ]]

    [2016-04-13T03:39:29.000-07:00] [OracleBIServerComponent] [TRACE:7] [USER-50] [] [ecid: 08b36aff96406675:75e2d056:1538086e548:-8000-000000000024923f,0:1:9:5] [tid: 55fe0940] [requestid: 396d001c] [sessionid: 396d0000] [username: weblogic] -------------------- The logical query seed the plan cache [[

    plan

    RqList <<258677>> [for database 0:0,0] distinct /* FETCH FIRST 100001 ROWS ONLY */

      0 as c1 GB [for database 0:0,1],

      D1.c1 as c2 GB [for database 3023:1077247,57],

      D1.c2 as c3 GB [for database 0:0,0],

      D1.c3 as c4 GB [for database 3023:1160583,57]

    Child Nodes (RqJoinSpec): <<258687>> [for database 0:0,0]

      RqJoinNode <<258688>> []

      (

      RqList <<258691>> [for database 0:0,0]

      D903.c1 as c1 GB [for database 3023:1077247,57],

      Evaluate( %1,'XXXXX_XXXXXX_PKG.GET_ORGIDN') as c2 GB [for database 0:0,1],

      D903.c2 as c3 GB [for database 3023:1160583,57]

      Child Nodes (RqJoinSpec): <<258700>> [for database 0:0,0]

      RqJoinNode <<258701>> []

      (

      RqList <<258704>> [for database 0:0,0]

      D902.c1 as c1 GB [for database 3023:1077247,57],

      D901.c1 as c2 GB [for database 3023:1160583,57]

      Child Nodes (RqJoinSpec): <<258710>> [for database 0:0,0]

      RqJoinNode <<258711>> []

     

      (

      RqList <<258715>> [for database 3023:1160583:Manufacturing,57]

      XXXXX_V2_Fact.ORG_ID as c1 GB [for database 3023:1160583,57],

      XXXXXX_V2_Fact.LINE_ID as c2 [for database 3023:1160583,57]

      Child Nodes (RqJoinSpec): <<258721>> [for database 3023:1160583:Manufacturing,57]

      RqJoinNode <<258722>> []

      XXXXX_MEASURES_V2 AS MLC_Order_Fulfill_Measure_V2_Fact

      OrderBy: c2 asc [for database 3023:1160583,57]

      ) as D901 LeftOuterJoin <<258712>> On D901.c2 = D902.c2; actual join vectors: [ 1 ] = [ 1 ]

     

      (

      RqList <<258732>> [for database 3023:1077247:MLC_WH_DB,57]

      XXX.LINE_STATUS as c1 GB [for database 3023:1077247,57],

      XXXX.LINE_ID as c2 [for database 3023:1077247,57]

      Child Nodes (RqJoinSpec): <<258738>> [for database 3023:1077247:MLC_WH_DB,57]

      RqJoinNode <<258739>> []

      MLC_IOT_LINES2 T1174081

      OrderBy: c2 asc [for database 3023:1077247,57]

      ) as D902

      ) as D903

      ) as D1

    OrderBy: c3 asc NULLS LAST, c2 asc NULLS LAST, c4 asc NULLS LAST [for database 0:0,0]

    ]]

    [2016-04-13T03:39:29.000-07:00] [OracleBIServerComponent] [TRACE:7] [USER-16] [] [ecid: 08b36aff96406675:75e2d056:1538086e548:-8000-000000000024923f,0:1:9:5] [tid: 55fe0940] [requestid: 396d001c] [sessionid: 396d0000] [username: weblogic] -------------------- Execution plan: [[

    RqList <<258535>> [for database 0:0,0] distinct /* FETCH FIRST 100001 ROWS ONLY */

      0 as c1 GB [for database 0:0,1],

      D1.c1 as c2 GB [for database 3023:1077247,57],

      D1.c2 as c3 GB [for database 0:0,0],

      D1.c3 as c4 GB [for database 3023:1160583,57]

    Child Nodes (RqJoinSpec): <<258578>> [for database 0:0,0]

      RqJoinNode <<258577>> []

      (

      RqList <<258542>> [for database 0:0,0]

      D903.c1 as c1 GB [for database 3023:1077247,57],

      Evaluate( %1,'XXXXX_XXXXX_PKG.GET_ORGIDN') as c2 GB [for database 0:0,1],

      D903.c2 as c3 GB [for database 3023:1160583,57]

      Child Nodes (RqJoinSpec): <<258589>> [for database 0:0,0]

      RqJoinNode <<258640>> []

      (

      RqList <<258623>> [for database 0:0,0]

      D902.c1 as c1 GB [for database 3023:1077247,57],

      D901.c1 as c2 GB [for database 3023:1160583,57]

      Child Nodes (RqJoinSpec): <<258626>> [for database 0:0,0]

      RqJoinNode <<258622>> []

     

      (

      RqList <<258593>> [for database 3023:1160583:Manufacturing,57]

      XXXXXXX_V2_Fact.ORG_ID as c1 GB [for database 3023:1160583,57],

      XXXXXX_V2_Fact.LINE_ID as c2 [for database 3023:1160583,57]

      Child Nodes (RqJoinSpec): <<258596>> [for database 3023:1160583:Manufacturing,57]

      RqJoinNode <<258592>> []

      XXXXXXXX  AS XXXXXXX_V2_Fact

      OrderBy: c2 asc [for database 3023:1160583,57]

      ) as D901 LeftOuterJoin <<258553>> On D901.c2 = D902.c2; actual join vectors: [ 1 ] = [ 1 ]

     

      (

      RqList <<258606>> [for database 3023:1077247:MLC_WH_DB,57]

      XXXXXX.LINE_STATUS as c1 GB [for database 3023:1077247,57],

      XXXXXX.LINE_ID as c2 [for database 3023:1077247,57]

      Child Nodes (RqJoinSpec): <<258609>> [for database 3023:1077247:MLC_WH_DB,57]

      RqJoinNode <<258605>> []

      MLC_IOT_LINES2 T1174081

      OrderBy: c2 asc [for database 3023:1077247,57]

      ) as D902

      ) as D903

      ) as D1

    OrderBy: c3 asc NULLS LAST, c2 asc NULLS LAST, c4 asc NULLS LAST [for database 0:0,0]

    ]]

    [2016-04-13T03:39:29.000-07:00] [OracleBIServerComponent] [TRACE:7] [USER-34] [] [ecid: 08b36aff96406675:75e2d056:1538086e548:-8000-000000000024923f,0:1:9:5] [tid: 55fe0940] [requestid: 396d001c] [sessionid: 396d0000] [username: weblogic] -------------------- Query Status: [nQSError: 42015] Cannot function ship the following expression: Evaluate( %1,'XXXX_XXXX_PKG.GET_ORGIDN') . [[

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    Hello,

    I cant see your physical query, section query to the database.

    Check:

    physical.png

    r1.png

    Reference link:

    http://gerardnico.com/wiki/dat/obiee/manage_session_log

    Despite of this, I try to guess:

    You want to get the ORG_ID , for that reason,

    you use this function "Evaluate( %1,'XXXXX_XXXXX_PKG.GET_ORGIDN')",you pass only one parameter, and only exist in a only one database (Database A).

    Right here, in the logical column map to different 2 physical tables ( Table 1 in Database A and Table 2 in Database B), if this scenario you have in your RPD, let me response you.


    OBIEE EVALUATE FUNCTION, go through your database and you could use the function that you have in your DB, so if you use for example:


    1. First Case: You choose in your Oracle BI Answers some criteria wich this column go to EVALUATE () function (which is in database A) and  Table1 (which is in database A) , here there is no problem.

    2. Second Case: You choose in your Oracle BI Answers some criteria wich this column go to EVALUATE () function (which is in database A) and  Table1 (which is in database B) , here there is  the issue, cus you OBIEE try to search your function in your DATABASE B, and it doesnt exist.

    Kind Regards,