Cannot function ship the following expression: EVALUATE.

Sonu agarwal

When the env is upgraded fom 12.1.1.4 to OAS 7 , some reports are failing due to evaluate function not working. The same report is working fine in OBIEE 12c but not working in OAS 7.

Error Codes: OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P. State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.

State: HY000. Code: 43275. [nQSError: 43275] Message returned from OBIS [ecid:995efb47-134f-452e-9da5-7ee96d7e30e1-000b880a,0:1:2:6 ts:2023-09-12T16:45:40.113-04:00].

State: HY000. Code: 43119. [nQSError: 43119] Query Failed: State: HY000. Code: 42015. [nQSError: 42015] Cannot function ship the following expression: EVALUATE.

Error Codes: OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P

This report have some dimensions and some case statements with evaluate in the column logics. What aggregation settings or Case statements enforcements or evaluate settigns are changed in OAS ?

Product version using : OAS 7, let me know if any other information is required?

Case logic that seems failing:

CASE  WHEN     CAST(COUNT(DISTINCT "Journals"."JE Header ID" by "- DFF - Recurring Journals"."Recurring Journal ID") AS INT) > 1   THEN 'Y'  

WHEN MAX(TIMESTAMPADD(SQL_TSI_HOUR,-5, "Journals"."Journal Posted Date")) < EVALUATE('trunc(last_day(add_months(sysdate, -1)) +1)')     THEN   

  case when     CAST(evaluate('XXPRUWH.SPGETGLBUSINESSDAYS(%1)',MAX(TIMESTAMPADD(SQL_TSI_HOUR, -5, "Journals"."Journal Posted Date"))) AS INT)     -(CAST(evaluate('XXPRUWH.SPGETGLBUSINESSDAYS(%1)',EVALUATE('last_day(add_months(sysdate, -1))')) AS INT))       > CAST("- DFF - Recurring Journals"."Expected Business Day" AS INT)   THEN 'Y'    else 'N' end      when   CAST(evaluate('XXPRUWH.SPGETGLBUSINESSDAYS(%1)',MAX(TIMESTAMPADD(SQL_TSI_HOUR, -5, "Journals"."Journal Posted Date"))) AS INT)     >     CAST("- DFF - Recurring Journals"."Expected Business Day" AS INT)     THEN 'Y'   ELSE 'N'  END 

GayathriAnand-Oracle

Hi

You can use System Settings in OAS to set EVALUATE SUPPORT LEVEL parameter. Login to OAS >>Console >>System Settings and set EVALUATE SUPPORT LEVEL accordingly. Click Apply button for settings to take effect.

Thanks

Gayathri

Sonu agarwal

This is already done, other reports using evaluate are working, only some are failing.

PriyankaS-Oracle

Is the datasource used same in OBIEE vs OAS?

Sonu agarwal

yes the datasource is same, the upgrade was doen by Bar file migration.

I checked the database features also, they are exactly the same. I wonder if it was worth upgrading.

Gianni Ceresa

There can be many reasons for EVALUATE to not work...

I would say that you shouldn't abuse EVALUATE, you do have some usages that could be written with native LSQL functions, for example these:

EVALUATE('trunc(last_day(add_months(sysdate, -1)) +1)')
EVALUATE('last_day(add_months(sysdate, -1))')

Then the rest of your query seems quite complex mixing aggregates passed as attributes to a function or something like that.

Never forget that you could maybe also just build a view in your database for something that is abusing the model of your OAS. There are cases when your model (the RPD) covers 95% of the needs, but it simply doesn't have the right structure for the other 5%. In these cases you can either model alternative objects in your RPD better suited for the need, or just push it down in the database fully with a view.

Also keep in mind that when the tool is used in special ways, the tool could behave in an unpredictable way: it does work somehow and it gives you what you need, but it wasn't really designed to do it, it just happened. With the various versions the rules have been made stronger to avoid some of those abuse of the tool, but there is still lo of freedom that the tool allows.

SteveF-Oracle

Hi,

Have you broken down your case statement to the individual calls to isolate the issue?

The following looks suspect to me, can you elaborate the function call here, or you need evaluate for truncation (understanding it works in OBI 12c), it doesn't seem necessary

EVALUATE('trunc(last_day(add_months(sysdate, -1)) +1)')     THEN  

It would be interesting to understand the datasource, and the datasource feature flags set.

Would you also mind updating your profile with your name/organization?

Sarada_M-Oracle

Any progress on this?

Timothy Dickinson

I had this issue this last week!= [nQSError: 42015] Cannot function ship the following expression: EVALUATE. there has been a very recent upgrade BUT it was't the problem. The datatype on the dataset OAS was referencing in the EVALUATE function had changed from date to datetime and i the message is misleading - there are columns on the same report using EVALUATE function working fine. it was just this one particular column the report was erroring on due to the function not being able to work with the new datatype as it was written .I has to rejig the function and it worked ok.