# SQL "Where" clause "Case" with multiple values in "Then"

**URL:** <https://forum.inductiveautomation.com/t/sql-where-clause-case-with-multiple-values-in-then/63624>\
**Category:** Ignition\
**Created:** [August 30, 2022, 7:42pm UTC](https://forum.inductiveautomation.com/t/sql-where-clause-case-with-multiple-values-in-then/63624 "2022-08-30T19:42:01Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [August 30, 2022, 7:42pm UTC](https://forum.inductiveautomation.com/t/sql-where-clause-case-with-multiple-values-in-then/63624/1 "2022-08-30T19:42:01Z")

</div>

In my query, in Microsoft SQL, I want to say like:

```auto
errorCode != case when :machine=1 then 158 to 160 
                  when :machine=3 then 87-95
else 0 end

```

Right now I am writing multiple cases like below. **Is there a way to do with fewer cases?**

```auto
code!= Case when :machine=1 or :machine=2 or (:machine >3 and :machine <7) or (:machine>7 and :machine<13) then 160 
							when :machine=3 or :machine=7 or :machine>13 then 95
							else 0 end and
code!= Case when :machine=1 or :machine=2 or (:machine >3 and :machine <7) or (:machine>7 and :machine<13) then 159 
							when :machine=3 or :machine=7 or :machine>13 then 94
							else 0 end and
code!= Case when :machine=1 or :machine=2 or (:machine >3 and :machine <7) or (:machine>7 and :machine<13) then 158 
							when :machine=3 or :machine=7 or :machine>13 then 93
							else 0 end and

```

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [August 31, 2022, 1:13pm UTC](https://forum.inductiveautomation.com/t/sql-where-clause-case-with-multiple-values-in-then/63624/2 "2022-08-31T13:13:13Z")

</div>

I take advantage of short circuit logic in databases a lot with the parameters inside of Ignition. I’m not quite sure what you are trying to do here as it appears your equalities for your three cases are all identical `code!= Case when :machine=1 or :machine=2 or (:machine >3 and :machine <7) or (:machine>7 and :machine<13) ` so I would imagine the first one will always be the one that executes.

However, quick example and hopefully it will set you on the right path. Imagine I have that is listing all sales orders associated with a customer, and a dropdown menu that lets me select what customer’s sales orders to see. The caveat being that if no one is selected (the dropdown is on selected All/selectedValue is -1), then I want to see all sales orders.

I can set that up like this -  
assuming that :customerId is a integer parameter

```auto
SELECT * 
FROM salesOrders
WHERE (:customerId =-1 OR salesOrders.parentCustomerId=:customerId)

```

So if `:customerId` is negative -1, that first clause of the OR statement is True and the second one is never evaluated. But if it `:customerId=-1` is false, then the filtering occurs.

You can do a similar and opposite with AND statements. `WHERE (:active=1 AND someTable.active=1)` would mean that if your parameter `:active` equals one and is True, then the second part of the clause is run (no short circuiting here).

There is nothing wrong with comparing parameters with case statements either, I do that as well, I think your issue is that all your comparisons are identical so I wouldn’t expect anything but the first case to ever come back.

Look at this thread and there’s a couple of SQL examples [Named query with many conditional WHERE clauses without query string? - #23 by kcollins1](https://forum.inductiveautomation.com/t/named-query-with-many-conditional-where-clauses-without-query-string/47880/23)

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [August 31, 2022, 1:16pm UTC](https://forum.inductiveautomation.com/t/sql-where-clause-case-with-multiple-values-in-then/63624/3 "2022-08-31T13:16:47Z")

</div>

What do you mean my cases are identical?  
One set should be 1,2,4-6,8-12.  
The other set should be 3,7,14 - the rest.  
Did I mess that up?

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [August 31, 2022, 1:22pm UTC](https://forum.inductiveautomation.com/t/sql-where-clause-case-with-multiple-values-in-then/63624/4 "2022-08-31T13:22:16Z")

</div>

When you do a case you might do something like this

```auto
CASE
    WHEN :someParam=0
        THEN 'someValue1'
    WHEN :someParam<0
        THEN 'someOtherValueLessThanZero'
    WHEN :someParam>0
        THEN 'someOtherValueGreaterThanZero'

```

And my cases are equal to 0, less than 0, or greater than zero.

Look at your script here -

```auto
code!= Case when :machine=1 or :machine=2 or (:machine >3 and :machine <7) or (:machine>7 and :machine<13) then 160 
							when :machine=3 or :machine=7 or :machine>13 then 95
							else 0 end and
code!= Case when :machine=1 or :machine=2 or (:machine >3 and :machine <7) or (:machine>7 and :machine<13) then 159 
							when :machine=3 or :machine=7 or :machine>13 then 94
							else 0 end and
code!= Case when :machine=1 or :machine=2 or (:machine >3 and :machine <7) or (:machine>7 and :machine<13) then 158 
							when :machine=3 or :machine=7 or :machine>13 then 93
							else 0 end and

```

~~I misread yours a bit due to the formatting but you really only have two cases  
`:machine=1 or :machine=2 or (:machine >3 and :machine <7) or (:machine>7 and :machine<13)` which will give you 160 or  
`:machine=3 or :machine=7 or :machine>13` which gives you 95. All your other statements WHEN conditions are one of these two, which mean they won’t be reached, because they will have turned into 160 or 95 already.~~

~~Like for 158 you have `:machine=1 or :machine=2 or (:machine >3 and :machine <7) or (:machine>7 and :machine<13)` - this will never be reached because if this statement is true, then it was true for 160.~~

~~If you’re able to show your full query and what your end goal is I could help you out a bit more.~~

EDIT: I think I am mistaken, I thought this was one full long case statement but I notice its three. I think there is a better way to write this. If you can explain what should go to what then I can still try to help you as I do think there’s probably a better way.

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [August 31, 2022, 1:25pm UTC](https://forum.inductiveautomation.com/t/sql-where-clause-case-with-multiple-values-in-then/63624/5 "2022-08-31T13:25:23Z")

</div>

for example purposes

```auto
select line, code, tstamp
from department1
where 
code!= Case when :machine=1 or :machine=2 or (:machine >3 and :machine <7) or (:machine>7 and :machine<13) then 160 
			when :machine=3 or :machine=7 or :machine>13 then 95
			else 0 end and
code!= Case when :machine=1 or :machine=2 or (:machine >3 and :machine <7) or (:machine>7 and :machine<13) then 159 
			when :machine=3 or :machine=7 or :machine>13 then 94
			else 0 end and
code!= Case when :machine=1 or :machine=2 or (:machine >3 and :machine <7) or (:machine>7 and :machine<13) then 158 
			when :machine=3 or :machine=7 or :machine>13 then 93
			else 0 end 

```

Each case is being reached because of the AND on the end.

My query is ridiculously complex at this point. So I just want to get this simpler version working.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [August 31, 2022, 1:26pm UTC](https://forum.inductiveautomation.com/t/sql-where-clause-case-with-multiple-values-in-then/63624/6 "2022-08-31T13:26:52Z")

</div>

Let’s just start with the top one and pretend your two other case statements don’t exist.

```auto
code!= Case when :machine=1 or :machine=2 or (:machine >3 and :machine <7) or (:machine>7 and :machine<13) then 160 
							when :machine=3 or :machine=7 or :machine>13 then 95
							else 0 end

```

How exactly do you want this to filter your table, what column should equal 160 or 95?

If this is not supposed to filter your table, what is the intended behavior of this statement for you?

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [August 31, 2022, 2:05pm UTC](https://forum.inductiveautomation.com/t/sql-where-clause-case-with-multiple-values-in-then/63624/7 "2022-08-31T14:05:10Z")

</div>

For one group of machines, I am trying to not show the timestamps of codes 160-155,97,96, and 82.

For the other group, I am trying not to show the timestamps of the codes 95-85, and 55 maybe.

---

<div class="post-metadata">

**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\
**Post date:** [August 31, 2022, 2:07pm UTC](https://forum.inductiveautomation.com/t/sql-where-clause-case-with-multiple-values-in-then/63624/8 "2022-08-31T14:07:36Z")

</div>

Don’t forget you can always manipulate data in scripting as well. Sometimes I find scripting to be a little easier to use vs SQL queries.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [August 31, 2022, 2:10pm UTC](https://forum.inductiveautomation.com/t/sql-where-clause-case-with-multiple-values-in-then/63624/9 "2022-08-31T14:10:25Z")

</div>

Make a permanent lookup table in the DB, perhaps named `excludecodes`, with non-null columns for `machine` and `code` (primary key). Add a left join to that table, then tack `AND excludecodes.code IS NULL` onto your WHERE clause.

Populate that table with every combination of machine and code that you want excluded.

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [August 31, 2022, 3:00pm UTC](https://forum.inductiveautomation.com/t/sql-where-clause-case-with-multiple-values-in-then/63624/10 "2022-08-31T15:00:04Z")

</div>

Is there some way to declare the machine and code status as the case “when” condition that makes a valid or invalid Boolean from the value assigned in “then”?

`code != Case ...`  
This part seems to confine case to output just one code to filter out.  
In the when part, I can isolate multiple conditions though.

Or can I have case result be a subset of values, and do an “in” check?

The joins add time to my queries, so I want to avoid that option if I can.