Forcing the color_code output to be a different string using CASE

Question

So I was successfully able to pull the data that I need, however the issue now is that the color_code is a 2-digit number that not everyone knows. So, to rectify, I'd like to create a CASE statement that would replace each of these 84 unique color codes to a particular color (ex. 69 = Navy). To get the color code, I'm using a SUBSTRING on product_sku.

The real issue I'm having here is that I'm trying to paste this CASE statement I made into my query, but keep getting syntax errors at or near CASE:

(CASE
    WHEN SUBSTRING(product_sku,10,2) = 09 THEN SUBSTRING(product_sku,10,2) = 'Black'
    ELSE 'CHECK THIS SHIET FOO'
    FROM l_nvr_ec_returns
END)

This is just for one color for testing purposes, of course, but what am I doing wrong? Or perhaps more importantly, where exactly does this snippet even go? After my initial SELECT clause? SOS!

Thank you, Z

After some help from Caius Jard below, this is where I'm at. But getting an "INVALID SYNTAX" error

SELECT
    item_name,
    (SELECT
  CASE SUBSTRING(product_sku,10,2)
    WHEN '09' THEN 'Black'
    WHEN '69' THEN 'Navy'
    ELSE 'Unknown Color'
  END as color
FROM l_nvr_ec_returns) color,
    SUM(return_qty) number_of_returns,
    number_of_returns/  
    (SELECT
    SUM(return_qty)
FROM 
    l_nvr_ec_returns
WHERE 
    return_created_date BETWEEN '2019-10-01' AND '2019-10-31'
    AND return_status NOT IN ('Cancelled', 'cancelled')
    AND return_qty > 0
    AND return_reason_desc = 'Color Not As Expected'
) return_rate



FROM 
    l_nvr_ec_returns
WHERE 
    return_created_date BETWEEN '2019-10-01' AND '2019-10-31'
    AND return_status NOT IN ('Cancelled', 'cancelled')
    AND return_qty > 0
    AND return_reason_desc = 'Color Not As Expected'

GROUP BY item_name, color
ORDER BY color

Rather than using a case statement to map out 84 code/values, I would suggest you create a mapping table and use it for lookups. — alexherm, Nov 14 '19 at 16:39

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

I was also going to add some detail along the lines of alexherm's comment.. I would seriously consider making a table with the 84 values for colors, if you can.. For example like:

--make a table of all our colors
SELECT DISTINCT
  SUBSTRING(product_sku,10,2) as ColorCode,
  'xxxxxxxxxxxxxxxxxxxxx' as ColorName
INTO ColorTable
FROM l_nvr_ec_returns

Now go and edit all the xxxxxxxxx to be proper color names, in every row

OR

Put your massive CASE in this query:

SELECT DISTINCT
  SUBSTRING(product_sku,10,2) as ColorCode,
  CASE SUBSTRING(product_sku,10,2)
    WHEN '01' THEN 'Pink'
    WHEN '02' THEN 'Green'
    ...
    WHEN '84' THEN 'Silver'
END as ColorName
INTO ColorTable
FROM l_nvr_ec_returns

This will create all the correct color names from the get-go without needing to edit the table later

Then use it like:

SELECT * 
FROM
  l_nvr_ec_returns r
  INNER JOIN colortable c ON SUBSTRING(r.product_sku,10,2) = c.colorcode

Going the table route sounds more clean. But would you kindly elaborate on the 'xxxxx' part? Trying to wrap my head around the syntax that would go there. — Z41N, Nov 14 '19 at 17:00
That first query just creates a table in the DB that is full of eg `01, xxx. 02, xxx. 03, xxx` - you then have to edit the table (open up your table editor tool and type new data into the tool, over the top of the xxxxx) to change the xxx for 01 to say "Pink" and xxx for 02 to say "Cyan" etc.. — Caius Jard, Nov 14 '19 at 17:01

score 1 · Accepted Answer · answered Nov 14 '19 at 16:59

1

Carefully edit your query to remove the characters obscured by red, leaving a trailing comma:

CASE is an expression that is supposed to be used in the select list, to convert a single SKU to a single color, per row of l_nvr_ec_returns

