CODEWITHSUNDEEP
sqldb2
OVO
OVO

Reputation: 123

DB2: Need help on CASE / WHEN

I have a query that pulls the driver name and terminal numbers here:

SELECT UNIQUE D.DRIVER_ID, D.NAME,
CASE
WHEN TERMINAL_NUMBER = '0' THEN 'LITHONIA'  
WHEN TERMINAL_NUMBER = '1' THEN 'MONTGOMERY' 
WHEN TERMINAL_NUMBER = '2' THEN 'BOWLING GREEN'
WHEN TERMINAL_NUMBER = '4' THEN 'OOLTEWAH'
WHEN TERMINAL_NUMBER = '40' THEN 'LEXINGTON SOUTH'
WHEN TERMINAL_NUMBER = '41' THEN 'DURHAM'
WHEN TERMINAL_NUMBER = '42' THEN 'LEXINGTON'
WHEN TERMINAL_NUMBER = '43' THEN 'OKC'
WHEN TERMINAL_NUMBER = '46' THEN 'STEVENSON'
WHEN TERMINAL_NUMBER = '47' THEN 'TUPELO'
WHEN TERMINAL_NUMBER = '48' THEN 'HUMBOLDT'
WHEN TERMINAL_NUMBER = '49' THEN 'MURFREESBORO'
WHEN TERMINAL_NUMBER = '5' THEN 'JEFFERSON CTIY'
WHEN TERMINAL_NUMBER = '50' THEN 'LEWISBURG'
WHEN TERMINAL_NUMBER = '52' THEN 'SAVANNAH'
WHEN TERMINAL_NUMBER = '54' THEN 'FRANKLIN'
WHEN TERMINAL_NUMBER = '6' THEN 'JACKSONVILLE'
WHEN TERMINAL_NUMBER = '75' THEN 'VIRGINIA'
WHEN TERMINAL_NUMBER = '8' THEN 'ATHENS'
WHEN TERMINAL_NUMBER = '9' THEN 'LATTA'
WHEN OTHER_CODE = 'COL' THEN 'COLOMBUS' 
END AS TERM 
FROM DRIVER D
WHERE D.ACTIVE_IN_DISP = 'True'
ORDER BY DRIVER_ID, term

However, I need it to have colombus be its own 'term' column, the only problem is the drivers who may have other code = 'col' are also in terminal 0.

How should I rewrite this query to demonstrate that ?

return results

drivers whose other_code = col

Upvotes: 0

Views: 46

Answers (1)

Hogan
Hogan

Reputation: 70523

This is a better way to this kind of translation -- I make a "temporary" table with VALUES statement and then join to that table. I think this makes it clear how this data could be stored in a table which would be better.

SELECT UNIQUE D.DRIVER_ID, D.NAME, TERMLIST.NAME AS TERM
FROM DRIVER D
LEFT JOIN (
  VALUES 
     ('0' , 'LITHONIA'  ),
     ('1' , 'MONTGOMERY' ),
     ('2' , 'BOWLING GREEN'),
     ('4' , 'OOLTEWAH'),
     ('40', 'LEXINGTON SOUTH'),
     ('41', 'DURHAM'),
     ('42', 'LEXINGTON'),
     ('43', 'OKC'),
     ('46', 'STEVENSON'),
     ('47', 'TUPELO'),
     ('48', 'HUMBOLDT'),
     ('49', 'MURFREESBORO'),
     ('5' , 'JEFFERSON CTIY'),
     ('50', 'LEWISBURG'),
     ('52', 'SAVANNAH'),
     ('54', 'FRANKLIN'),
     ('6' , 'JACKSONVILLE'),
     ('75', 'VIRGINIA'),
     ('8' , 'ATHENS'),
     ('9' , 'LATTA')
) TERMLIST ON TERMLIST.TN = D.TERMINAL_NUMBER
WHERE D.ACTIVE_IN_DISP = 'True'
ORDER BY D.DRIVER_ID, TERMLIST.NAME

For your special requirement on two values we do this:

SELECT UNIQUE D.DRIVER_ID, D.NAME,
     CASE WHEN D.TERMINAL_NUMBER = '0' AND D.OTHER_CODES = 'COL' THEN 'COL'
          ELSE TERMLIST.NAME END AS TERM 
FROM DRIVER D
LEFT JOIN (
  VALUES 
     ('0' , 'LITHONIA'  ),
     ('1' , 'MONTGOMERY' ),
     ('2' , 'BOWLING GREEN'),
     ('4' , 'OOLTEWAH'),
     ('40', 'LEXINGTON SOUTH'),
     ('41', 'DURHAM'),
     ('42', 'LEXINGTON'),
     ('43', 'OKC'),
     ('46', 'STEVENSON'),
     ('47', 'TUPELO'),
     ('48', 'HUMBOLDT'),
     ('49', 'MURFREESBORO'),
     ('5' , 'JEFFERSON CTIY'),
     ('50', 'LEWISBURG'),
     ('52', 'SAVANNAH'),
     ('54', 'FRANKLIN'),
     ('6' , 'JACKSONVILLE'),
     ('75', 'VIRGINIA'),
     ('8' , 'ATHENS'),
     ('9' , 'LATTA')
) TERMLIST ON TERMLIST.TN = D.TERMINAL_NUMBER
WHERE D.ACTIVE_IN_DISP = 'True'
ORDER BY D.DRIVER_ID, TERMLIST.NAME

Upvotes: 2

Related Questions