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