CASE STATEMENT in WHERE CLAUSE
-
[font="Arial"]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!
SELECTP.*,
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
ENDIs this what I [b][i]have to do[/i][/b]? 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
[/font]
-
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 = @DateStart
AND P.Capture = @DateStart
AND P.Deleted = @DateStart
AND I.Created = @DateStart
AND I.Due = @DateStart
AND I.Approved <= @DateEnd THEN 1
ELSE 0
END
)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
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
-
No problem, just glad I could help. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Awsom...
-
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Nice catch Sean 😀
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
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
- RBarryYoung - Monday, June 8, 2009 6:02 PM
Hi, i have same query but its working, please help on below query, i want to check dropdown value and perform a query accordingly.. Like if dropdown value is TITLE the perform a like query and search the titles, if dropdown value is External id then do an in cluse and search external id using case when query -
SyntaxEditor Code Snippet SELECT {Title}.[Title],{ReleaseCategory}.[Label],{Company}.[Name],{Title}.[External_ID],{Title}.[Title_Date],{Title}.[Assigned_To],{TitleDeliveryStatusMaster}.[Label],0,{Title}.[Id]FROM {Title} Left JOIN {Company} ON {Title}.[Studio] = {Company}.[Id] Left JOIN {TitleDeliveryStatusMaster} ON {Title}.[Delivery_Status_Title] = {TitleDeliveryStatusMaster}.[Id] Left JOIN {ReleaseCategory} ON {Title}.[Release_Category] = {ReleaseCategory}.[Id] Left JOIN {PCT} ON {Title}.[Id] = {PCT}.[TitleId]WHERECASE WHEN @DropDownValue = 'Title' Then {Title}.[Title] Like ('%'+@SearchTextVar +'%') WHEN @DropDownValue = 'ExternalID' Then {Title}.[External_ID] Like ('%'+@SearchTextVar +'%') End ORDER BY {Title}.[Assigned_To] ASCI am using this in OUTSYSTEM plateform, way of writing the query is different Please help me with the same. Thankyou
- vpandey29 - Friday, July 21, 2017 5:42 AM
This thread is more than 8 years old. If you have a question you should start a new thread not jump onto a really old one. Before you create your own question you should take a few minutes and read the first link in my signature for best practices when posting questions.
CASE is an expression which means it can return a single scalar value. It cannot be used like you are trying to do here. Pretty sure you want something along these lines.
WHERE (@DropDownValue = 'Title' AND {Title}.[Title] Like '%' + @SearchTextVar +'%')
OR (@DropDownValue = 'ExternalID' AND {Title}.[External_ID] Like '%' + @SearchTextVar +'%')The syntax here is not valid t-sql but this should demonstrate how this would work.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ - vpandey29 - Friday, July 21, 2017 5:42 AM
Simlarly, try this:
SELECT {Title}.[Title],
{ReleaseCategory}.[Label],
{Company}.[Name],
{Title}.[External_ID],
{Title}.[Title_Date],
{Title}.[Assigned_To],
{TitleDeliveryStatusMaster}.[Label],
0,
{Title}.[Id]
FROM {Title}
Left JOIN {Company}
ON {Title}.[Studio] = {Company}.[Id]
Left JOIN {TitleDeliveryStatusMaster}
ON {Title}.[Delivery_Status_Title] = {TitleDeliveryStatusMaster}.[Id]
Left JOIN {ReleaseCategory}
ON {Title}.[Release_Category] = {ReleaseCategory}.[Id]
Left JOIN {PCT}
ON {Title}.[Id] = {PCT}.[TitleId]
WHERE (@DropDownValue = 'Title' AND {Title}.[Title] Like ('%'+@SearchTextVar +'%'))
OR (@DropDownValue = 'ExternalID' AND {Title}.[External_ID] Like ('%'+@SearchTextVar +'%'))
ORDER BY {Title}.[Assigned_To] ASC;Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - vpandey29 - Friday, July 21, 2017 5:42 AM
Something like this should work for you:
SELECT
{Title}.[Title],
{ReleaseCategory}.[Label],
{Company}.[Name],
{Title}.[External_ID],
{Title}.[Title_Date],
{Title}.[Assigned_To],
{TitleDeliveryStatusMaster}.[Label],
0,
{Title}.[Id]
FROM {Title}
Left JOIN {Company} ON {Title}.[Studio] = {Company}.[Id]
Left JOIN {TitleDeliveryStatusMaster} ON {Title}.[Delivery_Status_Title] = {TitleDeliveryStatusMaster}.[Id]
Left JOIN {ReleaseCategory} ON {Title}.[Release_Category] = {ReleaseCategory}.[Id]
Left JOIN {PCT} ON {Title}.[Id] = {PCT}.[TitleId]
WHERE
(@DropDownValue = 'Title' AND {Title}.[Title] LIKE ('%'+@SearchTextVar +'%') )
OR
(@DropDownValue = 'ExternalID' AND {Title}.[External_ID] LIKE ('%'+@SearchTextVar +'%') )
ORDER BY {Title}.[Assigned_To] ASC“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 - Sean Lange - Friday, July 21, 2017 7:12 AM
Thankyou soo much.. It worked, there are certain conversion that i need to do.. God bless u .. thankyou
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply