November 29, 2007 at 11:57 am
Hello All,
I have an issue with isnull. basically what iam trying to do is:
where
col1 = isnull(@variable,col1)
i.e when @variable is null then condition on the col1 should be igonored and query should return all the values, even those where col1 is null.
but it is not returning those where col1 is null.
Please help me resolve this issue.
I appreciate your help.
Thanks,
Sandy
November 29, 2007 at 12:08 pm
Thats a good site and will try to explain the answer to you.
November 29, 2007 at 12:09 pm
Use COALESCE. It returns the first NON-NULL value in the list.
WHERE
col1 = COALESCE(@variable, col1)
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 29, 2007 at 12:14 pm
Jason Selburg (11/29/2007)
Use COALESCE. It returns the first NON-NULL value in the list.
WHERE
col1 = COALESCE(@variable, col1)
Sorry Jason - gotta part ways with ya here...
Sandy is looking to return items using something like
where col1=@variable OR col1 is null
The COALESCE syntax above would never return anything null by design......
----------------------------------------------------------------------------------
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?
November 29, 2007 at 12:29 pm
Matt Miller (11/29/2007)
Jason Selburg (11/29/2007)
Use COALESCE. It returns the first NON-NULL value in the list.
WHERE
col1 = COALESCE(@variable, col1)
Sorry Jason - gotta part ways with ya here...
Sandy is looking to return items using something like
where col1=@variable OR col1 is null
The COALESCE syntax above would never return anything null by design......
Actually, I think it's a mixture of both. Yours will not return all rows if the @variable is null since NULL never equals NULL, and mine will not return rows where col1 is null.
So I think what would be needed is ...'
WHERE
ISNULL(col1,'') = COALESCE(@variable, ISNULL(col1,''))
Anywho, gotta run for a bit......
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 29, 2007 at 12:29 pm
Agreed... a 2 operand COALESCE is identical to ISNULL and has the same "problem" with finding nulls, in this case.
To expound upon the point that Matt makes, consider the following code...
where
col1 = isnull(@variable,col1)
If both @variable and col1 are null, the WHERE clause evaluates as follows...
where
col1= NULL
... and, unless you made the serious mistake of how the server handles null, you cannot compare against a null with signs of equality. It just doesn't work.
Matt's code of...
WHERE Col1 = @variable or col1 IS NULL
... is likely the correct way to do things here (can't see the rest of the code so not 100% sure).
--Jeff Moden
Change is inevitable... Change for the better is not.
November 29, 2007 at 12:36 pm
Jeff Moden (11/29/2007)
... and, unless you made the serious mistake of how the server handles null, you cannot compare against a null with signs of equality. It just doesn't work.Matt's code of...
WHERE Col1 = @variable or col1 IS NULL
... is likely the correct way to do things here (can't see the rest of the code so not 100% sure).
But unfortunately this doesn't answer the posters original need ...
when @variable is null then condition on the col1 should be ignored and query should return all the values, even those where col1 is null.
So if a NULL @variable is put in you won't return any records other than NULLS. Likewise, no matter what variable is passed, you will always get NULLs and I don't think that is what Susan wants
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 29, 2007 at 12:38 pm
Jeff was correct most of the way, this way you can get all the results back:
where col1 = isnull(@variable,col1) or (@variable is null and col1 is null)
I believe that should work, try it out.
November 29, 2007 at 1:32 pm
Jason - exhibit A:
sandygem2k (11/29/2007)query should return all the values, even those where col1 is null.
but it is not returning those where col1 is null.
So - the syntax should be the same as NO where clause when col1 is null....
Now - if you do want to do this using ISNULL, try
WHERE ISNULL(col1,@variable)=@variable
----------------------------------------------------------------------------------
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?
November 29, 2007 at 3:01 pm
Hello All,
Thank You all for your suggestions...
What I am looking for is when @variable is null then the condition on the col should be ignored.
I am using this in Reporting Services...
So I am came up with this ..
(case @test-2 when 'ALL' then 'ALL' else col1 end )= (case @test-2 when 'ALL' then 'ALL' else @test-2 end )
@test-2 should be ALL when the user does nt want to give it any value...
November 29, 2007 at 3:51 pm
FIRST: I'm not attacking anyone, I'm just convinced that my second answer (Only after Matt pointed out an error) will work.
SECOND: Ravi's code will work as well. 😀
Matt, I think you've misunderstood the question. Susan only wants all of the records of col1 when the @variable is null, not when col1 is null.
sandygem2k (11/29/2007)when @variable is null then condition on the col1 should be igonred and query should return all the values, even those where col1 is null.
but it is not returning those where col1 is null.
TEST DATA ....
Using Matt's first suggestion
DECLARE @table TABLE (col1 VARCHAR(10))
DECLARE @variable VARCHAR(10)
-- initialize the variables
INSERT @table
SELECT 'a' UNION ALL
SELECT 'a' UNION ALL
SELECT 'b' UNION
SELECT NULL
SET @variable = 'a'
SELECT col1
FROM @table
WHERE col1=@variable OR col1 is null
erroneously returns the null record ...
col1
----------
NULL
a[/B]
AND fails to return ALL records if @variable is NULL ...
SET @variable = NULL
SELECT col1
FROM @table
WHERE col1=@variable OR col1 is NULL
Incorrectly returns ...
col1
----------
NULL[/B]
Using my second attempt, after Matt pointed out an issue ...
SET @variable = NULL
SELECT col1
FROM @table
WHERE
ISNULL(col1,'') = COALESCE(@variable, ISNULL(col1,''))
Correctly returns ...
col1
----------
NULL
a
b
AND ...
SET @variable = 'a'
SELECT col1
FROM @table
WHERE
ISNULL(col1,'') = COALESCE(@variable, ISNULL(col1,''))
Correctly returns ...
col1
----------
a
Now Matt's second suggestion of ...
SET @variable = 'a'
SELECT col1
FROM @table
WHERE ISNULL(col1,@variable)=@variable
Incorrectly returns the record with the NULL value ...
col1
----------
NULL
a
Again, I'm just confident and not attacking. Now just imagine if I am actually wrong... LOL
What do you think now Jeff ... :Whistling:
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 29, 2007 at 6:41 pm
welll...then...good job!!!!!
:hehe::cool::w00t:;):P:D:)
----------------------------------------------------------------------------------
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?
November 29, 2007 at 8:26 pm
Heh... Jason, I'm sitting here reading where you wrote...
So if a NULL @variable is put in you won't return any records other than NULLS. Likewise, no matter what variable is passed, you will always get NULLs and I don't think that is what Susan wants
... and I'm thinking, "Yeah, he's right... I should'a had more coffee before I answered that".
It funny how a couple of us have gotten to the point where we preface things with "I'm NOT attacking anyone". Show's we got a couple of WTF-battle-scars 😛
What do you think now Jeff ...
I'm thinking I was wrong... that I needed more coffee. 😀 And I think you did a heck of a nice job with the test code to prove it! :w00t: Well done!
--Jeff Moden
Change is inevitable... Change for the better is not.
November 30, 2007 at 4:49 am
Jeff, Matt and whomever else is reading this ....
I think all that happened here is something that I KNOW I have been guilty of ... "Answering a question without really understanding all of it". I've done it a few times and I always end up with egg on my face. Heck, I did it on this post with my first reply. In the end I think it's the nature of our business. We are constantly (at least I am) expected to solve problems as quickly as possible. It's very easy for things like this to happen in that situation and difficult to slow down sometimes.
All in all, I'm glad we could work through this without getting feathers ruffled 🙂
One of the things I'm taking out of this thread is that I know of at least two more people that I can work on issues with professionally.
Thanks guys!
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 30, 2007 at 10:48 am
Heh... thanks for that, Jason. Hope a couple of other folks take heed of that little lesson...
In the meantime, what's the best way for me to get this dried egg out of my beard without cutting it? 😛 Oh, I know... I'll go "soak my head" for a while 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply