Complex filter issue. How do I do this

Question

We are trying to convert our OAS reports to OAC and one of the parameters in the where clause of the report is what I am trying to make in OAC. I created a calculation  as follows and I am trying to put it into a filter . The code validates

EVALUATE('GREATEST (

TRUNC (

NVL (%1,

TO_DATE (''1/1/2013'', ''mm/dd/yyyy'')

)

),

TRUNC (

NVL (%2, TO_DATE (''1/1/2013'', ''mm/dd/yyyy'')

)

) >= TO_DATE (''1/1/2024'', ''mm/dd/yyyy'')

)' AS DATE , Last Activity Date", MAXDT")

and get the following error

Error during query processing (SQLExecDirectW).

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.

(HY000)

Chere-Oracle · Answer

Hi User_SYYDG,

The formula within single quotes has an extra closing parenthesis, and the column names have closing double quotes but no opening double quotes.  Any of these problems would cause an error, but I am also wondering about using EVALUATE() to evaluate the result of a condition to TRUE or FALSE — this might not work.  You might want to try this instead:

EVALUATE('GREATEST (
TRUNC (
NVL (%1,
TO_DATE (''1/1/2013'', ''mm/dd/yyyy'')
)
),
TRUNC (
NVL (%2, TO_DATE (''1/1/2013'', ''mm/dd/yyyy'')
)
)
)' AS DATE , "Last Activity Date", "MAXDT") >= DATE'2024/01/01'

But why not simplify it as . . .

IFNULL(CAST(CASE WHEN "Last Activity Date" > "MAXDT" THEN "Last Activity Date" ELSE "MAXDT" END AS DATE), DATE'2013/01/01') >= DATE'2024/01/01'

. . . so as not to send the computation to the database.

User_SYYDG · Answer

I am not using the Classic version of OAC. I am trying to do this complex parameter in my workbook

Gianni Ceresa · Answer

Hi,

Welcome to the Oracle AI and Analytics community.

Just to be sure on the "pieces" involved, when you say converting from OAS to OAC, do you mean converting a "classic" analysis (you have /analytics in the URL) to a DV workbook (you have /dv in the URL) ?

Because OAS and OAC have mostly the same features, therefore your "reports" wouldn't need any conversion…

How was your complex filter expressed, defined, in the source?