9 Replies Latest reply: May 3, 2010 11:51 PM by 768510 RSS

    NESTED CASE statement in a SQL

    768510
      Hi,
      I have written a NESTED CASE statement in a SQL but when try running it, I'm getting the error as "missing keyword"
      Can someone help me in correcting this?

      SELECT ITEM
      ,DETAIL_LEVEL_DESC AS DESCRIPTION
      ,COMP_DETAIL_ID AS PROMO_ID
      ,CASE WHEN CHANGE_TYPE = 'N'
      THEN CASE WHEN INSTR(UPPER(DETAIL_LEVEL_DESC), 'S/P')!=0 THEN 'SPP'
      WHEN INSTR(UPPER(DETAIL_LEVEL_DESC), 'NIO')!=0 THEN 'NIO'
      ELSE 'NEW' END,
      ELSE CASE WHEN INSTR(UPPER(DETAIL_LEVEL_DESC), 'SOE')!=0 THEN 'SOE'
      ELSE 'SOM' END, SUBTYPE_CODE
      FROM PROMO_COMP
      ORDER BY ITEM;

      regards
      Harsha
        • 1. Re: NESTED CASE statement in a SQL
          21205
          this?
          SELECT ITEM
          ,DETAIL_LEVEL_DESC AS DESCRIPTION
          ,COMP_DETAIL_ID AS PROMO_ID
          ,case
            WHEN CHANGE_TYPE = 'N' 
            THEN CASE 
                  WHEN INSTR(UPPER(DETAIL_LEVEL_DESC), 'S/P')!=0 
                  THEN 'SPP'
                  WHEN INSTR(UPPER(DETAIL_LEVEL_DESC), 'NIO')!=0 
                  THEN 'NIO'
                  ELSE 'NEW' end
            ELSE 
             CASE WHEN INSTR(UPPER(DETAIL_LEVEL_DESC), 'SOE')!=0 
                  THEN 'SOE'
             ELSE 'SOM' 
             end
             , SUBTYPE_CODE 
          FROM PROMO_COMP
          ORDER BY ITEM;
          formatting your code might help you identify the missing keyword.
          • 2. Re: NESTED CASE statement in a SQL
            Centinul
            This is when it's always helpful to format your code for readability because the errors pretty much jump out at you:
            CASE    WHEN CHANGE_TYPE = 'N'
                    THEN CASE 
                                    WHEN INSTR(UPPER(DETAIL_LEVEL_DESC), 'S/P')!=0 
                                    THEN 'SPP'
                                    WHEN INSTR(UPPER(DETAIL_LEVEL_DESC), 'NIO')!=0 
                                    THEN 'NIO'
                                    ELSE 'NEW' 
                         END,
                    ELSE CASE 
                                    WHEN INSTR(UPPER(DETAIL_LEVEL_DESC), 'SOE')!=0 
                                    THEN 'SOE'
                                    ELSE 'SOM' 
                         END
            END /* MISSING THIS END */
            • 3. Re: NESTED CASE statement in a SQL
              Frank Kulash
              Hi, Harsha,

              As Alex said, and Centinul illustrated so well, indent CASE expressions so that every END statement is directly below its correspond CASE, with nothing but white space between them.

              You didn't put commas before the inner ELSE statements; you don't need one before the outer ELSE statement, either.
              CASE    WHEN  CHANGE_TYPE = 'N'
                      THEN  CASE 
                                      WHEN  INSTR (UPPER (DETAIL_LEVEL_DESC), 'S/P') != 0 
                                      THEN  'SPP'
                                      WHEN  INSTR( UPPER (DETAIL_LEVEL_DESC), 'NIO') != 0 
                                      THEN  'NIO'
                                      ELSE  'NEW' 
                            END                  -- No "," here
                      ELSE  CASE 
                                      WHEN  INSTR( UPPER( DETAIL_LEVEL_DESC), 'SOE') != 0 
                                      THEN  'SOE'
                                      ELSE  'SOM' 
                            END
              END
              You don't need the second nested CASE expression. The expression below produces the same results as the one above, and may be slightly more efficient:
              CASE    WHEN  CHANGE_TYPE = 'N'
                      THEN  CASE 
                                      WHEN  INSTR (UPPER (DETAIL_LEVEL_DESC), 'S/P') != 0 
                                      THEN  'SPP'
                                      WHEN  INSTR( UPPER (DETAIL_LEVEL_DESC), 'NIO') != 0 
                                      THEN  'NIO'
                                      ELSE  'NEW' 
                            END                  -- No "," here
                      WHEN  INSTR( UPPER( DETAIL_LEVEL_DESC), 'SOE') != 0 
                      THEN  'SOE'
                      ELSE  'SOM' 
              END
              Edited by: Frank Kulash on Apr 27, 2010 9:18 AM
              • 4. Re: NESTED CASE statement in a SQL
                768510
                Hi,
                Thanks for your reply.
                I corrected the CASE statement, but when i run it, its returning me "invalid number".

                DETAIL_LEVEL_DESC is defined as VARCHAR2(160 BYTE)
                ITEM     is defined as VARCHAR2(25 BYTE)
                ==============================================
                SELECT
                ITEM
                ,CASE WHEN CHANGE_TYPE = 'N'
                THEN CASE
                WHEN INSTR(UPPER(DETAIL_LEVEL_DESC), 'S/P') > '0'
                THEN 'SPP'
                WHEN INSTR(UPPER(DETAIL_LEVEL_DESC), 'NIO') > '0'
                THEN 'NIO'
                ELSE 'NEW'
                END
                ELSE CASE WHEN INSTR(UPPER(DETAIL_LEVEL_DESC), 'SOE') > '0'
                THEN 'SOE'
                ELSE 'SOM'
                END
                END SUBTYPE_CODE
                FROM COMP_SIMPLE_TABLE
                ORDER BY ITEM;
                ==============================================

                Also, i could see that when you post the reply, the SQL is properly aligned which I'm not able to do. So how do you do that!?

                Regards
                Harsha
                • 5. Re: NESTED CASE statement in a SQL
                  Frank Kulash
                  Hi,
                  harsha puthraya wrote:
                  Hi,
                  Thanks for your reply.
                  I corrected the CASE statement, but when i run it, its returning me "invalid number".
                  Whenever you have a problem, post a little sample data (CREATE TABLE and INSERT statements) so people can re-create the problem and test their solutions.
                  If the problem involves an error, post the complete error message, including line number.
                  >
                  DETAIL_LEVEL_DESC is defined as VARCHAR2(160 BYTE)
                  ITEM     is defined as VARCHAR2(25 BYTE)
                  ==============================================
                  SELECT
                  ITEM
                  ,CASE WHEN CHANGE_TYPE = 'N'
                  THEN CASE
                  WHEN INSTR(UPPER(DETAIL_LEVEL_DESC), 'S/P') > '0'
                  THEN 'SPP'
                  WHEN INSTR(UPPER(DETAIL_LEVEL_DESC), 'NIO') > '0'
                  THEN 'NIO'
                  ELSE 'NEW'
                  END
                  ELSE CASE WHEN INSTR(UPPER(DETAIL_LEVEL_DESC), 'SOE') > '0'
                  THEN 'SOE'
                  ELSE 'SOM'
                  END
                  END SUBTYPE_CODE
                  FROM COMP_SIMPLE_TABLE
                  ORDER BY ITEM;
                  ==============================================
                  INSTR returns a NUMBER. Why are you comparing it to a string ('0')? Change all the string '0's back to NUMBER 0s, like they were in your first message.
                  (I doubt this is causing the error, however.)
                  Also, i could see that when you post the reply, the SQL is properly aligned which I'm not able to do. So how do you do that!?
                  Type these 6 characters:

                  \
                  (small letters only, inside curly brackets) before and after each section of formatted text.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                  • 6. Re: NESTED CASE statement in a SQL
                    768510
                    Hi Frank,

                    here are the details
                    CREATE TABLE CODETYPE
                    (ITEM VARCHAR(20), CHANGE_TYPE CHAR(1), DETAIL_LEVEL_DESC VARCHAR(160))
                    
                    INSERT INTO CODETYPE VALUES
                    ('012893132','N','s/p fuji gloss photo')
                    ('010954818','N','NIO A4 50 SHEET')
                    ('014573180','N','A4 photo paper')
                    ('014513626','2','SOE Gunmetal')
                    
                    SQL
                    ===
                    
                    SELECT
                    ITEM
                    ,CASE WHEN CHANGE_TYPE = 'N'
                          THEN CASE
                                    WHEN INSTR(UPPER(DETAIL_LEVEL_DESC), 'S/P') > 0
                                    THEN 'SPP'
                                    WHEN INSTR(UPPER(DETAIL_LEVEL_DESC), 'NIO') > 0
                                    THEN 'NIO'
                                    ELSE 'NEW'
                               END
                          ELSE CASE WHEN INSTR(UPPER(DETAIL_LEVEL_DESC), 'SOE') > 0
                                    THEN 'SOE'
                                    ELSE 'SOM'
                               END
                    END SUBTYPE_CODE
                    FROM CODETYPE
                    ORDER BY ITEM;
                    
                    Im getting the error as "invalid number", but its not showing me the line number.
                    
                    Regards
                    Harsha
                    • 7. Re: NESTED CASE statement in a SQL
                      Frank Kulash
                      Hi,
                      harsha puthraya wrote:
                      Hi Frank,

                      here are the details
                      CREATE TABLE CODETYPE
                      (ITEM VARCHAR(20), CHANGE_TYPE CHAR(1), DETAIL_LEVEL_DESC VARCHAR(160))
                      
                      INSERT INTO CODETYPE VALUES
                      ('012893132','N','s/p fuji gloss photo')
                      ('010954818','N','NIO A4 50 SHEET')
                      ('014573180','N','A4 photo paper')
                      ('014513626','2','SOE Gunmetal')
                      Is that what you're really doing?
                      Don't you have to do something like this?
                       
                      CREATE TABLE CODETYPE
                      (ITEM VARCHAR(20), CHANGE_TYPE CHAR(1), DETAIL_LEVEL_DESC VARCHAR(30))
                      ;
                       
                      INSERT INTO CODETYPE VALUES ('012893132','N','s/p fuji gloss photo');
                      INSERT INTO CODETYPE VALUES ('010954818','N','NIO A4 50 SHEET');
                      INSERT INTO CODETYPE VALUES ('014573180','N','A4 photo paper');
                      INSERT INTO CODETYPE VALUES ('014513626','2','SOE Gunmetal');
                      SQL
                      ===
                      
                      SELECT
                      ITEM
                      ,CASE WHEN CHANGE_TYPE = 'N'
                      THEN CASE
                      WHEN INSTR(UPPER(DETAIL_LEVEL_DESC), 'S/P') > 0
                      THEN 'SPP'
                      WHEN INSTR(UPPER(DETAIL_LEVEL_DESC), 'NIO') > 0
                      THEN 'NIO'
                      ELSE 'NEW'
                      END
                      ELSE CASE WHEN INSTR(UPPER(DETAIL_LEVEL_DESC), 'SOE') > 0
                      THEN 'SOE'
                      ELSE 'SOM'
                      END
                      END SUBTYPE_CODE
                      FROM CODETYPE
                      ORDER BY ITEM;
                      
                      Im getting the error as "invalid number", but its not showing me the line number.
                      Hiow are you running this? What is your front end?
                      Regards
                      Harsha
                      That works fine for me. Output:
                      ITEM                 SUB
                      -------------------- ---
                      010954818            NIO
                      012893132            SPP
                      014513626            SOE
                      014573180            NEW
                      I tried it in Oracle 10.2.0.3.0 and 11.1.0.6.0. What version are you using?
                      • 8. Re: NESTED CASE statement in a SQL
                        768510
                        Hi Frank,
                        Thanks for trying out the SQL.

                        But I'm still getting the same error.

                        My Oracle version - 10.2.0.4.0
                        Front End - Oracle SQL Developer

                        Error which I'm getting is
                        -----------------------------------
                        ORA-01722:invalid number
                        Vendor code 1722Error at Line:1

                        Regards
                        Harsha
                        • 9. Re: NESTED CASE statement in a SQL
                          768510
                          Frank,
                          Some of my "DETAIL_LEVEL_DESC" has NULL values. I think its returning me a non-number value when it is NULL, so I'm getting the error.

                          Is my understanding correct?

                          Regards
                          Harsha