Replace SQL Case statement with nested if's or switch ?

[Xoom]

Hi All,

Long time reader but first time poster. I did search through a few other posts but couldn't quite land on an answer hence my post.

I have a case statement written in SQL as follows which I am trying to replicate via a workflow:

case when store_id in ('AAA', 'BBB') and permission = 'Y' then '8'
  when permission = 'Y' then '7'
  when store_id in ('AAA', 'BBB') then '2'
  else '1'
end as ID

I have done this successfully by adding a formula tool and created a new field with the below logic. From my testing this seems to provide the same as the above.

if [store_id] in ('AAA', 'BBB') and [permission] = 'Y' then 8
elseif [permission] = 'Y' then 7
elseif [store_id] in ('AAA', 'BBB') then 2
else 1
endif

I had thought the switch statement would be a better approach so I tried the below however this resulted in all the values returning as 1. I have used the switch statement before where the logic was only predicated on a single field e.g. month=1, 'JAN' etc but never like this scenario.

This was the code I tried with the switch:

Switch([ID],1,([store_id] in ('AAA', 'BBB') and [permission] = 'Y'), 8,
([permission] = 'Y'), 7,
([store_id] in ('AAA', 'BBB')), 2)

Have I mis-understood the use of the switch for this scenario or is it not possible to have this done via a switch and should I stick with my nested IF approach.

Your assistance would be much appreciated.

Thx

[Kaviyarasan_P]

Using Switch for your scenario is difficult. Below are the difficulties: 

1. Assigning the default value.
2. Multi-condition in the case.

If you are getting the answer in the  IF condition then please use IF condition.

[mbarone · Accepted solution]

For the Switch function:

Switch(Value,Default,Case1,Result1,...,CaseN,ResultN)

The Cases (Case1, Case2, . . , CaseN) can only be values, not other functions or operators/expressions.  So you will have to stick to your IF statements.

[Xoom]

---

@mbarone wrote:

For the Switch function:

Switch(Value,Default,Case1,Result1,...,CaseN,ResultN)

The Cases (Case1, Case2, . . , CaseN) can only be values, not other functions or operators/expressions.  So you will have to stick to your IF statements.

---

Thx @mbarone .... yeah ive just stuck with my nested if's ...... not ideal when coming from a java/dev background but it'll do.

Thx again

[mbarone]

Welcome!