SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 dynamic where clause and null values
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

droog
Starting Member

12 Posts

Posted - 04/16/2007 :  17:29:10  Show Profile  Reply with Quote
I found an article about implementing a dynamic where clause and am having a problem returning data when their are fields set to "allow nulls".

Here is the sql statement, all fields are set to not null except cust_country. Thanks!


DECLARE @Cus_Name varchar(30),
@Cus_City varchar(30),
@Cus_Country varchar(30)

SET @Cus_Name = NULL
SET @Cus_City = 'Paris'
SET @Cus_Country = NULL

SELECT Cus_Name,
Cus_City,
Cus_Country
FROM Customers
WHERE Cus_Name = COALESCE(@Cus_Name,Cus_Name) AND
Cus_City = COALESCE(@Cus_City,Cus_City) AND
Cus_Country = COALESCE(@Cus_Country,Cus_Country)

dinakar
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 04/16/2007 :  17:44:29  Show Profile  Visit dinakar's Homepage  Reply with Quote
What is the problem you have? Is it s synax error? logical error?


************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

droog
Starting Member

12 Posts

Posted - 04/16/2007 :  17:55:03  Show Profile  Reply with Quote
It wont return anything. If all fields are "not null" it works, but if i have any fields "allow nulls" it doesnt.
Go to Top of Page

dinakar
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 04/16/2007 :  18:08:32  Show Profile  Visit dinakar's Homepage  Reply with Quote
Try this:


SELECT Cus_Name,
Cus_City,
Cus_Country
FROM Customers
WHERE Cus_Name = (CASE WHEN @Cus_Name IS NULL THEN Cus_Name ELSE @Cus_Name END )
AND Cus_City = (CASE WHEN @Cus_City IS NULL THEN Cus_City ELSE @Cus_City END )
AND Cus_Country = (CASE WHEN @Cus_Country IS NULL THEN Cus_Country ELSE @Cus_Country END )


************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

droog
Starting Member

12 Posts

Posted - 04/16/2007 :  18:19:41  Show Profile  Reply with Quote
I am sure that works, but it defeats the purpose of using COALESCE. It seems to be more robust, clean and straightforward. I just want to know why it isn't returning records if i have a field that allows nulls.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/16/2007 :  18:52:31  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Your requirements do not make logical sense if your column allows NULLS.

please read:

http://weblogs.sqlteam.com/jeffs/archive/2007/03/14/60133.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

dinakar
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 04/16/2007 :  18:53:23  Show Profile  Visit dinakar's Homepage  Reply with Quote
Theoretically it should be better since using functions on column names especially in WHERE clauses can force table scans..


************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37316 Posts

Posted - 04/16/2007 :  19:09:47  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by dinakar

Theoretically it should be better since using functions on column names especially in WHERE clauses can force table scans..




I don't believe that's the case in his situation since the column is isolated on one side of the predicate:

WHERE ColumnName = SomeFunction(...)

Perhaps this is what you are referring to:

WHERE SomeFunction(ColumnName, ...) = SomeValue

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Edited by - tkizer on 04/16/2007 19:10:15
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/16/2007 :  19:29:13  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Again, coding is not the issue. Read the link.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

dinakar
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 04/16/2007 :  19:43:04  Show Profile  Visit dinakar's Homepage  Reply with Quote
quote:
Originally posted by tkizer

quote:
Originally posted by dinakar

Theoretically it should be better since using functions on column names especially in WHERE clauses can force table scans..




I don't believe that's the case in his situation since the column is isolated on one side of the predicate:

WHERE ColumnName = SomeFunction(...)

Perhaps this is what you are referring to:

WHERE SomeFunction(ColumnName, ...) = SomeValue

Tara Kizer
http://weblogs.sqlteam.com/tarad/



you are right Tara.


************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/16/2007 :  20:22:17  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
quote:
Originally posted by tkizer

quote:
Originally posted by dinakar

Theoretically it should be better since using functions on column names especially in WHERE clauses can force table scans..




I don't believe that's the case in his situation since the column is isolated on one side of the predicate:

WHERE ColumnName = SomeFunction(...)

Perhaps this is what you are referring to:

WHERE SomeFunction(ColumnName, ...) = SomeValue

Tara Kizer
http://weblogs.sqlteam.com/tarad/



Actually, that is not correct, the column is not isolated on one side ...
The column is referenced on BOTH sides.

This is actually the situation:

WHERE ColumnName = SomeFunction(... ColumnName ... )


- Jeff
http://weblogs.sqlteam.com/JeffS

Edited by - jsmith8858 on 04/16/2007 20:23:02
Go to Top of Page

droog
Starting Member

12 Posts

Posted - 04/16/2007 :  20:59:29  Show Profile  Reply with Quote
Well i am officially more confused as ever. I read your article and you say:

"(a) come up with an alternate filtering specification; i.e., change the specification to fit your environment
or
(b) edit your schema to disallow NULLs in that column; i.e., change your environment to fit the specification"

So lets say i have a table that looks like:
articleID (PK, int, not null)
articleTitle (varchar(20), not null)
articleAuthID (FK, int, not null)
articleCreateDate (smalldatetime, not null)
articleScheduleDate (smalldatetime, null)

