Filter with nested case when

Edit: See ly.le’s comment below — a simple variable is the right approach here. My original suggestion is left for context only.

Hi ​@ly.le ,

The syntax fails because a PQL CASE WHEN statement can only return a scalar value (like a text string or a number..). It cannot dynamically output operators (= or IN) or structural lists ('A', 'B') inside the THEN clause.
 

To fix this, rebuild the logic using native boolean AND/OR blocks. This allows the PQL engine to evaluate the variable state first, and then apply the correct filter execution without breaking syntax rules -->

FILTER 
    (
        COALESCE('${Var1}', '') = 'Open Order' 
        AND "VBAP"."FilteringField" = 'Open Order'
    ) 
    OR 
    (
        COALESCE('${Var1}', '') = 'Missing Invoice' 
        AND "VBAP"."FilteringField" IN ('Open Order', 'Missing Invoice')
    )

Replace

 ${Var1}

with your exact variable format if needed.

Thank you for your reply! I still got the err after trying out your suggestion.

But I realized that I overcomplicated the thing. Since it is only single field I am applying the filter on, there is no need for CASE WHEN. I simply revised it as:

FILTER 

"VBAP"."FilteringField" = ${var1}

And then let the users use the button to update the Var1.

One small note on your comment on the CASE WHEN returning only scalar: It actually can return a column. And because of this, I was surprise when it complained on a list in my formular.

​@ly.le  Ah, thanks for the correction, I honestly didn’t know CASE WHEN could return a column like that. My bad.
Your button-variable approach is way cleaner anyway, glad you got it sorted.