SQL - Capture result of where comparison as ad hoc column

Question

I've got a SQL question. I want to use case logic to determine the value of an ad hoc column based on the result of comparisons done in the where clause. Simplified, I want to do something like this:

select t.id,
(case
    when cond1 then "lbl1" + t2.v1
    when cond2 then "lbl2" + t2.v1)
from
    tbl1 as t left join tbl2 as t2 
where 
    ( cond1 || cond2 )

The problem is I don't want to recompute cond1 and cond2 in the select clause, as they're expensive.

How can I get this result?

Thanks, frood

include some sample data with expected output? Which RDBMS? mysql, sqlserver or oracle? tag only one — HaveNoDisplayName, Jul 23 '15 at 13:16
don't know that you'll be able to do this as a simple view. Maybe a stored proc. Declare variables at the beginning (maybe bits) then use them in place of your cond1 and cond2? — Gene, Jul 23 '15 at 13:25
@ comment 1 - I am looking for an answer for each RDBMS, or agnostic SQL — froodley, Jul 23 '15 at 13:29
@Gene - Can you give an example of how you'd use variables to solve the problem? — froodley, Jul 23 '15 at 13:29
A UNION or UNION ALL of a cond1-only query with a cond2-only query would work. — Tab Alleman, Jul 23 '15 at 13:32
The answers here vary based on the DBMS you are using. Surely you aren't really using all three? In sql server you could use a cte or a correlated subquery so you don't have to calculate a second time. Perhaps the calculation for these conditions could be made simpler. Typically a where predicate should not be so overly complicated that this is a major concern. — Sean Lange, Jul 23 '15 at 13:34
@TabAlleman - This is an attempt to optimize an earlier version of a query. The original query does what you're suggesting (actually it does two left joins against t2, capturing cond1 results and then cond2). I am trying to avoid iterating t1 twice, to do both comparisons in one pass and only capture one value. — froodley, Jul 23 '15 at 13:50
Mmm, then nope, I'm pretty sure it can't be done. I can't think of any path to a solution that doesn't involve either two FROM clauses or using the cond1 & cond2 twice. However, I question whether the optimizer would really "recompute" the conditions in the query in your question. It might be smart enough to only compute it once. Have you checked this in the execution plan? — Tab Alleman, Jul 23 '15 at 13:58
@SeanLange - I really want to know how to do it in all three RDBMS. The calculation isn't that expensive, I just want to know how to avoid doing it twice. The expense comes from doing it in volume; these are large history tables. I want to re-use one or the other result immediately, so there should be no reason beyond a language limitation to recalculate it. — froodley, Jul 23 '15 at 14:13
Oh, and by the way, using "cond1 || cond2" only gives you the illusion that you're "avoiding iterating t1 twice". Behind the scenes the optimizer is doing a UNION, so you're not really optimizing at all. — Tab Alleman, Jul 23 '15 at 14:13
@SeanLange - I do think there may be a way with a correlated subquery, tho.... select t.id, (select (case logic) from t2) from t... Going to try that... thanks... — froodley, Jul 23 '15 at 14:16
Adding another column which is a select statement is going to be a big performance hit as it turns your query into RBAR no matter how you slice it. If it is really that complicated then just put the data from your select statement in a temp table or cte, maybe with an additional column to indicate which condition is met. I get the feeling you are way overthinking this. — Sean Lange, Jul 23 '15 at 14:18
Is it possible for cond1 and cond2 to be true at the same time? — Gene, Jul 23 '15 at 14:20
@SeanLange - Yeah, you're right on all counts. Thanks. Your answer is what I proposed; there are reasons internal to the software application it was shot down. The extra column indicating which condition was met is the information I'm looking to capture here, though; if I could do it in the CTE select clause without re-doing the comparisons, I could use the same thing here, right? — froodley, Jul 23 '15 at 14:31
@TabAlleman - Thanks for the info about the OR operator. This link is also informative. I finally got a minute to try this earlier, and my solution above was MUCH slower then doing two LEFT JOINS - I don't think I could have figured out why without your share. You rock, thanks... http://stackoverflow.com/questions/1013797/is-sql-in-bad-for-performance — froodley, Jul 23 '15 at 21:53
I'm afraid I don't have anything bookmarked. It's something I read a long time ago. — Tab Alleman, Jul 24 '15 at 13:14

Gene · Answer 1 · 2015-07-23T14:33:34.523

0

This was written in MS SQL 2008, not sure if it'll work in other RDBMS with the same code, but hopefully it gives you an idea of what might work. it would have to be iterated for each record, but overall would have to run each comparison once instead of twice for each record.

declare @cond1 as bit
declare @cond2 as bit

if 1=1 -- substitute with condition 1
    set @cond1 = 1 
ELSE
    set @cond1 = 0 

if 1=0 -- substitute with conditon 2
    set @cond2 = 1 
ELSE
    set @cond2 = 0


SELECT 
CASE WHEN @Cond1 = 1 THEN 'Condition 1 met'
     WHEN @Cond2 = 1 THEN 'Condition 2 met' 
     END 
WHERE @cond1 = 1 OR @cond2 = 1

edited Jul 23 '15 at 14:33

answered Jul 23 '15 at 14:24

Gene

392
6
15

I thought that's what you were getting at. I think the problem is going to come up in the if statements; I'm going to have to do the loop twice to answer both questions, right? The (original) issue is that both conditions involve t1 and t2, so I want to do both comparisons while it's on a row in t2, and output a value from t2 along with a string/whatever indicating which condition it met. – froodley Jul 23 '15 at 14:52
You shouldn't have to loop twice for the conditions, just once. Hard to say though without knowing what your conditions are. It also doesn't have to be done with two variables if only one can be true at a time. Can you post the conditions you are using, it would be easier to give a better answer. – Gene Jul 23 '15 at 15:23

SQL - Capture result of where comparison as ad hoc column

1 Answers1