I unable to filter the report on EVALUATE function shown below
CAST((Evaluate ('work_days_between_ADS (min(%1 ), max(%2))' as INT, "Order Attributes"."Booked Date" , "Line Level Attributes"."Line Status Date" )) AS INTEGER) >29
above code is one column out of about 20 columns and report is working fine without filter but I want to see the data with work_days >29
I got below error with above filter condition.
View Display Error
Odbc driver returned an error (SQLExecDirectW).
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 17001] Oracle Error code: 604, message: ORA-00604: error occurred at recursive SQL level 1 ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 8 ORA-00934: group function is not allowed here at OCI call OCIStmtExecute. [nQSError: 17010] SQL statement preparation failed. (HY000)
If anyone of you know solution for the above please post it here. appreciate for your help
Edited by: JV123 on May 9, 2013 9:24 AM
As you are getting an ORA- error, there is nothing wrong with the OBIEE report or with the fact that the you have used Evaluate in the filters.
This seems like an error while parsing a particular row value for the parameters (Booked Date and Line Status Date) you are passing to the work_days_between_ADS function. Pick up the physical sql and fire it directly on the database and try to identify what values are causing the error (probably at line 8 of the function def).
with error OBI doesn't generate SQL. anyway instead of using DB function I'm trying to incorporate database function functionality within the repository that way I can overcome the issue.