ORA-00905: missing keyword in CASE Clause, But I'm able to do with [AND/OR]

User_R5UI6

SELECT tbl1.R1, tbl1.R2

  FROM (select 'ABC' as A1, 

'123' as R1, 

'112233' 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 '123' = tbl1.R1 AND '112233' = tbl1.R2

 WHEN tbl1.C2 IS NOT NULL

THEN '123' = tbl1.R1 AND '112233' = tbl1.R2

 WHEN tbl1.C3 IS NOT NULL

THEN '123' = tbl1.R1 AND '112233' = tbl1.R2

 WHEN tbl1.C4 IS NOT NULL

THEN '123' = tbl1.R1 AND '112233' = tbl1.R2

         ELSE NULL       

       END; 

Error:

ORA-00905: missing keyword

00905. 00000 - "missing keyword"

*Cause:   

*Action:

User_R5UI6

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

cormaco

You can't have a boolean expression as return type of a CASE expression, because boolean is not a datatype in Oracle SQL.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CASE-Expressions.html

For a simple CASE expression, the expr and all comparison_expr values 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.

User_3ABCE

The CASE-THEN allows only sql expressions. "'123' = tbl1.R1 AND '112233' = tbl1.R2" is a sql condition.

In your case, the CASE is not needed.

BEDE

So, to understand that code, I had to format it properly, which gave me the below.

SELECT tbl1.R1, tbl1.R2
FROM (
  select 'ABC' as A1, 
  '123' as R1, 
  '112233' 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 '123' = tbl1.R1 AND '112233' = tbl1.R2
     WHEN tbl1.C2 IS NOT NULL THEN '123' = tbl1.R1 AND '112233' = tbl1.R2
     WHEN tbl1.C3 IS NOT NULL THEN '123' = tbl1.R1 AND '112233' = tbl1.R2
     WHEN tbl1.C4 IS NOT NULL THEN '123' = tbl1.R1 AND '112233' = tbl1.R2
   ELSE NULL       
   END;

That shows you did not understand the syntax of the case in SQL. After the then you must have an expression returning one value, so that you may not have anything like  THEN '123' = tbl1.R1. Ech branch of a case should have an expression returning the same data type. And, if used in the where clause of a select, after a case there should be something like below:

where
case
  when ...
  when ...
  else ...
end=x_value /*--- or it may be <= , */

Sorry, but I do not understand what you actually wish to do.

BluShadow

Based on BEDE's formatting it looks to me like you actually want...

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

I assume the actual values for each case would likely differ, otherwise you can optimize those case statements to ...

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

Remember that a CASE statement 'returns' a single value (you can't 'return' and expression from it), and your WHERE clause needs to compare that value with something, so the test against the R1 and R2 columns need to be outside the CASE.

In PL/SQL the CASE statement can return boolean TRUE or FALSE which can be evaluated from an expression, but not in SQL statements themselves which don't have a boolean datatype.

Stax

SELECT *
FROM (
  select 'ABC' as A1,   '123' as R1,   '112233' as R2,   '' as C1,  '1' as C2,  '' as C3,  '' as C4 from dual union all
  select 'ABC' as A1,   '123' as R1,   '112233' as R2,   '' as C1,  '' as C2,  '' as C3,  '' as C4 from dual union all
  select 'ABC' as A1,   '123' as R1,   '112233' as R2,   '' as C1,  '' as C2,  '' as C3,  '1' as C4 from dual union all
  select 'ABC' as A1,   '345' as R1,   '112233' as R2,   '' as C1,  '1' as C2,  '' as C3,  '' as C4 from dual
) tbl1 
WHERE
  'ABC' = tbl1.A1 AND 
   '123' = tbl1.R1 AND 
   '112233' = tbl1.R2 AND 
   coalesce(c1,c2,c3,c4) IS NOT NULL
--(c1 IS NOT NULL or c2 IS NOT NULL or c3 IS NOT NULL or c4 IS NOT NULL)

SQL> /


A1  R1  R2     C C C C
--- --- ------ - - - -
ABC 123 112233   1
ABC 123 112233       1


SQL>

Frank Kulash

Hi,

As others have said, you can have a CASE expression return a BOOLEAN in pure SQL, because there is no BOOLEAN data type in pure SQL.

CASE expressions are handy when you need to do if-then-else logic. Conditions, such as those in WHERE clauses, have their own way of doing if-then-else logic, so CASE expressions aren't needed (or helpful) very much in conditions. If you're ever tempted to use a CASE expression in a condition, try to think of some other way; there usually is a better way.

Even if

CASE WHEN tbl1.C1 IS NOT NULL

THEN '123' = tbl1.R1 AND '112233' = tbl1.R2

 WHEN tbl1.C2 IS NOT NULL

THEN '123' = tbl1.R1 AND '112233' = tbl1.R2

 WHEN tbl1.C3 IS NOT NULL

THEN '123' = tbl1.R1 AND '112233' = tbl1.R2

 WHEN tbl1.C4 IS NOT NULL

THEN '123' = tbl1.R1 AND '112233' = tbl1.R2

         ELSE NULL       

       END; 

was allowed, it would be better expressed like this:

(   COALESCE (tbl1.c1, tbl1.c2, tbl1.c3, tbl1.c4) IS NOT NULL
AND tbl1.r1 = '123'
AND tbl1.r2 = '112233'
)

MATTEO RAPPAZZO

Maybe are you trying to do something like this?

SELECT tbl1.R1, tbl1.R2

FROM (select 'ABC'    as A1, 

        '123'    as R1, 

        '112233'  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 AND '123' = tbl1.R1 AND '112233' = tbl1.R2

    THEN 1

  WHEN tbl1.C2 IS NOT NULL AND '123' = tbl1.R1 AND '112233' = tbl1.R2

    THEN 1

  WHEN tbl1.C3 IS NOT NULL AND '123' = tbl1.R1 AND '112233' = tbl1.R2

    THEN 1

  WHEN tbl1.C4 IS NOT NULL AND '123' = tbl1.R1 AND '112233' = tbl1.R2

    THEN 1

  ELSE 0

END ) = 1