And i want to allow end users to have the ability to search all fields. Lets say in this case we want to search all articles created in the last week that haven't yet been scheduled.

I can't very easily make articleScheduleDate a 'not null' field. So 'b' doesn't seem to be much of a choice. And 'a', are you suggesting creating a separate query statement just for this?

What would you do?
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/17/2007 :  08:11:30  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
You need to come up with a new specification.

Right now, your specification is:


if @ColumnName parameter is null, then don't filter on that column. Otherwise, show only rows where ColumnName = @ColumnName.


Right?

Well, if ColumnName is nullable, then this spec won't work. So you need to come up with a new one. Something like this (for date columns for example):


if @ColumnName = '1/1/1900', then don't filter on that column. Otherwise, show only rows where ColumnName = @ColumnName. If @ColumnName is null, show only rows where ColumnName is null.


Which you could then implement like this:

where ((@ColumnName = '1/1/1900') or 
       (@ColumnName is null AND ColumnName is null) or 
       (ColumnName = @ColumnName)
      )

It's long, ugly, and inefficient, but that's what you need to deal with when implementing an optional filter on a nullable column using a single parameter.

You also could accept two parameters per column that is nullable, like this:

@CreateDate datetime, @CreateDateAll bit

and specify that (for example):


if @CreateDateAll is set to "1", you don't filter on CreateDate (i.e., the @CreateDate param is ignored), otherwise you filter where CreateDate = @CreateDate.


Again, now that we have a clear, logical specification, we can implement it:


where (@CreateDateAll = 1 OR @CreateDate = articleCreateDate OR (@CreateDate is null AND articleCreateDate is null))


still kind of long, but that's the logic.

- Jeff
http://weblogs.sqlteam.com/JeffS

Edited by - jsmith8858 on 04/17/2007 08:13:27
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 04/17/2007 :  08:29:43  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
I don't see how you are finding problem in this. I am getting correct output.

-- prepare sample data
declare @t table
(
	cus_name varchar(50),
	cus_city varchar(50),
	cus_country varchar(50)
)

insert into @t
select 'aaa', 'new york', 'US' union all
select 'bbb', 'paris', 'France' union all
select 'ccc', 'bombay', 'India'

declare
	@Cus_Name varchar(30),
	@Cus_City varchar(30),
	@Cus_Country varchar(30)

SET @Cus_Name = NULL
SET @Cus_City = 'Paris'
SET @Cus_Country = NULL

-- this query works
select * from @t
WHERE 
	Cus_Name = COALESCE(@Cus_Name,Cus_Name) AND
	Cus_City = COALESCE(@Cus_City,Cus_City) AND
	Cus_Country = COALESCE(@Cus_Country,Cus_Country)


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/17/2007 :  08:48:56  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
harsh -- if there are null values in the data, they are never returned.

Again, the problem is not code. It is that logically what he is trying to do will not work, no matter how you code it up in T-SQL or any other language.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 04/17/2007 :  08:55:28  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
Oh...now I got the problem...it has nothing to do with the values we set for the parameter, but with the table data itself.

How about this then?

-- prepare sample data
declare @t table
(
	cus_name varchar(50),
	cus_city varchar(50),
	cus_country varchar(50)
)

insert into @t
select 'aaa', 'new york', 'US' union all
select NULL, 'paris', NULL union all
select 'ccc', 'bombay', 'India'

declare
	@Cus_Name varchar(30),
	@Cus_City varchar(30),
	@Cus_Country varchar(30)

SET @Cus_Name = NULL
SET @Cus_City = 'Paris'
SET @Cus_Country = NULL

-- this query works
select * from @t
WHERE 
	(Cus_Name = COALESCE(@Cus_Name,Cus_Name) or cus_name is NULL) AND
	(Cus_City = COALESCE(@Cus_City,Cus_City) or Cus_City is NULL) AND
	(Cus_Country = COALESCE(@Cus_Country,Cus_Country) or Cus_Country is NULL)


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/17/2007 :  09:19:33  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Again, it is not a coding problem. it is a logic problem. That code will ALWAYS return all NULL values in those columns, even if you pass in a parameter that says "only return 'Paris'".

For the (hopefully) final time: YOU CANNOT WRITE CODE TO "FIX" THIS PROBLEM. The problem is in the specifications themselves, they do not make logical sense and will not work if the column is nullable!!



- Jeff
http://weblogs.sqlteam.com/JeffS

Edited by - jsmith8858 on 04/17/2007 09:21:02
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 04/17/2007 :  09:28:07  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
But then there is conflict in requirements. If OP wants only non-null values (which satisfies given condition) to be returned, my first solution will do that. If OP wants NULL values also to be included, my second solution will work.

Now, he has to decide how he wants the output to be.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/17/2007 :  09:40:02  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
>> But then there is conflict in requirements.

That is my ENTIRE point, which I've now repeated quite a few times!



- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 04/17/2007 :  09:53:32  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
But while we are doing all the discussions and suggesting solutions, where is OP? Doesn't even bother to clarify what he wants?


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/17/2007 :  09:57:31  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
He is in there, he read my article, asked a question regarding advice on changing his requirements, and I responded. It's all right there, a few posts back.

The problem is, people keep going back and forth trying to write a SQL solution for him, when that is NOT what he needs.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000