2 Replies Latest reply: Nov 29, 2012 6:47 PM by Solomon Yakobson RSS

    Nested CASE statement naming error: Missing Keyword

    977121
      Hi guys,

      It Keeps saying Missing Keyword and I have no absolutely no clue why.

      SELECT ID_NUMBER,
      (CASE WHEN BUSINESS_UNIT_ID = 'ABC' THEN
      MAX( CASE WHEN rn = '1' THEN ASSIGNEE_HISTORY_GROUP END) AS first_group,
      MAX( CASE WHEN rn = '2' THEN ASSIGNEE_HISTORY_GROUP END) AS second_group,
      MAX( CASE WHEN rn = '3' THEN ASSIGNEE_HISTORY_GROUP END) AS third_group,
      MAX( CASE WHEN rn = '4' THEN ASSIGNEE_HISTORY_GROUP END) AS fourth_group,
      MAX( CASE WHEN rn = '5' THEN ASSIGNEE_HISTORY_GROUP END) AS fifth_group,
      MAX( CASE WHEN rn = '6' THEN ASSIGNEE_HISTORY_GROUP END) AS sixth_group,
      MAX( CASE WHEN rn = '7' THEN ASSIGNEE_HISTORY_GROUP END) AS seventh_group END),
      /*More Code*/

      Thanks,
      Alan
        • 1. Re: Nested CASE statement naming error: Missing Keyword
          rp0428
          Welcome to the forum!

          Whenever you post provide your 4 digit Oracle version.
          >
          (CASE WHEN BUSINESS_UNIT_ID = 'ABC' THEN
          MAX( CASE WHEN rn = '1' THEN ASSIGNEE_HISTORY_GROUP END) AS first_group,
          MAX( CASE WHEN rn = '2' THEN ASSIGNEE_HISTORY_GROUP END) AS second_group,
          . . .
          >
          You can't construct multiple columns using a case statement like that.

          That code tests for 'ABC' and the THEN clause has a, b, c, d, e, etc.

          When you are trying to work out the syntax you don't need 7 nested CASE statements and 'more code'. Just use the simplest possible example that helps you get the syntax correct. Once the syntax is correct it is very easy to add additional elements.

          So please explain, in English, with a SIMPLE example what you are trying to do.

          This won't be correct but along the lines of
          >
          I want to select ID_NUMBER and the MAX value of ASSIGNEE_HISTORY_GROUP.
          >
          Show some sample data, explain what 'rn' is, what that group column represents and why you want the MAX of it.

          Once we understand what you are trying to do and what data you are doing it with it will be a lot easier to help you.
          • 2. Re: Nested CASE statement naming error: Missing Keyword
            Solomon Yakobson
            974118 wrote:

            It Keeps saying Missing Keyword and I have no absolutely no clue why.
            Look at your code:
            SELECT  ID_NUMBER,
                    (
                     CASE
                       WHEN BUSINESS_UNIT_ID = 'ABC'
                         THEN 
                           MAX( CASE WHEN rn = '1' THEN ASSIGNEE_HISTORY_GROUP END) AS first_group, 
                           MAX( CASE WHEN rn = '2' THEN ASSIGNEE_HISTORY_GROUP END) AS second_group,
                           MAX( CASE WHEN rn = '3' THEN ASSIGNEE_HISTORY_GROUP END) AS third_group,
                           MAX( CASE WHEN rn = '4' THEN ASSIGNEE_HISTORY_GROUP END) AS fourth_group,
                           MAX( CASE WHEN rn = '5' THEN ASSIGNEE_HISTORY_GROUP END) AS fifth_group,
                           MAX( CASE WHEN rn = '6' THEN ASSIGNEE_HISTORY_GROUP END) AS sixth_group,
                           MAX( CASE WHEN rn = '7' THEN ASSIGNEE_HISTORY_GROUP END) AS seventh_group
                     END
                    ) -- that;s where column alias can be assigned
            There are two issues:

            1) THEN clause can produce one and only one expression
            2) column aliases are inside column expression.

            It is hard to tell what you need. Assuming you are grouping by BUSINESS_UNIT_ID:
            SELECT  ID_NUMBER,
                    CASE
                      WHEN BUSINESS_UNIT_ID = 'ABC' THEN MAX(CASE WHEN rn = '1' THEN ASSIGNEE_HISTORY_GROUP END)
                    END AS first_group, 
                    CASE
                      WHEN BUSINESS_UNIT_ID = 'ABC' THEN MAX(CASE WHEN rn = '2' THEN ASSIGNEE_HISTORY_GROUP END)
                    END AS second_group,
                    CASE
                      WHEN BUSINESS_UNIT_ID = 'ABC' THEN MAX(CASE WHEN rn = '3' THEN ASSIGNEE_HISTORY_GROUP END)
                    END AS third_group,
                    CASE
                      WHEN BUSINESS_UNIT_ID = 'ABC' THEN MAX(CASE WHEN rn = '4' THEN ASSIGNEE_HISTORY_GROUP END)
                    END AS fourth_group,
                     CASE
                      WHEN BUSINESS_UNIT_ID = 'ABC' THEN MAX(CASE WHEN rn = '5' THEN ASSIGNEE_HISTORY_GROUP END)
                    END AS fifth_group,
                    CASE
                      WHEN BUSINESS_UNIT_ID = 'ABC' THEN MAX(CASE WHEN rn = '6' THEN ASSIGNEE_HISTORY_GROUP END)
                    END AS sixth_group,
                    CASE
                      WHEN BUSINESS_UNIT_ID = 'ABC' THENMAX(CASE WHEN rn = '7' THEN ASSIGNEE_HISTORY_GROUP END)
                    END AS seventh_group
            SY.