February 7, 2020 at 11:03 am
What do you think is the best way to get rid of this attached CASE statement? Extend the Join ..? Or a differently shaped WHERE clause? What will it look like? I kind of got stuck ... (see attached Select statement).
...........
FROM Popup_Switches AS PS WITH (NOLOCK)
JOIN #Temp_1 AS T
ON T.node_order = 1
WHERE 1 = CASE
WHEN T.record_Key_4 = '1' OR T.Record_Key_5 NOT IN ('I', 'X')
THEN CASE WHEN PS.popup_gid = 2 THEN 1 ELSE 0 END
ELSE
CASE WHEN PS.popup_gid = 5 THEN 1 ELSE 0 END
END;
Thank you.
Likes to play Chess
February 7, 2020 at 11:29 am
This is probably simpler, although it may or may not be more efficient. Remember to test it to destruction for equivalence.
WHERE ((T.record_Key_4 = '1' OR T.Record_Key_5 NOT IN ('I', 'X')) AND PS.popup_gid = 2)
OR (T.record_Key_4 <> '1' AND T.Record_Key_5 IN ('I', 'X') AND PS.popup_gid = 5 )John
February 7, 2020 at 1:07 pm
Thanks.
what is "destruction for equivalence"?
Likes to play Chess
February 7, 2020 at 1:55 pm
Two separate concepts. To test to destruction is to test until you can think of no more scenarios for which to test. Equivalence means making sure that my solution always returns the same result as yours does.
John
February 7, 2020 at 3:35 pm
It's an unusual query. There's no join condition so it's really a CROSS JOIN. I would guess the temp table always returns 1 row. No matter what this query will apply every row from T to every row from Popup_Switches where the WHERE clause is true. So really it only matters if the conditions on T are true and PS has popup_gid in(2,5). At least afaik I think it's this way
/* these 2 queries are equivalent */
with
ps_cte(popup_gid) as (
select 1 union all select 2 union all select 3 union all select 4 union all select 5),
t_cte(node_order, Record_Key_4, Record_Key_5) as (
select 1, '1', 'Y')
select
t.*, ps.*
from
ps_cte ps
cross join
t_cte t
WHERE
ps.popup_gid in(2, 5)
and t.node_order=1
and iif(((T.record_Key_4 = '1' OR T.Record_Key_5 NOT IN ('I', 'X')) and PS.popup_gid = 2), 1, iif(PS.popup_gid = 5, 1, 0))=1;
with
ps_cte(popup_gid) as (
select 1 union all select 2 union all select 3 union all select 4 union all select 5),
t_cte(node_order, Record_Key_4, Record_Key_5) as (
select 1, '1', 'Y')
select
t.*, ps.*
from
ps_cte ps
cross join
t_cte t
WHERE
ps.popup_gid in(2, 5)
and t.node_order=1
and (T.record_Key_4 = '1' or T.Record_Key_5 NOT IN ('I', 'X'));
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
February 7, 2020 at 4:04 pm
WHERE
(PS.popup_gid = 5)
OR
(PS.popup_gid = 2 AND T.record_Key_4 = '1')
OR
(PS.popup_gid = 2 AND T.Record_Key_5 NOT IN ('I', 'X'))
Add an extra column to your temp table:
MyFilter = CASE WHEN T.record_Key_4 = '1' OR T.Record_Key_5 NOT IN ('I', 'X') THEN 1 ELSE 0 END
Then you can change your WHERE clause to this:
WHERE
(PS.popup_gid = 5)
OR
(PS.popup_gid = 2 AND t.MyFilter = 1)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 7, 2020 at 4:12 pm
WHERE PS.popup_gid = 5 OR
(PS.popup_gid = 2 AND (T.record_Key_4 = '1' OR T.Record_Key_5 NOT IN ('I', 'X')))
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 7, 2020 at 4:31 pm
I'm not sure that all the answers given in this thread are correct?
The WHERE clause, if indented, looks like this:
WHERE 1 = CASE WHEN T.record_Key_4 = '1' OR T.Record_Key_5 NOT IN('I', 'X')
THEN CASE WHEN PS.popup_gid = 2 THEN 1
ELSE 0
END
ELSE CASE WHEN PS.popup_gid = 5 THEN 1
ELSE 0
END
END;Which can be rewritten (using De Morgan's law) to be:
WHERE ((T.record_Key_4 = '1' OR T.Record_Key_5 NOT IN('I', 'X')) AND PS.popup_gid = 2)
OR ((T.record_Key_4 <> '1' AND T.Record_Key_5 IN('I', 'X')) AND PS.popup_gid = 5)Which is the same as John Mitchell's answer but not the same as other answers.
For example if:
T.record_Key_4 = '1'
T.Record_Key_5='X'
PS.popup_gid = 5
this should not result in True
February 7, 2020 at 4:32 pm
...
from
Popup_Switches ps
cross join
#temp_1 t
where
ps.popup_gid in(2, 5)
and t.node_order=1
and (t.record_Key_4 = '1' or t.Record_Key_5 NOT IN ('I', 'X'));
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
February 7, 2020 at 4:38 pm
WHERE 1 = CASE
WHEN T.record_Key_4 = '1' OR T.Record_Key_5 NOT IN('I', 'X') THEN CASE WHEN PS.popup_gid = 2 THEN 1 eLSE 0 END
ELSE CASE WHEN PS.popup_gid = 5 THEN 1 ELSE 0 END
END;
This works for me.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 7, 2020 at 4:51 pm
Well. Testing. So far it doesn't.
In some scenarios it does
But doing more testing now...
Likes to play Chess
February 7, 2020 at 4:53 pm
Yeah I'm getting counterexamples too including my own. Which columns can be NULL?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
February 7, 2020 at 4:55 pm
I'm not sure that all the answers given in this thread are correct?
The where clause, if indented, looks like this:
WHERE 1 = CASE WHEN T.record_Key_4 = '1' OR T.Record_Key_5 NOT IN('I', 'X')
THEN CASE WHEN PS.popup_gid = 2 THEN 1
ELSE 0
END
ELSE CASE WHEN PS.popup_gid = 5 THEN 1
ELSE 0
END
END;Which can be rewritten (using De Morgan's law) to be:
WHERE ((T.record_Key_4 = '1' OR T.Record_Key_5 NOT IN('I', 'X')) AND PS.popup_gid = 2)
OR ((T.record_Key_4 <> '1' AND T.Record_Key_5 IN('I', 'X')) AND PS.popup_gid = 5)Which is the same as John Mitchell's answer but not the same as other answers.
For example if:
T.record_Key_4 = '1'
T.Record_Key_5='X'
PS.popup_gid = 5this should not result in True
You forgot to take into account NULL values. A condition not being true is not the same as the condition being false. It could also be unknown.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 7, 2020 at 5:00 pm
You forgot to take into account NULL values. A condition not being true is not the same as the condition being false. It could also be unknown.
Drew
Yes, I was referring to whether or not it would be filtered in the WHERE clause. I also assumed that the columns were all NOT NULL. If they are nullable then for each not-equal they would also have to complicate the where clause by adding "or colX is null". (A good reason to avoid nullable columns wherever possible).
Here it is accounting for a nullable record_Key_4 and Record_Key_5
WHERE ((T.record_Key_4 = '1' OR T.Record_Key_5 NOT IN('I', 'X')) AND PS.popup_gid = 2)
OR ((T.record_Key_4 <> '1' or T.record_Key_4 IS NULL)
AND ((T.Record_Key_5 IN('I', 'X')) OR T.Record_Key_5 IS NULL) AND PS.popup_gid = 5)I actually think this looks worse than the original CASE statement.
Also, some of the answers assumed that "PS.popup_gid = 5" always resulted in the row getting through the filter, which is not correct.
February 7, 2020 at 5:14 pm
Here's how I'm testing it. I'm switching over to John and Jonathan's in the case where there are not nullable columns. Still with the CROSS JOIN. Wait a sec
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply