CASE STATEMENT in WHERE CLAUSE

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » CASE STATEMENT in WHERE CLAUSE

CASE STATEMENT in WHERE CLAUSE

Rate Topic

Display Mode

Topic Options

Author

Message

johnj-747215

Posted Monday, June 08, 2009 5:45 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, June 09, 2009 12:36 PM
Points: 2, Visits: 4

I am trying to add a CASE to my WHERE clause that is a little more complex than I normally use. Normally, I would do something like the following. I haven't checked this SQL. It is just meant for display purposes.

SELECT *
FROM Users
WHERE LastLogin >
CASE @LastLogin
WHEN 'PastYear' THEN DATEADD(DAY, -365, GETDATE())
WHEN '90Days' THEN DATEADD(DAY, -90, GETDATE())
WHEN 'ThisMonth' THEN DATEADD(DAY, -30, GETDATE())
WHEN 'ThisWeek' THEN DATEADD(DAY, -7, GETDATE())
WHEN 'Today' THEN DATEADD(DAY, -1, GETDATE())
WHEN 'Online' THEN DATEADD(MINUTE, -30, GETDATE())
END

But, I want to do something a little more complex right now. This is what I currently have, but SQL Server 2008 doesn't like it. I have never been one to use CASE statements in WHERE clauses very much and so I have never written anything very complex. If someone could help me with this quickly, I would greatly appreciate it. Thank you in advance!

SELECT P.*,
I.*,
C.*,
T.*,
A.UserName,
A.Deleted AS 'AdminDeleted'
FROM dbo.Billing_Payments P
INNER JOIN dbo.Billing_Invoices I ON I.InvoiceID = P.InvoiceID
INNER JOIN dbo.Billing_Payments_Types T ON T.PaymentTypeID = P.PaymentTypeID
LEFT JOIN dbo.Billing_Cards C ON C.CardID = P.CardID
LEFT JOIN dbo.SystemAdmins A ON A.SystemAdminID = P.ModifiedBy
WHERE (
(P.PaymentID = @PaymentID OR @PaymentID IS NULL)
AND
(P.InvoiceID = @InvoiceID OR @InvoiceID IS NULL)
AND
(P.CardID = @CardID OR @CardID IS NULL)
AND
(P.PaymentTypeID = @PaymentTypeID OR @PaymentTypeID IS NULL)
AND
(P.Amount = @Amount OR @Amount IS NULL)
AND
(P.Success = @Success OR @Success IS NULL)
AND
(
CASE @DateRangeType
WHEN 'Captured' THEN P.Captured >= @DateStart AND P.Captured <= @DateEnd
WHEN 'Capture' THEN P.Capture >= @DateStart AND P.Capture <= @DateEnd
WHEN 'Deleted' THEN P.Deleted >= @DateStart AND P.Deleted <= @DateEnd
WHEN 'Created' THEN I.Created >= @DateStart AND I.Created <= @DateEnd
WHEN 'Due' THEN I.Due >= @DateStart AND I.Due <= @DateEnd
WHEN 'Approved' THEN I.Approved >= @DateStart AND I.Approved <= @DateEnd
END
)
)

As you can see, my CASE checks to see what TYPE of date range they selected onthe WebForm. I then take that "DateRangeType" and plug in the appropriate column names, etc. I think the problem is that I am trying to put the column names and the comparison both within the WHEN portion of the CASE. All other CASE statements within WHERE clauses that I have written have the column name BEFORE the CASE statement and only the comparison within the WHEN. So, normally I do......

WHERE ColumnName >=
CASE @DateRangeType
WHEN 'Approved' THEN @DateStart
END
AND ColumnName <=
CASE @DateRangeType
WHEN 'Approved' THEN @DateEnd
END

Is this what I have to do? Do I have to write a CASE for the <= and one for the >= as well as a CASE for each column name I want to compare/use? If so, that will become very sloppy and cumbersome. There has to be a way to do it in less code... Sorta like I have it laid out and I am trying to do. Please help. w00t

- John

Post #731047

RBarryYoung

Posted Monday, June 08, 2009 6:02 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

You are trying to return boolean truth values in your case expression, but SQL has no such datatype, so you can't do that. As you suggested, the SOP here is to nest CASE expressions for each of you branches. Yes, it's a lot of code, but you can't be skittish about writing a lot of code if you want to do SQL right. All too often, the right way to do it in SQL is to write a prodigious amount of code.

Fortunately in your case, there is a slightly more compact way to do it:

AND 1 =
(
	CASE 
		WHEN @DateRangeType = 'Captured' 
			AND P.Captured >= @DateStart 
			AND P.Captured <= @DateEnd THEN 1
		WHEN @DateRangeType = 'Capture' 
			AND P.Capture >= @DateStart 
			AND P.Capture <= @DateEnd  THEN 1
		WHEN @DateRangeType = 'Deleted' 
			AND P.Deleted >= @DateStart 
			AND P.Deleted <= @DateEnd THEN 1
		WHEN @DateRangeType = 'Created' 
			AND I.Created >= @DateStart 
			AND I.Created <= @DateEnd THEN 1
		WHEN @DateRangeType = 'Due' 
			AND I.Due >= @DateStart 
			AND I.Due <= @DateEnd THEN 1
		WHEN @DateRangeType = 'Approved' 
			AND I.Approved >= @DateStart 
			AND I.Approved <= @DateEnd THEN 1
		ELSE 0
	END
)

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #731052

johnj-747215

Posted Tuesday, June 09, 2009 12:00 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, June 09, 2009 12:36 PM
Points: 2, Visits: 4

Awesome. Thank you. I knew there had to be a better way of doing this. I really appreciate you taking the time to help me out.

- John

Post #731757

RBarryYoung

Posted Tuesday, June 09, 2009 12:07 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

No problem, just glad I could help. Smile

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #731763

himanshu.luthra-769166

Posted Wednesday, July 25, 2012 12:14 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, September 06, 2012 3:07 AM
Points: 1, Visits: 10

Awsom...

Post #1334913

CELKO

Posted Wednesday, July 25, 2012 8:39 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

This is minimal polite behavior on SQL forums.

What you did post is full of fundamental errors. Even the title is wrong; SQL has a case expression, not a statement. The first problem is that you do not know about the Calendar tables for this. In particular, pay attention to the Julianized business day.

Also, we use the ANSI Stanard CURRENT_TIMESTAMP and not the old Sybase getdate())

>> This is what I currently have, but SQL Server 2008 doesn't like it. I have never been one to use CASE statements in WHERE clauses very much and so I have never written anything very complex. <<

No, complexity is not the real problem; you do not know the fundamentals.

1. We never use the * in production code.
2. DELETED is a reserved word in T-SQL, a verb and just plain silly in good SQL; it is a value for a status of some kind, not a stupid assembly language flag shoved into a table.
3. The use of 'AdminDeleted' is dialect; SQL uses double quotes and good SQL does not change the name of data elements.
4. Billing_Payments_Types is an attribute of a billing payment. There ought to be a REFERENCES to it in that table. NEVER treated as an entity in a join, as you have done.
5. The name “<something>_type_id” is another fundamental design error; An attribute can be a “<something>_type” or “<something>_id” but not this absurd hybrid. The same principle applies to “<something>_date_range_type” too. I had laugh at “@DateRangeType = 'Approved'” because the idea of someone saying that they approve the last week of July 2006, but want to delete last Thursday is absurd. Can Fridays be purple? Is 23.45 mm “approved” or not? Scales and measurements is covered in the first week or two of any data modeling course.
6. More than one left outer join is a sign of a really screwed up schema. The usual cause is attribute splitting. After three decades of reading bad code, I can make good guesses :)
7. You do not seem to know the difference between “_id” and “_nbr” in a data model.
8. Amount? Of what?
9. Why do you have audit data in this query?
10. Again, CASE is an expression. You want to use it as a procedural flow control statements. Noty a small error but a fundamental one.

>> As you can see, my CASE checks to see what TYPE of date range they selected on the WebForm. <<

No. A date range is a measurement on a temporal scale. A type is a discrete measurement on a nominal scale with a test – think “blood_type” if that helps.

>> I then take that "DateRangeType [sic]" and plug in the appropriate column names, etc. <<

NO! NO! NO! A table, whether is is a base table, VIEW, or query result, models a set. The set has known attributes and each row is a fact about one and only one element of the set. The table does not change from automobiles to squids to Lady Gaga as you move from row to row.

Someone can screw you over with kludges, but the truth is that you just do not understand RDBMS or SQL. This si more than a forum can do for you. Sorry.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Post #1335174

Sean Lange

Posted Wednesday, July 25, 2012 9:26 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 1:17 PM
Points: 8,641, Visits: 8,273

This post is over 3 years old and the OP has not logged into SSC since they created the original post. Move along...nothing to see here.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1335215

Lokesh Vij

Posted Wednesday, July 25, 2012 9:36 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 8:24 AM
Points: 1,176, Visits: 1,253

Nice catch Sean BigGrin

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1335229

ptaffet

Posted Tuesday, January 29, 2013 11:00 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 01, 2013 12:12 PM
Points: 1, Visits: 9

The thread is never to old to comment on this.

Celko, this is the most condescending, insulting and downright rude comment that I've seen on a tech forum. You may be a self-proclaimed expert in T-Sql, you may be a true expert, but you don't know squat about common courtesy.

Based on what I see here, I will avoid your Celko series like it carries the plague.

Phil Taffet

Post #1413201

« Prev Topic | Next Topic »

Permissions