ORA 01797- operator must be followed by any or all

Question

I am testing a condition like this in the where clause of a subquery. But I am getting the error "operator must be followed by any or all" when I execute the SSRS report.

dbase is oracle. And i need to use IN with parameter because the parameter in SSRS report is multivalued. I am using a separate function to generate dates that go in :P_Date. I need to check if this date is = or < or null . All three conditions need to be tested.

where 
trunc(tt.fyh_fecha) IN (:P_Date) OR
trunc(tt.fyh_fecha) <(:P_Date) OR 
trunc(tt.fyh_fecha) IS NULL AND
tc.cod_tree = 'blue' AND
tt.color_flower = 'pink'

Answer 1

This doesnt seem directly possible - you are trying to use a parameter containing an array of values against the < operator which only expects one value. Your design doesnt make any logical sense to me either (<= multiple dates?), but anyway ...

I would add a join to a Calendar / Date Dimension table, where I would apply the IN (:P_Date) criteria to get a list of Date values as a deliberate cross join.

Then I would replace:

trunc(tt.fyh_fecha) IN (:P_Date) OR

trunc(tt.fyh_fecha) <(:P_Date) OR

with:

trunc(tt.fyh_fecha) <= Dim_Date.Date_Value