Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Conditional Statements in WHERE Clauses Expand / Collapse
Author
Message
Posted Thursday, October 04, 2007 10:15 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, January 17, 2014 1:22 PM
Points: 727, Visits: 265
I had 1 or 2 approaches to this, but now I have a whole set of them.

This is such a textbook example of what a technical blog is all about! So great to see so many different solutions to one problem! All contributors are doing a great service to the community, regardless of your views!

Paul


- Paul

http://paulpaivasql.blogspot.com/
Post #406936
Posted Thursday, October 04, 2007 2:11 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 10, 2007 6:26 AM
Points: 6, Visits: 9
Wow. It's been 3 years and still there is a lot of activity on this post. Thank you all very much for your replies.



Adding to what I said then, the conditional approach is probably not suitable for large data sets or systems where response is critical. Smart joins, dynamic SQL or even application - level constructed query statements would probably do the job better there. It's better to think of this specific approach as flexibility vs performance. You can choose what you need more.



What you get with this approach is a flexible WHERE clause generator - without the generator :) Of course it's always possible to construct such a WHERE clause in other ways such as those mentioned above, but, in my opinion, it'd take more man-hours and it should only be done where it's worth it.


Sotiris L. Filippidis
Post #407076
Posted Wednesday, October 10, 2007 5:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:22 PM
Points: 131, Visits: 587
To be honest I didn't understand this article. First of all the example doesn't cover the first condition "All records that contain the word "the" in the company name", but it's fine because no german company has that in name, irrelevant however. To me this is the way to go:

select
customerid, companyname, country
from
customers
where
--companyname LIKE '%the%'
--AND
companyname NOT LIKE 'A%'
AND
country='Germany'

Plain and simple.


Don't just give the hungry man a fish, teach him how to catch it as well.

the sqlist

Post #408911
Posted Wednesday, October 10, 2007 6:08 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 10, 2007 6:26 AM
Points: 6, Visits: 9
The two conditions in the article were just examples of the wealth of criteria there are for the same query. They were two separate conditions which were not meant to be combined, and of course the example's purpose was not to demonstrate those specific conditions.



Just suppose those two conditions could be part of a larger array of conditions in a reporting UI, for example, and would apply depending on user's choices. Of course, the letter "A" or the string "the" would be parameters the user could specify in some kind of text box or something. In that case, you would have to use some conditional logic or dynamic SQL and not the plain SQL statement you typed. Otherwise, you'd have to have one different statement for every condition. :)


Sotiris L. Filippidis
Post #408919
Posted Wednesday, October 10, 2007 6:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:22 PM
Points: 131, Visits: 587
Sotiris Filippidis (10/10/2007)
Just suppose those two conditions could be part of a larger array of conditions in a reporting UI, for example, and would apply depending on user's choices. Of course, the letter "A" or the string "the" would be parameters the user could specify in some kind of text box or something. In that case, you would have to use some conditional logic or dynamic SQL and not the plain SQL statement you typed. Otherwise, you'd have to have one different statement for every condition. :)


Maybe the example chosen was not the best. However you don't need a dynamic SQL to solve the problem because you can build the query in the UI before sending it to the SQL server. In general trying to build a "good for any situation" condition is not easy and not very practical either.

Another thing is that the example only replaced the values of simple conditions with a case statement delivering 1 or 0 depending of the situation. In some special "weird" :o) situation this aproach might be useful, though, I agree.


Don't just give the hungry man a fish, teach him how to catch it as well.

the sqlist

Post #408930
Posted Wednesday, October 10, 2007 6:29 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 10, 2007 6:26 AM
Points: 6, Visits: 9
I agree, each approach can serve a different purpose and different needs. You can build the query dynamically at the BL, you can use dynamic SQL, *or* you can use this approach. It all depends on what you have to do and how you want to do it.

As for the 0 and 1 values, they exist to make specific parts of the CASE statement evaluate. It's not that it's actually returning 0 or 1! :)



Sotiris L. Filippidis
Post #408942
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse