2 Replies Latest reply: Nov 21, 2012 10:06 AM by rp0428 RSS

    plz help me in case statement

    975418
      Hi All,

      In a table OT_C_S_R_HEAD in this there is a column "CSR_STATUS" having datatype "Number" and i have to apply case on this that if "2 or null is no, 1 is yes".
      But apply getting some error "ORA-00932: inconsistent datatypes: expected NUMBER got CHAR"


      SELECT
      CSR_AOE_NO

      , CASE CSR_STATUS
      WHEN '2' THEN ('NO')
      WHEN '1' THEN ('YES')
      WHEN NULL THEN ('NO')
      END CSR_STATUS

      FROM OT_C_S_R_HEAD

      plz reply asap....
        • 1. Re: plz help me in case statement
          975472
          Try removing " ' ".

          SELECT
          CSR_AOE_NO

          , CASE CSR_STATUS
          WHEN 2 THEN ('NO')
          WHEN 1 THEN ('YES')
          WHEN NULL THEN ('NO')
          END CSR_STATUS

          FROM OT_C_S_R_HEAD

          Edited by: user6216982 on 21-nov-2012 3.25
          • 2. Re: plz help me in case statement
            rp0428
            Welcome to the forum!

            This is NOT the forum for SQL or PL/SQL questions. As the title says this forum is for sql developer questions only.

            If you have any additional comments or follow please DO NOT followup in this forum but repost the question in the sql and pl/sql forum.
            PL/SQL
            >
            In a table OT_C_S_R_HEAD in this there is a column "CSR_STATUS" having datatype "Number" and i have to apply case on this that if "2 or null is no, 1 is yes".
            But apply getting some error "ORA-00932: inconsistent datatypes: expected NUMBER got CHAR"
            >
            The reply that you marked CORRECT will not cause the exception you were receiving but it will also NOT give you the results you want.
            create table a (col1 number)
            
            insert into a values (null)
            
            insert into a values (1)
            
            insert into a values (2)
            
            insert into a values (3)
            
            SELECT col1, CASE col1
            WHEN 2 THEN ('NO')
            WHEN 1 THEN ('YES')
            WHEN NULL THEN ('NO')
            END test
            FROM a 
            
            COL1,TEST
            ,
            1,YES
            2,NO
            3,
            You said you wanted 'NO' if the column has a NULL value but that query gives you a NULL. The query will also give you NULL for values other than 1 or 2 because there is no ELSE clause. Except in unusual cases there should always be an ELSE clause as a 'catch all'.

            If all you want is YES for a 1 and NO for anything else just use
            SELECT col1, CASE col1
            WHEN 1 THEN ('YES')
            ELSE ('NO')
            END test
            FROM a 
            
            COL1,TEST
            ,NO
            1,YES
            2,NO
            3,NO