CODEWITHSUNDEEP
sqlcasewhere-clause
sqljeopardy
sqljeopardy

Reputation: 1

Case in a Where Clause

I'm new to SQL and I am trying to understand the CASE clause. I would like to print the department name in the report next to the Department code when counting the merchandise. Can you tell me what I am doing incorrectly. Thank you.

SELECT  
DEPT_C AS "DEPT CODE",
NAME as "DEPT NAME",
TO_CHAR(COUNT(*), '999,999,999,999') AS TOTAL 
FROM STORE.DEPT 
WHERE CASE  
WHEN DEPT_C = 'D01' THEN 'WOMAN ACTIVE WEAR' 
WHEN DEPT_C = 'D02' THEN 'WOMAN PLUS SIZE'
WHEN DEPT_C = 'D03' THEN 'JUNIORS'
WHEN DEPT_C = 'D04' THEN 'GIRLS'
WHEN DEPT_C = 'D05' THEN 'INFANTS AND TODDLERS'
WHEN DEPT_C = 'D06' THEN 'ACCESSORIES'
WHEN DEPT_C = 'D07' THEN 'JEWLERY'
WHEN DEPT_C = 'D08' THEN 'PERFUME'
WHEN DEPT_C = 'D09' THEN 'COSMETICS'
ELSE 'OTHER' END NAME
GROUP BY 1,2
ORDER BY 2,1;

Upvotes: 0

Views: 39

Answers (1)

nestedloop
nestedloop

Reputation: 2646

Think of CASE as a switch statement.

In this case, you want a different name for each department code. Therefore your cases use the DEPT_C column as the reference variable, and assign name values for individual codes.

Like this:

SELECT  
DEPT_C AS "DEPT CODE",
CASE DEPT_C
WHEN 'D01' THEN 'WOMAN ACTIVE WEAR' 
WHEN 'D02' THEN 'WOMAN PLUS SIZE'
WHEN 'D03' THEN 'JUNIORS'
WHEN 'D04' THEN 'GIRLS'
WHEN 'D05' THEN 'INFANTS AND TODDLERS'
WHEN 'D06' THEN 'ACCESSORIES'
WHEN 'D07' THEN 'JEWLERY'
WHEN 'D08' THEN 'PERFUME'
WHEN 'D09' THEN 'COSMETICS'
ELSE 'OTHER' 
END as 'DEPT NAME'
FROM STORE.DEPT

EDIT:

You can also ignore the input expression, namely the DEPT_C, but then each condition should be explicit, like this:

SELECT  
DEPT_C AS "DEPT CODE",
CASE 
WHEN DEPT_C = 'D01' THEN 'WOMAN ACTIVE WEAR' 
WHEN DEPT_C = 'D02' THEN 'WOMAN PLUS SIZE'
WHEN DEPT_C = 'D03' THEN 'JUNIORS'
WHEN DEPT_C = 'D04' THEN 'GIRLS'
WHEN DEPT_C = 'D05' THEN 'INFANTS AND TODDLERS'
WHEN DEPT_C = 'D06' THEN 'ACCESSORIES'
WHEN DEPT_C = 'D07' THEN 'JEWLERY'
WHEN DEPT_C = 'D08' THEN 'PERFUME'
WHEN DEPT_C = 'D09' THEN 'COSMETICS'
ELSE 'OTHER' 
END as 'DEPT NAME'
FROM STORE.DEPT

CAVEAT: I changed your select order, be sure to rewrite your GROUP BY and ORDER BY accordingly.

Upvotes: 3

Related Questions