September 29, 2016 at 3:33 pm
xr280xr (9/29/2016)
drew.allen (9/26/2016)
Luis Cazares (9/26/2016)
drew.allen (9/26/2016)
xr280xr (9/26/2016)
I don't specifically care about short circuiting. I just need to only evaluate b if a is false and only evaluate c if a and b are false. It's not a performance issue to me, it's the logic I need. I was only pointing out that short circuiting allows that to be written very neatly. I'm hoping for a way to write it more neatly in t-sql or confirmation that it can't be.A CASE expression will do this. It evaluates the subsequent conditions only if all of the preceding conditions evaluate to FALSE or UNKNOWN (NULL). The CASE expression CANNOT return a Boolean value, so generally you want to return some token (usually 'T', 'True' or 1) to indicate when the condition is true and then compare that token to the expected value.
That being said, be very sure that you really need to evaluate them in the specified order.
Drew
DON'T USE A CASE STATEMENT IN A WHERE CLAUSE. That's just a recipe for disaster, as your queries become non-SARGable. The CASE statement should go in the column list if you need to assign that row to a group. In the WHERE clause, you don't care if it doesn't short circuit the condition.
Never say, "Never." As with anything else, you have to evaluate it within the context, which we simply don't have. There may be, and likely are, more efficient solutions, but it doesn't appear that the OP is willing to give us enough information to suggest such an alternate.
I asked what I was interested in, which was options for the simplification of a fundamental logical condition. I was interested in all cases, not just my current one. I thought someone might have a clever syntactic shortcut. I was curious about the case expression as an alternative which is why I marked it as an answer but I didn't actually end up using it in my current case. To some of the latter posts, again, the goal wasn't to cause short circuiting, but I appreciate your input on the subject.
Here's what I settled on. AllowsDeletionGracePeriod was a new column so I had to update the where clause to include it.
DELETE FROM tbl_a
WHERE [some additional criteria]
AND (DeleteAsap = 1 OR
(DeleteAsap = 0 AND AllowsDeletionGracePeriod = 1 AND CreatedDate <= DATEADD(d, -7, GETDATE())) OR
(DeleteAsap = 0 AND AllowsDeletionGracePeriod = 0 AND (LastTransactionDate IS NULL OR LastTransactionDate <= DATEADD(m, -6, GETDATE())))
)
Thanks for posting back. It looks clean and readable. No issues with performance I am guessing? If so remember that you can add individual indexes to the columns you use here.
----------------------------------------------------
September 29, 2016 at 3:58 pm
xr280xr (9/29/2016)
Here's what I settled on. AllowsDeletionGracePeriod was a new column so I had to update the where clause to include it.
DELETE FROM tbl_a
WHERE [some additional criteria]
AND (DeleteAsap = 1 OR
(DeleteAsap = 0 AND AllowsDeletionGracePeriod = 1 AND CreatedDate <= DATEADD(d, -7, GETDATE())) OR
(DeleteAsap = 0 AND AllowsDeletionGracePeriod = 0 AND (LastTransactionDate IS NULL OR LastTransactionDate <= DATEADD(m, -6, GETDATE())))
)
Assuming that DeleteAsap only allows the values 1 and 0 then these two are equivalent. That is, you don't need to worry about being mutually exclusive. You're trying to force an order to the evaluation which is unnecessary.
DELETE FROM tbl_a
WHERE [some additional criteria]
AND (DeleteAsap = 1 OR
(DeleteAsap = 0 AND AllowsDeletionGracePeriod = 1 AND CreatedDate <= DATEADD(d, -7, GETDATE())) OR
(DeleteAsap = 0 AND AllowsDeletionGracePeriod = 0 AND (LastTransactionDate IS NULL OR LastTransactionDate <= DATEADD(m, -6, GETDATE())))
)
DELETE FROM tbl_a
WHERE [some additional criteria]
AND (DeleteAsap = 1 OR
(AllowsDeletionGracePeriod = 1 AND CreatedDate <= DATEADD(d, -7, GETDATE())) OR
(AllowsDeletionGracePeriod = 0 AND (LastTransactionDate IS NULL OR LastTransactionDate <= DATEADD(m, -6, GETDATE())))
)
I can even show you the logic if you don't believe me.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply