How to pass filter Exception date

Question

How to pass Exception date filter < DATE '2019-08-01' on below expression ?

EVALUATE('MAX(%4) KEEP (DENSE_RANK FIRST ORDER BY %1 DESC) OVER (PARTITION BY %2,%3,%5)' as double,"Document Details"."Exception Date","Segment1"."Segment Desc1","Item"."Item Number","Document Details"."List Price","Item"."Item category")

Thank you,

Steel.

Steelbird · Answer

Thank you @Jerry Casey, I have removed the Null and it is not populating duplicate columns.

Jerry Casey · Answer

The null values are the result of the ELSE clause that I used as an example.  You can substitute whatever is appropriate for your requirements in the ELSE  clause.

I may have misinterpreted your original question, which makes this a bad approach.

Can you just apply the date filter to the query itself?

Steelbird · Answer

Hi @Jerry Casey

I have added the same case condition and noticed that it is causing null values in data, I tried to keep filter as List price is not null where it is giving error Oracle Error code: 934, message: ORA-00934: group function is not allowed here at OCI call OCIStmtExecute.

It is populating duplicate values will null in List price column.

Is there anyway that I can restrict the case condition not to pass Null values to report.

I have implemented in this way, I kept filter on Product number and checked the List price column without Case condition, the result it is populating as without null values,

O/P:

List Price column is the calculated column which it this formula: EVALUATE('MAX(%4) KEEP (DENSE_RANK FIRST ORDER BY %1 DESC) OVER (PARTITION BY %2,%3,%5)' as double,"Document Details"."Exception Date","Segment1"."Segment Desc1","Item"."Item Number","Document Details"."List Price","Item"."Item category")

Thank you,

Steel