CODEWITHSUNDEEP
sqlsql-server-2008case
Dango
Dango

Reputation: 7

Case statement combining three tables

SELECT 
    RS00200M.ACTION_NO,RS00200M.ACTION_CD,
    CAST(RS00200M.RESOLUTION_DT AS DATE) AS RESOLVED_DATE,
    CASE 
       WHEN (RS00200M.RESOLUTION_CD = 'CMPLT') 
          THEN (1) ELSE (0) 
    END AS COUNT_COMPLETE_SO,
    CASE 
       WHEN (RS00200M.ACTION_CD IN ('MTFRD', 'MTSRD')) 
          THEN (1) ELSE (0) 
    END AS COUNT_FINAL_INITIAL_SO,
    CASE 
       WHEN (RS00200M.ACTION_CD = 'RERTW') 
          THEN (1) ELSE (0) 
    END AS COUNT_ERT_CHANGE_SO,
    CASE 
       WHEN (RS00200M.ACTION_CD = 'MTEXE' AND UM00300M.SHORT IN ('058', '034', '100'))
          THEN (1) ELSE (0) 
    END AS COUNT_SHORT,
    CASE 
       WHEN (RS00200M.ACTION_CD = 'MTRRD') 
          THEN (1) ELSE (0) 
    END AS COUNT_REREAD_SO,
    CASE 
       WHEN (RS00200M.ACTION_CD = 'HIUSE') 
          THEN (1) ELSE (0) 
    END AS COUNT_HIUSE,
    CASE 
       WHEN (RS00200M.ACTION_CD = 'LKADJ') 
          THEN (1) ELSE (0) 
    END AS COUNT_LKADJ_SO
FROM
    RS00200M, UM00300M, RS00210M
LEFT JOIN 
    RS00210M ON RS00200M.ACTION_CD = RS00210M.ACTION_CD
LEFT JOIN 
    UM00300M ON UM00300M.SERIAL_NO = UM00210M.SERIAL_NO

I'm getting an error

Msg 208, Level 16, State 1, Line 1
Invalid object name 'RS00210M'

Any suggestions? Thanks in advance -- All three tables do exist in the database

Upvotes: 0

Views: 110

Answers (1)

GMB
GMB

Reputation: 222482

To start with, you need to fix your from clause. Each table should appear just once

FROM RS00200M
LEFT JOIN RS00210M on RS00200M.ACTION_CD=RS00210M.ACTION_CD
LEFT JOIN UM00300M on UM00300M.SERIAL_NO=UM00210M.SERIAL_NO

Also, you should remove this superfluous parentheses in the CASE expressions. Typically, this:

CASE WHEN (RS00200M.RESOLUTION_CD = 'CMPLT') THEN (1) ELSE (0) END AS COUNT_COMPLETE_SO

Should be spelled:

CASE WHEN RS00200M.RESOLUTION_CD = 'CMPLT' THEN 1 ELSE 0 END AS COUNT_COMPLETE_SO

This should be enough to make your query run without errors.

Side note: the structure of your query tend to indicate that you possibly want conditional aggregation. If so, here is the structure for it:

SELECT 
    RS00200M.ACTION_NO,
    RS00200M.ACTION_CD,
    CAST(RS00200M.RESOLUTION_DT AS DATE) AS RESOLVED_DATE,
    SUM(CASE WHEN RS00200M.RESOLUTION_CD = 'CMPLT' THEN 1 ELSE 0 END) AS COUNT_COMPLETE_SO,
    --- ... more SUM(CASE ...) expressions here ...
FROM RS00200M
LEFT JOIN RS00210M on RS00200M.ACTION_CD=RS00210M.ACTION_CD
LEFT JOIN UM00300M on UM00300M.SERIAL_NO=UM00210M.SERIAL_NO
GROUP BY
    RS00200M.ACTION_NO,
    RS00200M.ACTION_CD,
    CAST(RS00200M.RESOLUTION_DT AS DATE) AS RESOLVED_DATE

Upvotes: 2

Related Questions