ORA-00905: missing keyword while Using [CASE]) Clause in WHERE condition in Oracle SQL-CodePudding

I'm trying to run the two sets of queries on the same dataset with Both ([AND/OR] and [CASE]) approaches, where one [AND/OR] is working and the other [CASE] is not working and getting "ORA-00905: missing keyword"

Condition is: For Columns [C1, C2, C3, and C4] any one column will contain a value remaining, and all will be null.

With AND/OR clause (Working...)

SELECT tbl1.R1, tbl1.R2
    FROM (select 'ABC' as A1, 
                 '1234' as R1, 
                 '11223344' as R2, 
                 '' as C1,
                 '1' as C2,
                 '' as C3,
                 '' as C4
         from dual) tbl1
    WHERE
        'ABC' = tbl1.A1 AND (tbl1.C1 IS NOT NULL AND ('1234' = tbl1.R1 AND '11223344' = tbl1.R2)) OR
        'ABC' = tbl1.A1 AND (tbl1.C2 IS NOT NULL AND ('1234' = tbl1.R1 AND '11223344' = tbl1.R2)) OR
        'ABC' = tbl1.A1 AND (tbl1.C3 IS NOT NULL AND ('1234' = tbl1.R1 AND '11223344' = tbl1.R2)) OR
        'ABC' = tbl1.A1 AND (tbl1.C4 IS NOT NULL AND ('1234' = tbl1.R1 AND '11223344' = tbl1.R2));

What's wrong? I'm doing this SQL queries:

With CASE Clause (Error)

SELECT tbl1.R1, tbl1.R2
    FROM (select 'ABC' as A1, 
                 '1234' as R1, 
                 '11223344' as R2, 
                 '' as C1,
                 '1' as C2,
                 '' as C3,
                 '' as C4
         from dual) tbl1
    WHERE
        'ABC' = tbl1.A1 
         AND CASE WHEN tbl1.C1 IS NOT NULL
                    THEN '1234' = tbl1.R1 AND '11223344' = tbl1.R2
                  WHEN tbl1.C2 IS NOT NULL
                    THEN '1234' = tbl1.R1 AND '11223344' = tbl1.R2
                  WHEN tbl1.C3 IS NOT NULL
                    THEN '1234' = tbl1.R1 AND '11223344' = tbl1.R2
                  WHEN tbl1.C4 IS NOT NULL
                    THEN '1234' = tbl1.R1 AND '11223344' = tbl1.R2  
                  ELSE NULL            
             END;

Why Error:

ORA-00905: missing keyword
00905. 00000 -  "missing keyword"
*Cause:    
*Action:

Expected output:

R1      R2
1234    11223344

CodePudding user response：

'1234' = tmptbl.B AND '11223344' = tmptbl.C is a boolean expression. That means you want the result of the CASE expression to be a boolean. Oracle SQL doesn't support a boolean data type, though, so the expression is invalid.

As a WHERE clause already is a boolean expression, you don't need CASE expressions there, anyway. Just use AND, OR, and the appropriate parentheses.

You probably want something along the lines of

WHERE tmptbl.A = 'CM'
AND (tmptbl.C IS NULL OR (tmptbl.B = 1234 AND tmptbl.C = 11223344))

CodePudding user response：

I agree that you should continue to use the boolean logic of and/or with parentheses for your where clause. However, to demonstrate how that case expression could be modified to work please consider this:

SELECT tbl1.R1, tbl1.R2
FROM (select 'ABC' as A1, 
             '1234' as R1, 
             '11223344' as R2, 
             '' as C1,
             '1' as C2,
             '' as C3,
             '' as C4
     from dual) tbl1
WHERE
     tbl1.A1 = 'ABC'
     AND CASE WHEN tbl1.C1 IS NOT NULL
                and '1234' = tbl1.R1 AND '11223344' = tbl1.R2 then 1
              WHEN tbl1.C2 IS NOT NULL
                and '1234' = tbl1.R1 AND '11223344' = tbl1.R2 then 1
              WHEN tbl1.C3 IS NOT NULL
                and '1234' = tbl1.R1 AND '11223344' = tbl1.R2 then 1
              WHEN tbl1.C4 IS NOT NULL
                and '1234' = tbl1.R1 AND '11223344' = tbl1.R2 then 1
              else 0
         END = 1

Here the case expression is returning a value (which is what they are expected to do).