Using CASE statement in where clause having parameters-CodePudding

I am creating oracle report where if the user inputs true or false, the respective values should go to parameter. if user inputs All, then both true and false should go to parameters.

I used the following case statements in 3 ways. all of them show missing keyword error. could you please help me resolve this.

WHERE "EngExec Status" = ((:eng_exec_status = 'True' and "EngExec Status" = 'True') 
                           OR (:eng_exec_status = 'False' AND "EngExec Status" = 'False') 
                           OR (:eng_exec_status = 'All' AND  ("EngExec Status" = 'True' OR "EngExec Status"= 'False')))
                         
WHERE "EngExec Status" IN (CASE 
                               WHEN (:eng_exec_status) = 'True' THEN "EngExec Status"= 'True'
                               WHEN (:eng_exec_status) = 'False' THEN "EngExec Status"= 'False'
                               ELSE "EngExec Status" IN ('True', 'False')  
                           END) 

WHERE "EngExec Status" IN (CASE 
                               WHEN (:eng_exec_status) = 'All' THEN "EngExec Status" IN ('True','False')
                               ELSE (:eng_exec_status)   
                           END)

CodePudding user response：

My guess is that you want something simple like

WHERE ( :eng_exec_status = 'All' )
   OR ( :eng_exec_status = "EngExec Status" )

CodePudding user response：

Your first snippet:

WHERE "EngExec Status" = ((:eng_exec_status = 'True' and "EngExec Status" = 'True') 
                           OR (:eng_exec_status = 'False' AND "EngExec Status" = 'False') 
                           OR (:eng_exec_status = 'All' AND  ("EngExec Status" = 'True' OR "EngExec Status"= 'False')))

Fails as you have WHERE column_value = (<boolean_expression>) and that is not valid syntax. You can just remove the "EngExec Status" = at the start:

WHERE (:eng_exec_status = 'True'  AND "EngExec Status" = 'True') 
OR    (:eng_exec_status = 'False' AND "EngExec Status" = 'False') 
OR    (:eng_exec_status = 'All'   AND ("EngExec Status" = 'True' OR "EngExec Status"= 'False'))

The second snippet has the CASE expression:

CASE 
WHEN (:eng_exec_status) = 'True' THEN "EngExec Status"= 'True'
WHEN (:eng_exec_status) = 'False' THEN "EngExec Status"= 'False'
ELSE "EngExec Status" IN ('True', 'False')  
END

It should have the syntax:

CASE WHEN comparison_expr THEN return_expr [...] ELSE expr END

And from the documentation:

For both simple and searched CASE expressions, all of the return_exprs must either have the same data type (CHAR, VARCHAR2, NCHAR, or NVARCHAR2, NUMBER, BINARY_FLOAT, or BINARY_DOUBLE) or must all have a numeric data type.

Your return values do not have any of those data types and you have put a comparison expression in instead.

Oracle complains about a " missing keyword error" as it expects either the WHEN, ELSE or END keywords after the "EngExec Status" in the THEN and ELSE clauses (since it is not valid to put a comparison expression in that location).

Your third snippet fails for a similar reason to the second.

If you have a CHECK constraint on the "EngExec Status" column to restrict the value to either 'True' or 'False' then you can simplify the WHERE filter to:

WHERE :eng_exec_status = "EngExec Status" OR :eng_exec_status = 'All'

If you do not have a CHECK constraint to restrict the values then you can use:

WHERE "EngExec Status" IN ('True', 'False')
AND   (:eng_exec_status = "EngExec Status" OR :eng_exec_status = 'All')