Case Statement

Question

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.

brunda k · 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

Jignesh Trivedi · Answer

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.