March 18, 2015 at 3:57 am
Suppose I have added a xyz bit column in mytable. From now onwards new values inserted in mytable will have 0 or 1 in xyz column but the values that were previously stored will have NULL value in xyz column.
Now I want to write a query in which I will not give xyz parameter always like it can be null or value.
select * from mytable
where class='something'
and xyz is null
or
select * from mytable
where class='something'
and xyz = 1
Now how to write same query for both cases.
March 18, 2015 at 3:58 am
select * from mytable
where class='something'
and (xyz is null OR xyz = 1);
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 18, 2015 at 4:03 am
select * from mytable
where class='something'
and (xyz is null OR xyz = 1);
But xyz can either be NULL or 1. I mean
select * from mytable
where class='something'
and xyz IS NULL
and
select * from mytable
where class='something'
and xyz = 1
March 18, 2015 at 4:06 am
select * from mytable
where class='something'
and ISNULL(xyz, 99) = 1);
March 18, 2015 at 4:09 am
rexyrexin (3/18/2015)
select * from mytablewhere class='something'
and (xyz is null OR xyz = 1);
But xyz can either be NULL or 1. I mean
Thats why you have OR in between to cover both cases (OR im not getting it :hehe:)
March 18, 2015 at 4:23 am
select * from mytable
where class='something'
and ISNULL(xyz, 99) = 1);
What is 99?
Thats why you have OR in between to cover both cases (OR im not getting it [Hehe] )
But that I will bring result of both cases. I want only records when xyz = 1 at a time.
March 18, 2015 at 4:28 am
Is your "1" a parameter/variable that you are providing?
March 18, 2015 at 4:30 am
rexyrexin (3/18/2015)
select * from mytablewhere class='something'
and ISNULL(xyz, 99) = 1);
What is 99?
A dummy value. Something that is definately not equal to 1.
rexyrexin (3/18/2015)
Thats why you have OR in between to cover both cases (OR im not getting it [Hehe] )But that I will bring result of both cases. I want only records when xyz = 1 at a time.
Then what's wrong with this?
select * from mytable
where class='something'
and xyz = 1
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 18, 2015 at 4:37 am
ok If your "1" value is in fact a parameter and you want your results to be based on that parameter having a null value or 1 or 0 you can use a dummy value like already mentioned and write something like below:
@Param
select *
from mytable
where class='something'
and ISNULL(xyz,-1) = ISNULL(@Param, -1)
-1 is a dummy value to be able to compare NULL's, taking into account xyz column won't have different values than 0 or 1 or NULL
March 18, 2015 at 4:42 am
a sample set and desired output will help to identify your actual scenario.
March 18, 2015 at 4:44 am
rexyrexin (3/18/2015)
I got the answer:select * from mytable
where class='something'
AND ISNULL(xyz,0) = CASE WHEN @abc = 1 THEN 1 ELSE ISNULL(xyz,0) END
Here @abc is the stored procedure parameter .
Thanks all for replying.
If your @abc is other than 1 than the query will return all rows (where xyz is 1, 0 and null)
March 18, 2015 at 4:48 am
twin.devil (3/18/2015)
a sample set and desired output will help to identify your actual scenario.
+1
A lot of confusion could have been avoided.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 18, 2015 at 7:09 am
rexyrexin (3/18/2015)
I got the answer:select * from mytable
where class='something'
AND ISNULL(xyz,0) = CASE WHEN @abc = 1 THEN 1 ELSE ISNULL(xyz,0) END
Here @abc is the stored procedure parameter .
Thanks all for replying.
A slow answer as the predicates are non-sargable. Try this instead:
SELECT *
FROM mytable
WHERE class = 'something'
AND (
(@abc = 1 AND xyz = 1) OR (@abc IS NULL AND xyz IS NULL)
)
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply