This topic contains 4 replies, has 0 voices, and was last updated by BrianE 6 years, 9 months ago.
-
Posts
-
bradsimpson- Contributions: 0
- Level 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: 0
- Level 1
- ☆
You could either use the INSTR() function or SUBSTR() function depending on how the field is structured.
-
NSThriver- Contributions: 0
- Level 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: 0
- Level 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: 0
- Level 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.
You must be logged in to reply to this topic.