T-SQL query selecting on two parameters
Hello expert,
Working on selecting pairs of Payers and Component
and excluding them from lists.
SELECT
COM.CCMEmployerID AS EMPLOYER_ID,
EMP.EMPLOYER_NAME,
PAY.PAYER_ID,
PAY.PAYER_NAME,
COM.CCMComponentID AS COMPONENT_ID
where
and Pay.Payer_ID <> 100083 --exclude DISA
and Component_ID <> 7 -- exclude Audiogram
But this query excludes all DISA not just where DISA is a payer for the Audiogram.
For instance this excludes DISA where DISA is the Payer for a DOT Physical.
So I tried
and (Pay.Payer_ID <> 100083 and and Component_ID <> 7 )
hoping this would exclude the combination of DISA and Audiogram
but it has the say effect as the first where statement.
What is the syntax for excluding a record that does not equal a
combination of two fields equal to certain values?
Thanks.
Allen in Dallas
Working on selecting pairs of Payers and Component
and excluding them from lists.
SELECT
COM.CCMEmployerID AS EMPLOYER_ID,
EMP.EMPLOYER_NAME,
PAY.PAYER_ID,
PAY.PAYER_NAME,
COM.CCMComponentID AS COMPONENT_ID
where
and Pay.Payer_ID <> 100083 --exclude DISA
and Component_ID <> 7 -- exclude Audiogram
But this query excludes all DISA not just where DISA is a payer for the Audiogram.
For instance this excludes DISA where DISA is the Payer for a DOT Physical.
So I tried
and (Pay.Payer_ID <> 100083 and and Component_ID <> 7 )
hoping this would exclude the combination of DISA and Audiogram
but it has the say effect as the first where statement.
What is the syntax for excluding a record that does not equal a
combination of two fields equal to certain values?
Thanks.
Allen in Dallas
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
try
where not (Pay.Payer_ID = 100083 and Component_ID = 7)
@HainKurt - you provided the same suggestion as Jim :) Same thinking.
oops, sorry, I missed it :)
I work full time, and from time to time, I check questions and scan answers
and if I dont see what I think the solution is, I post my solution...
if it is there, I pass :)
I work full time, and from time to time, I check questions and scan answers
and if I dont see what I think the solution is, I post my solution...
if it is there, I pass :)
@Pawan - If it's within minutes it's very forgivable, and nobody's going to get all jumpy-excited about it.
ASKER
Actually the OR statement in parenthesis returns data required. Thanks for your help Mr. Horn. I was trying to isolate the issue by simplifying the code. I see now that the simplification affects the issue description.
also, if you go with "or" solution and you have another filter like
>>> add proper (...)
where not (Pay.Payer_ID = 100083 and Component_ID = 7) and insertDate>getDate()-7>>> add proper (...)
where (Pay.Payer_ID <> 100083 or Component_ID <> 7) and insertDate>getDate()-7
> where Component_ID <> 7 -- exclude Audiogram
For starters, one problem with this code is that you're using the alias COMPONENT_ID in your SELECT clause, and referring to in in your WHERE clause, and if you look at SQL Server Query Order of Execttion WHERE is processed before SELECT, which means WHERE does not know what a column alias is that is defined later in the SELECT.
So I'm guessing COMPONENT_ID exists in another table, hence no error message, and perhaps that is why your query results are not returning what you are expecting.
Try this and see what happens..
Open in new window
Also try this..
Open in new window