CODEWITHSUNDEEP
sql-server
Andrew
Andrew

Reputation: 65

One long CASE WHEN Statement

I have the longest comnined case statement i have done ever and I cannot troubleshoot this error. IT says its a syntax error near ) which I am fully embarrassed that I cannot figure out such a simple error

SUM(CASE 
      WHEN COL_V_STD_REP_FullCourseDetail.SYSTEM = 'GEMS' 
      THEN CASE 
             WHEN RRDD LIKE '12%' OR RRDD LIKE '13%' OR RRDD LIKE '16%' OR 
                  RRDD LIKE '17%' OR RRDD LIKE '2706%' OR RRDD LIKE '2707%' OR 
                  RRDD LIKE '2331%' 
             THEN [DUR_IN_Hours] 
             ELSE 0 
             END) AS SP_DOM_INTL_HRS

Upvotes: 1

Views: 1289

Answers (2)

Chris Story
Chris Story

Reputation: 1197

Logically, I wouldn't have nested CASE statements when the logic isn't that complex. For something like this, I would add the nested CASE statement's logic in the first CASE statement (shown below):

SUM(CASE 
  WHEN COL_V_STD_REP_FullCourseDetail.SYSTEM = 'GEMS' AND 
       (RRDD LIKE '12%' 
        OR RRDD LIKE '13%' 
        OR RRDD LIKE '16%' 
        OR RRDD LIKE '17%' 
        OR RRDD LIKE '2706%' 
        OR RRDD LIKE '2707%' 
        OR RRDD LIKE '2331%') THEN [DUR_IN_Hours] 
  ELSE 0 
END) AS SP_DOM_INTL_HRS

Upvotes: 2

Taryn
Taryn

Reputation: 247720

You are missing the second END:

SUM(CASE 
      WHEN COL_V_STD_REP_FullCourseDetail.SYSTEM = 'GEMS' 
      THEN 
        CASE 
          WHEN RRDD LIKE '12%' 
            OR RRDD LIKE '13%' 
            OR RRDD LIKE '16%' 
            OR RRDD LIKE '17%' 
            OR RRDD LIKE '2706%' 
            OR RRDD LIKE '2707%' 
            OR RRDD LIKE '2331%' 
          THEN [DUR_IN_Hours] 
          ELSE 0 
        END
     END) AS SP_DOM_INTL_HRS
     ^ -- this is missing

Just as a side note, if you format your code you will have an easier time finding these issues.

Upvotes: 6

Related Questions