6 Replies Latest reply on Apr 14, 2016 12:15 PM by cesar.advincula.o

    Cannot function ship the following expression: Evaluate

    2844292

      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

        • 1. Re: Cannot function ship the following expression: Evaluate
          2844292

          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

          • 2. Re: Cannot function ship the following expression: Evaluate
            cesar.advincula.o

            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,

            • 3. Re: Cannot function ship the following expression: Evaluate
              2844292

              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 .

              • 4. Re: Cannot function ship the following expression: Evaluate
                cesar.advincula.o

                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,

                • 5. Re: Cannot function ship the following expression: Evaluate
                  2844292

                  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') . [[

                  • 6. Re: Cannot function ship the following expression: Evaluate
                    cesar.advincula.o

                    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,