Oracle Business Intelligence

Products Banner

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

Received Response
14
Views
4
Comments

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.

Tagged:

Answers

  • Jerry Casey
    Jerry Casey ✭✭✭✭✭

    Hi Steel,

    Have you tried putting it in a case statement, such as:

    Case when "Document Details"."Exception Date"<DATE '2019-08-01' THEN 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") ELSE cast(NULL as CHAR) end

    The "ELSE cast(NULL as char)" clause should be replaced with whatever is appropriate for your data.

    Jerry

  • 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.

    pastedImage_1.png

    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:

    pastedImage_0.png

    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

  • Jerry Casey
    Jerry Casey ✭✭✭✭✭

    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?

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