In my query, in Microsoft SQL, I want to say like:
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?
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 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
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
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?
When you do a case you might do something like this
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 -
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.
1 Like
for example purposes
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.
Let’s just start with the top one and pretend your two other case statements don’t exist.
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?
1 Like
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.
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.
2 Likes
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.
6 Likes
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.