Loading

Case Statement

Lynn Emery

13y1.7kSQL

Hi I am trying to add a virtual field via a substring, however I am having difficulties when trying to compare more the one substring.
For example

SELECT location, sales, Month, Year, CASE WHEN Substring([location], 1, 8) = '12345678' OR '24578914' THEN 'Location A' END AS Storage
FROM Sales

Does anyone know how to compare more than one substring value within a statement rather than creating a case for each.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

brunda k13y ago
Accepted answer
SELECT location, sales, Month, Year, CASE WHEN (Substring([location], 1, 8) = '12345678' OR Substring([location], 1, 8) = '24578914' ) THEN 'Location A' END AS Storage FROM Sales
0
Jignesh Trivedi13y ago
hi,

try...

SELECT location, sales, Month, Year,
CASE Substring([location], 1, 8) WHEN '12345678' THEN 'Location A' WHEN '24578914' THEN 'Location B' ELSE 'Location C' END AS Storage
FROM Sales

hope this will solve your problem.
0