Where Clause in T-SQL

Question

Where Clause in T-SQL

pdsqsql

Old Hand

Points: 358
More actions
July 31, 2019 at 10:33 am

#3667480

Hello,
I am trying to use following conditions in my where clause but trying make sure that i ma using correct way.
Requirement: need to change, see below:
a. Program <> 35 and acct1 <> 1
b. Program = 30, 31 and acct1 =6
My Where clause is correct as below?
WHERE TYPE <> 35 AND ACCT1 <> 2
AND TYPE IN (30, 31) AND ACCT1 = 6
Is it i am using correct way or i need to modify it to?

Thanks for your help!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

That looks OK to me. Does it work?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Phil Parkin SSC Guru Points: 246986 More actions · Answer 2

That requirement looks suspect to me.

Do (a) and (b) both have to be true (AND), or is it a case of one or the other (OR)?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

pietlinden SSC Guru Points: 63509 More actions · Answer 3

Looks like it should be:

WHERE (TYPE <> 35 AND ACCT1 <> 2)
OR (TYPE IN (30, 31) AND ACCT1 = 6)

below86 SSChampion Points: 11538 More actions · Answer 4

The way you have it written you don't need the "TYPE <> 35 AND ACCT1 <> 2". The check where the Type IN (30, 31) will insure you don't get type 35 the same can be said for the check for Acct1 if it has to be 6 then the check for <> 2 isn't needed.

This should be all you need:

WHERE Type IN (30,31) AND Acct1 = 6

Unless like others have suggested that this should be one OR the other, then I would go with the code that pietlinden posted.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

I agree with Phil and pietlinden, the only way those criteria make sense is if you evaluate these separately with an OR between them.

pdsqsql Old Hand Points: 358 More actions · Answer 6

Thanks.

I am greed with you guys as checking with user exact condition. Both condition is overlapping so it's should be only one.

pdsqsql Old Hand Points: 358 More actions · Answer 7

pdsqsql

Old Hand

Points: 358

August 3, 2019 at 2:04 pm

#3668485

Thanks everyone for your response.