answered Nov 14 '19 at 16:59

Caius Jard

72,509
5
49
80

This worked!! Will be adding the remaining colors, but if you do not mind, I would still like to learn how to use the table method. Thank you !~ – Z41N Nov 14 '19 at 17:02
When you've added the remaining colors, see the edit I made to the "create a table" answer. You basically either create a table with some known data (the bit of the SKU) and some dummy data (the xxxxxx) and then later replace the dummy data with real data... Or you write all your case when to make the colors at the time you make the table. – Caius Jard Nov 14 '19 at 17:05
Thank you very much Caius!! Im going to use this method of adding all the cases into the SELECT clause. Will learn the table route once I have things going. Thank you. – Z41N Nov 14 '19 at 17:12

Caius Jard · Answer 3 · 2019-11-14T16:46:02.513

0

I'm not totally familiar with redshift, but I think it's postgres based. I would expect your case to work if it looks like:

SELECT
  CASE SUBSTRING(product_sku,10,2)
    WHEN '09' THEN 'Black'
    WHEN '69' THEN 'Navy'
    WHEN ... THEN ...

    ELSE 'Unknown Color'
  END as color
FROM l_nvr_ec_returns

CASE can have two forms:

CASE something
  WHEN another_value_matches_something THEN return_value
  WHEN anotheranother_value_matches_something THEN returnanother_value
  ...

CASE 
  WHEN something = another_value_matches_something THEN return_value
  WHEN something = anotheranother_value_matches_something THEN returnanother_value

Which you choose depends on whether youre testing the same something or whether youre running different truth tests, like

CASE
  WHEN name = 'John' THEN ...
  WHEN age = 20 THEN ....

CASE typically has to have a single value expressed after its THEN. CASE in most DB cannot be used with expressions that do not produce a value

This could work:

WHERE 
  name =
  CASE WHEN age = 2  THEN (SELECT MAX(petname) FROM pets) ELSE 'noname' END

This probably won't:

WHERE 
  name IN
  CASE WHEN age = 2  THEN (SELECT name FROM pets) ELSE (SELECT 'noname') END

This is probably a syntax error:

WHERE 
  CASE WHEN SUBSTRING(x, 1, 2) = 'ab' THEN SUBSTRING(x, 5, 2) = 'cd'

I say probably because MySQL would probably evaluate SUBSTRING(x, 5, 6) = 'cd' as a boolean and return true or false depending on whether chars 5 and 6 of x were cd, but most major DBs wouldn't evaluate it as a boolean

edited Nov 14 '19 at 16:46

answered Nov 14 '19 at 16:40

Caius Jard

72,509
5
49
80

Thanks Caius - now where exactly would I place this CASE statement? At the end of my entire query? – Z41N Nov 14 '19 at 16:42
You place it where you want to use it. If it's decoding two chars of an SKU to a color, then maybe like in my first example, in the SELECT block. If you're using it as a condition to find only black things then eg `SELECT * FROM products WHERE (CASE WHEN SUNSTRING(sku, blah, blah) = '09' THEN 'Black' ... ELSE 'Unknown' END) IN ('Blue', 'Unknown')` to find only blue and unknown articles.. Though for that you might not bother using a CASE, and just say `WHERE SUBSTRING(sku, x, y) IN ('09', '99')` – Caius Jard Nov 14 '19 at 16:49
You can use CASE anywhere in a query that you want to operate on a value, you can even have it in a JOIN ON condition – Caius Jard Nov 14 '19 at 16:49
Copy - I added it as my 2nd column in the SELECT statement, but now getting "INVALID SYNTAX" error. Updated my post to provide you with the entire code. – Z41N Nov 14 '19 at 16:55
It's hard to know what you're trying to achieve, but you've basically pasted a query that returns thousands of rows into your SELECT area of your main query.. It's not a valid syntax, because queries in the select area need to return only one value per row – Caius Jard Nov 14 '19 at 16:57
I added another answer to cater for this latest edit – Caius Jard Nov 14 '19 at 16:59

Forcing the color_code output to be a different string using CASE

3 Answers3

OR