Forum Stats

  • 3,838,560 Users
  • 2,262,382 Discussions
  • 7,900,688 Comments

Discussions

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

User_R5UI6
User_R5UI6 Member Posts: 2 Red Ribbon

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:

Tagged:

Answers

  • User_R5UI6
    User_R5UI6 Member Posts: 2 Red Ribbon

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

  • cormaco
    cormaco Member Posts: 1,952 Silver Crown

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

    For a simple CASE expression, the expr and all comparison_expr values must either have the same data type (CHARVARCHAR2NCHAR, or NVARCHAR2NUMBERBINARY_FLOAT, or BINARY_DOUBLE) or must all have a numeric data type.

  • User_3ABCE
    User_3ABCE Member Posts: 51 Blue Ribbon

    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
    BEDE Oracle Developer Member Posts: 2,454 Gold Trophy

    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.

    User_5D5O0
  • BluShadow
    BluShadow Member, Moderator Posts: 42,132 Red Diamond

    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
    Stax Member Posts: 130 Bronze Badge
    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>
    
    
    Paulzip
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,243 Red Diamond

    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
    MATTEO RAPPAZZO Member Posts: 3 Green Ribbon

    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