Forum Stats

  • 3,837,679 Users
  • 2,262,284 Discussions
  • 7,900,354 Comments

Discussions

NESTED CASE statement in a SQL

768510
768510 Member Posts: 8
edited May 4, 2010 12:51AM in SQL & PL/SQL
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

Best Answer

  • Centinul
    Centinul Member Posts: 6,871 Bronze Crown
    Answer ✓
    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 */

Answers

  • 21205
    21205 Member Posts: 6,168 Gold Trophy
    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.
    21205
  • Centinul
    Centinul Member Posts: 6,871 Bronze Crown
    Answer ✓
    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 */
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,221 Red Diamond
    edited Apr 27, 2010 9:20AM
    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
  • 768510
    768510 Member Posts: 8
    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
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,221 Red Diamond
    edited Apr 27, 2010 9:37AM
    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.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 768510
    768510 Member Posts: 8
    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
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,221 Red Diamond
    edited Apr 27, 2010 10:41AM
    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?
  • 768510
    768510 Member Posts: 8
    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
  • 768510
    768510 Member Posts: 8
    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
This discussion has been closed.