May 23, 2011 at 6:41 pm
I need to exclude 5 types of accounts for a report. My query is as follows:
SELECT INDACCOUNTNUMBER, TOTALPYMTAMT, NETPAID, PAIDDATE, ACCOUNT_NAME
FROM dbo.Audit2010$
WHERE (ACCOUNT_NAME <> 'Community Impact Fund') AND (ACCOUNT_NAME <> 'Health') AND (ACCOUNT_NAME <> 'Income') AND (ACCOUNT_NAME <> 'Education') AND (ACCOUNT_NAME <> 'Community Relief Fund')
For some reason, this query is also excluding results where the ACCOUNT_NAME field is null. I have tried including "AND (ACCOUNT_NAME IS NULL OR ACCOUNT_NAME='')" but that returned no results.
Any help would be appreciated!
May 23, 2011 at 7:52 pm
Try wrapping your column in ISNULL() or COALESCE()
example:
SELECT INDACCOUNTNUMBER, TOTALPYMTAMT, NETPAID, PAIDDATE, ACCOUNT_NAME
FROM dbo.Audit2010$
WHERE ISNULL(ACCOUNT_NAME,'') IN ('Community Impact Fund','Health', 'Income', 'Education','Community Relief Fund')
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
May 23, 2011 at 8:27 pm
It's all about the parentheses...try it this way:
SELECT INDACCOUNTNUMBER ,
TOTALPYMTAMT ,
NETPAID ,
PAIDDATE ,
ACCOUNT_NAME
FROM dbo.Audit2010$
WHERE ACCOUNT_NAME IS NULL
OR (
( ACCOUNT_NAME <> 'Community Impact Fund' )
AND ( ACCOUNT_NAME <> 'Health' )
AND ( ACCOUNT_NAME <> 'Income' )
AND ( ACCOUNT_NAME <> 'Education' )
AND ( ACCOUNT_NAME <> 'Community Relief Fund' )
)
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 23, 2011 at 8:29 pm
...same results but a little more concise:
SELECT INDACCOUNTNUMBER ,
TOTALPYMTAMT ,
NETPAID ,
PAIDDATE ,
ACCOUNT_NAME
FROM dbo.Audit2010$
WHERE ACCOUNT_NAME IS NULL
OR ACCOUNT_NAME NOT IN ( 'Community Impact Fund', 'Health', 'Income', 'Education', 'Community Relief Fund' )
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 24, 2011 at 7:00 am
opc.three (5/23/2011)
...same results but a little more concise:
SELECT INDACCOUNTNUMBER ,TOTALPYMTAMT ,
NETPAID ,
PAIDDATE ,
ACCOUNT_NAME
FROM dbo.Audit2010$
WHERE ACCOUNT_NAME IS NULL
OR ACCOUNT_NAME NOT IN ( 'Community Impact Fund', 'Health', 'Income', 'Education', 'Community Relief Fund' )
D'oh! NOT in, right... thanks opc.three
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
May 24, 2011 at 7:10 am
Sure thing jcrawf02. As a side note, I would not have posted at all because the poster would have caught the IN/NOT IN logic nugget, however when there is a logically equivalent option you should avoid wrapping columns with functions (built-in or user-defined) as it can negate the use of indexes and result in the optimizer choosing to do expensive table or index scans. Sometimes wrapping a column with a function may be the only choice to get the result you need, however in this case there were better options.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 24, 2011 at 7:22 am
opc.three (5/24/2011)
Sure thing jcrawf02. As a side note, I would not have posted at all because the poster would have caught the IN/NOT IN logic nugget, however when there is a logically equivalent option you should avoid wrapping columns with functions (built-in or user-defined) as it can negate the use of indexes and result in the optimizer choosing to do expensive table or index scans. Sometimes wrapping a column with a function may be the only choice to get the result you need, however in this case there were better options.
Good point, thanks.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
May 24, 2011 at 9:30 am
jcrawf02 (5/24/2011)
opc.three (5/24/2011)
Sure thing jcrawf02. As a side note, I would not have posted at all because the poster would have caught the IN/NOT IN logic nugget, however when there is a logically equivalent option you should avoid wrapping columns with functions (built-in or user-defined) as it can negate the use of indexes and result in the optimizer choosing to do expensive table or index scans. Sometimes wrapping a column with a function may be the only choice to get the result you need, however in this case there were better options.Good point, thanks.
I just recognized you from the boards jcrawf02...I am confident you already knew this point...sorry if it came across as a lecture.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 24, 2011 at 9:39 am
opc.three (5/24/2011)
I just recognized you from the boards jcrawf02
Which means nothing, I'm definitely not an expert, just trying to learn 😉
opc.three (5/24/2011)
...I am confident you already knew this point
Ah, but you see I had momentarily forgotten that, so worth the post, and instructive to others who might not know, including possibly the OP
opc.three (5/24/2011)
...sorry if it came across as a lecture.
Which it did not. Thanks! 🙂
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
May 24, 2011 at 9:42 am
THank you all for your help!
May 24, 2011 at 9:58 am
Your parentheses are getting you again. I assume you want
SELECT INDACCOUNTNUMBER, TOTALPYMTAMT, NETPAID, PAIDDATE, ACCOUNT_NAME
FROM dbo.Audit2010$
WHERE
(--treat both ACCOUNT_NAME conditions as one by enclosing in parentheses
ACCOUNT_NAME IS NULL
OR (ACCOUNT_NAME Not In ('Community Impact Fund','Health','Income','Education','Community Relief Fund'))
)
AND TOTALPYMTAMT=0
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply