May 17, 2012 at 2:09 pm
Ok dudes and dudettes. I'm suffering from drain bramage today and I can't figure out a better way to do this.
Example. Simple table. Col_1 has Int 1 through 100. Col_2 has the same. Order is random so any number can be paired with any number.
Ok. The usr has requested everything from the report that already exists, but says, If Col_1 is 25, then Col_2 must be 30. SO.. I can build a flag to highlight those instances that are incorrect:
CASE WHEN COL_1 = 25 AND COL_2 <> 30 THEN 'DOH!!!'
ELSE ''
END 'DOH - FLAG'
But if the user says he wants those eliminated from the recordset... and I'm drawing a complete blank. All the other records are fine, just exclude records where Col_1 = 25 and Col_2 <> 30....
Someone please unplug my brain....
May 17, 2012 at 2:17 pm
OK.. Case statement works, but is it the best way....
.....
AND (CASE WHEN COL_1 = 25 AND COL_2 <> 30 THEN 1
ELSE 0
END) = 0
easy to test...
but is it the best? Hey... that rhymes....
May 17, 2012 at 2:22 pm
CptCrusty1 (5/17/2012)
OK.. Case statement works, but is it the best way.........
AND (CASE WHEN COL_1 = 25 AND COL_2 <> 30 THEN 1
ELSE 0
END) = 0
easy to test...
but is it the best? Hey... that rhymes....
...
AND NOT (COL_1 = 25 and COL_2 <> 30)
May 17, 2012 at 2:22 pm
I'd look at using a work table with a good index on it to track the combinations. The best performance would likely be if you can set up a table with all valid combinations: that way you can simply add the truth table to your joins.
As in, instead of
select blah from mytable
where
case when col1=25 and col2=30 then 1 --valid combo
when col1=30 and col2=45 then 1 --anoth valid combo
--etc...
else 0
end = 1
use a query that looks more like:
select blah
from mytable
join truthTable
on mytable.col1=truthtable.col1 and
mytable.col2=truthtable.col2
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 17, 2012 at 2:23 pm
CptCrusty1 (5/17/2012)
OK.. Case statement works, but is it the best way.........
AND (CASE WHEN COL_1 = 25 AND COL_2 <> 30 THEN 1
ELSE 0
END) = 0
easy to test...
but is it the best? Hey... that rhymes....
A similar way:
AND NOT (COL_1 = 25 AND COL_2 <> 30)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 17, 2012 at 2:24 pm
RBarryYoung (5/17/2012)
CptCrusty1 (5/17/2012)
OK.. Case statement works, but is it the best way.........
AND (CASE WHEN COL_1 = 25 AND COL_2 <> 30 THEN 1
ELSE 0
END) = 0
easy to test...
but is it the best? Hey... that rhymes....
A similar way:
AND NOT (COL_1 = 25 AND COL_2 <> 30)
Barry, look up.
May 17, 2012 at 2:24 pm
!!! How did you guys get in front of me?!?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 17, 2012 at 3:12 pm
Lynn Pettis (5/17/2012)
CptCrusty1 (5/17/2012)
OK.. Case statement works, but is it the best way.........
AND (CASE WHEN COL_1 = 25 AND COL_2 <> 30 THEN 1
ELSE 0
END) = 0
easy to test...
but is it the best? Hey... that rhymes....
...
AND NOT (COL_1 = 25 and COL_2 <> 30)
Oooohhh... be careful. I haven't looked at this particular one but I've been bitten by this before. You would expect the conditions in the parenthesis to first be evaluated and then the NOT applied to the result but it doesn't always work that way. If you write it up and look at the formula in the execution plan, you might find that they've distributed the NOT and the resulting forumla may be transposed to (COL_1 <> 25 OR COL_2 = 30) which might not actually be what you want.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 17, 2012 at 3:13 pm
RBarryYoung (5/17/2012)
!!! How did you guys get in front of me?!?
DBCC TIMEWARP 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
May 17, 2012 at 3:17 pm
WHY is there an ORACLE add banner at the top of the SQL SERVER page??? PIRACY!!!!
May 17, 2012 at 3:18 pm
Jeff Moden (5/17/2012)
Lynn Pettis (5/17/2012)
CptCrusty1 (5/17/2012)
OK.. Case statement works, but is it the best way.........
AND (CASE WHEN COL_1 = 25 AND COL_2 <> 30 THEN 1
ELSE 0
END) = 0
easy to test...
but is it the best? Hey... that rhymes....
...
AND NOT (COL_1 = 25 and COL_2 <> 30)
Oooohhh... be careful. I haven't looked at this particular one but I've been bitten by this before. You would expect the conditions in the parenthesis to first be evaluated and then the NOT applied to the result but it doesn't always work that way. If you write it up and look at the formula in the execution plan, you might find that they've distributed the NOT and the resulting forumla may be transposed to (COL_1 <> 25 OR COL_2 = 30) which might not actually be what you want.
Buyer beware. If you don't give us enough to test, you are on your own.
May 17, 2012 at 3:19 pm
CptCrusty1 (5/17/2012)
WHY is there an ORACLE add banner at the top of the SQL SERVER page??? PIRACY!!!!
Redgate is now also building and selling Oracle tools.
May 17, 2012 at 3:21 pm
Lynn Pettis (5/17/2012)
Jeff Moden (5/17/2012)
Lynn Pettis (5/17/2012)
CptCrusty1 (5/17/2012)
OK.. Case statement works, but is it the best way.........
AND (CASE WHEN COL_1 = 25 AND COL_2 <> 30 THEN 1
ELSE 0
END) = 0
easy to test...
but is it the best? Hey... that rhymes....
...
Looks like I might actually just stick with the Case statement... Recordset is small, and report only runs twice a month... Might not be pretty, but would be ok.
AND NOT (COL_1 = 25 and COL_2 <> 30)
Oooohhh... be careful. I haven't looked at this particular one but I've been bitten by this before. You would expect the conditions in the parenthesis to first be evaluated and then the NOT applied to the result but it doesn't always work that way. If you write it up and look at the formula in the execution plan, you might find that they've distributed the NOT and the resulting forumla may be transposed to (COL_1 <> 25 OR COL_2 = 30) which might not actually be what you want.
Buyer beware. If you don't give us enough to test, you are on your own.
May 18, 2012 at 2:05 am
If i got your question right then you can use following..
with cte as
(select <your cols>,CASE WHEN COL_1 = 25 AND COL_2 <> 30 THEN 1
ELSE 0
END as DOHFLAG
)
select * from cte where dohflag = 1 --if you need only COL_1 = 25 AND COL_2 <> 30 this data
--select * from cte where dohflag = 0 --if you need data except COL_1 = 25 AND COL_2 <> 30
--select * from cte --if you need all the data..
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply