[bradsimpson Contributions: 0Level 1☆]

I've got a transaction search where Department is one of the results. I want to convert our sales departments into the regions we use (North and South). The departments are written like this: North Sales : CIN-DAY District, South Sales : Florida District etc… I need to use some SQL to turn those into their top level regions. North or South. Here is what I've come up with and it does not work.

CASE {department} WHEN LIKE 'North%' THEN 'North' ELSE {department} WHEN LIKE 'South%' THEN 'South' ELSE 'No Region Identified' END

Any suggestions?
This is a cached copy. Click here to see the original post.

[NSThriver Contributions: 0Level 1☆]

You could either use the INSTR() function or SUBSTR() function depending on how the field is structured.

[NSThriver Contributions: 0Level 1☆]

Forgot to provide an example. This one is contrived but you can easily adopt it for your needs

case when instr({account}, 'Purchase') > 0 then 'Purchase' when instr({account}, 'Sales') > 0 then 'Sales' else 'DNE' end

[MarthaHakes Contributions: 0Level 1☆]

I use this formula to convert our branch location into a number. Our locations are listed as South Central and South East. Hope this helps

CASE WHEN LOWER({location}) LIKE '%central%' THEN '10245248' WHEN LOWER({location}) LIKE '%east%' THEN '10267359' ELSE {location} END

[BrianE Contributions: 0Level 1☆]

It is usually easier to write the CASE statement using separate WHEN conditions followed by an ELSE:

Code:
CASE
WHEN {department} LIKE 'North%' THEN 'North'
WHEN {department} LIKE 'South%' THEN 'South'
ELSE 'No Region Identified'
END
This is the same form as suggested by MarthaHakes.

[Author]

Posts