May 1, 2012 at 9:26 am
Hi,
I've created a query that, because of the last 3 items in the select statement (canceled date, new order status and disbactiondate), returns every file in the system. When all 3 of these items are null for a given file, i would like to filter out these results. I am attempting to do this in the Where clause, however, do not know how to refer to a case statement. Take a look at the last line of the Where Clause to see exactly what i'm referring to. Any advice? Here is the query:
SELECTFM.FileNumber,
FM.OpenedDate,
PC.Name AS Client,
P.State AS State,
ALTT.Name +': '+ALPT.DisplayName AS TransactionType,
FM.PrimaryPropertyAddress AS PropertyAddress,
FM.PrimaryBuyerSellerName AS Transactee,
CASE WHEN PC3.Name IS NULL THEN PC.Name ELSE PC3.Name END AS ClientsClient,
CASE WHEN PC1.Name IS NULL THEN 'No Sales Manager' ELSE PC1.Name END AS SalesManager,
CASE WHEN PC2.Name IS NULL THEN 'No Sales Rep' ELSE PC2.Name END AS SalesRep,
'.Today' AS Type,
CASE WHEN FM.OpenedDate >= @enddate THEN 'New Order' END AS 'New Order Status',
(SELECT MIN(dbo.GetLocalDateTimeFunc(FA.ReceivedDate)) FROM FileActions FA
WHERE FA.ActionDefID IN (61,315, 393, 400)
AND FA.ReceivedDate IS NOT NULL
AND FA.ReceivedDate >= @enddate
AND FA.FileID = FM.FileID) AS DisbActionDate,
CASE WHEN FM.ClosedDate >= @enddate and S.StatusID =8 THEN 'Canceled' END AS 'Canceled Date'
FROMFileMain FM
JOINStatus S ON S.StatusID = FM.StatusID
JOINPartnerCompany PC ON PC.PartnerCompanyID = FM.ClientID
JOINActionListTransactionType ALTT ON ALTT.ActionListTransactionTypeID = FM.ActionListTransactionTypeID
JOINActionListProductType ALPT ON ALPT.ActionListProductTypeID = FM.ActionListProductTypeID
JOINProperty P ON P.FileID = FM.FileID
LEFT JOIN FilePartnerRel FPR ON FPR.FileID = FM.FileID
AND FPR.PartnerTypeID = 5 -- ClientsClient
LEFT JOIN PartnerCompany PC3 ON PC3.PartnerCompanyID = FPR.PartnerCompanyID
LEFT JOIN FilePartnerRel FPR1 ON FPR1.FileID = FM.FileID
AND FPR1.PartnerTypeID = 10032 -- Sales Manager
LEFT JOIN PartnerCompany PC1 ON PC1.PartnerCompanyID = FPR1.PartnerCompanyID
LEFT JOIN FilePartnerRel FPR2 ON FPR2.FileID = FM.FileID
AND FPR2.PartnerTypeID = 9994 -- Sales Rep
LEFT JOIN PartnerCompany PC2 ON PC2.PartnerCompanyID = FPR2.PartnerCompanyID
WHEREFM.ClientID !=3
AND FM.FileNumber NOT LIKE 'PA-%'
AND FM.OfficeID = 1
AND ('new order status' IS NOT NULL OR 'DisbActionDate' IS NOT NULL OR 'Canceled Date' IS NOT NULL)
May 1, 2012 at 9:33 am
Can you post DDL and Sample date (See the link in my signature for help on posting that if you need it.) it looks like your where clause is correct so that only if all 3 are null you will not see them.
If you want it where if any of the 3 are null do not show the record then change your OR's to AND's
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 1, 2012 at 9:42 am
capn.hector (5/1/2012)
Can you post DDL and Sample date (See the link in my signature for help on posting that if you need it.) it looks like your where clause is correct so that only if all 3 are null you will not see them.If you want it where if any of the 3 are null do not show the record then change your OR's to AND's
I was going to post some sample data, but its too wide for the forum. It doesn't fit and gets all jumbled. You are correct that I do want the Or's and not And's. Having the last line in the where clause makes absolutely no change in the results. I must be referencing it incorrectly some how...
May 1, 2012 at 10:24 am
You can't reference your derived column like that. You have to use the whole case statement again.
Here is a really basic example.
select case when 1 = 2 then 'NO' else 'YES' end
where case when 1 = 2 then 'NO' else 'YES' end = 'YES'
In your situation your case statements are more complicated but you will have to do the same thing.
Just so you know your code is full or performance problems. You have a scalar function in the select list. Scalar functions are horrible for performance. Given that your subquery is going to return the same value for every single row it would be better to use a variable and store that first.
declare @ReceivedDate datetime
(SELECT MIN(dbo.GetLocalDateTimeFunc(FA.ReceivedDate)) FROM FileActions FA
WHERE FA.ActionDefID IN (61,315, 393, 400)
Then you just refer to your variable in your select.
I was going to post some sample data, but its too wide for the forum. It doesn't fit and gets all jumbled.
To get around this use the IFCode shortcuts when posting. They are on the left of the screen when posting. CODE is probably the most common one, it will even color highlight based on sql color schemes. 🙂
_______________________________________________________________
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/
May 1, 2012 at 10:35 am
Sean Lange (5/1/2012)
You can't reference your derived column like that. You have to use the whole case statement again.Here is a really basic example.
select case when 1 = 2 then 'NO' else 'YES' end
where case when 1 = 2 then 'NO' else 'YES' end = 'YES'
In your situation your case statements are more complicated but you will have to do the same thing.
Just so you know your code is full or performance problems. You have a scalar function in the select list. Scalar functions are horrible for performance. Given that your subquery is going to return the same value for every single row it would be better to use a variable and store that first.
declare @ReceivedDate datetime
(SELECT MIN(dbo.GetLocalDateTimeFunc(FA.ReceivedDate)) FROM FileActions FA
WHERE FA.ActionDefID IN (61,315, 393, 400)
Then you just refer to your variable in your select.
I was going to post some sample data, but its too wide for the forum. It doesn't fit and gets all jumbled.
To get around this use the IFCode shortcuts when posting. They are on the left of the screen when posting. CODE is probably the most common one, it will even color highlight based on sql color schemes. 🙂
Thanks. you've been a huge help. Right before you replied, I actually went back through and and put the entire case statements in the where clause. It works now. As to the 2nd part of your response... I actually have this entire query multiple times with unions in between to do different time periods. Today, MTD, PM, PPM, YTD. Is there still a way to declare it, even though each one needs to refer to a different time period? Thanks.
May 1, 2012 at 10:39 am
Jeremy... (5/1/2012)
Thanks. you've been a huge help. Right before you replied, I actually went back through and and put the entire case statements in the where clause. It works now. As to the 2nd part of your response... I actually have this entire query multiple times with unions in between to do different time periods. Today, MTD, PM, PPM, YTD. Is there still a way to declare it, even though each one needs to refer to a different time period? Thanks.
There are a couple things you could do. You could use a different variable for each unique value. Not too bad if the list is fairly small. The other thing you could do is convert your function to an iTVF (inline table value function). This will greatly help with performance. You could even have it return all the values in one fell swoop if you know what the different values needed would be.
_______________________________________________________________
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/
May 1, 2012 at 12:17 pm
Sean Lange (5/1/2012)
Jeremy... (5/1/2012)
Thanks. you've been a huge help. Right before you replied, I actually went back through and and put the entire case statements in the where clause. It works now. As to the 2nd part of your response... I actually have this entire query multiple times with unions in between to do different time periods. Today, MTD, PM, PPM, YTD. Is there still a way to declare it, even though each one needs to refer to a different time period? Thanks.
There are a couple things you could do. You could use a different variable for each unique value. Not too bad if the list is fairly small. The other thing you could do is convert your function to an iTVF (inline table value function). This will greatly help with performance. You could even have it return all the values in one fell swoop if you know what the different values needed would be.
I've never done an iTVF before, but it looks like it could be very useful. I'll read up on it and try it out. Thanks again for your help.
May 1, 2012 at 12:37 pm
You are quite welcome. Take a look at BOL here.
You can also take a look at this thread about ways to avoid calling the same function repeatedly.
Finally you should look at APPLY.
Paul White has a great article set on APPLY.
Understanding and Using APPLY (Part 1)[/url]
Understanding and Using APPLY (Part 2)[/url]
I know that is a lot to read but understanding how you can convert your function to a table valued function and then reference it with APPLY will be a huge step forward.
Feel free to post back with questions or if you run into anything you can't get figured out.
_______________________________________________________________
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/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply