Help with AND and OR - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with AND and OR

Author

Topic

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-08-30 : 12:44:51

I am sure the answer has to be very simple ... !!

However

I would expect this

CASE WHEN CAST(Field28 As Integer)>0 AND (Field25 <> '99'OR Field25 <> '98' OR Field25 <> '97')
THEN 'ERROR '+Field28+' '+Field25+' 216-A2' ELSE NULL END AS Test216

To return NULL when Field28 = '100' and Field25 = '99'

BUT it returns the error text 'ERROR 100 99 216-A2'

I guess that there must also be a more elegant way of testing for the presence (or not) of multiple values?

Help - I am clearly missing something about the way OR works

PS If I remove the multiple checks and just test Field25<>'99' it returns NULL as expected

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-30 : 13:51:44

Does this help?

DECLARE @Field25 varchar(5)
DECLARE @Field28 varchar(5)

SET @Field25 = 99
SET @Field28 = 100

SELECT CASE WHEN CAST(@Field28 As Integer)>0 AND (@Field25 NOT IN ('99','98','97'))
THEN 'ERROR '+@Field28+' '+@Field25+' 216-A2'
ELSE NULL
END AS Test216

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-08-30 : 14:57:38

Exactly the insight I needed - thank you - just plain simply never used NOT IN before - thank you

I much prefer the NOT IN to the repeating ORs - but please can someone explain why the OR syntax does not work?
CASE WHEN CAST(Field28 As Integer)>0 AND (Field25 <> '99'OR Field25 <> '98' OR Field25 <> '97')
THEN 'ERROR '+Field28+' '+Field25+' 216-A2' ELSE NULL END AS Test216

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-30 : 16:59:38

It cannot work because NOT OR NOT is always true.

Field25 <> '99'OR Field25 <> '98' OR Field25 <> '97'

For example Field25 has the value '98' then <> '99' is true.
If Field25 has the value '99' then <> '98' is true and so on...

Your OR should have been an AND.

No, you're never too old to Yak'n'Roll if you're too young to die.

RSQLSERVER
Starting Member

5 Posts

Posted - 2009-08-30 : 19:43:33

I guess the mistake is Field28 should be 100 and not '100'

? ?????? ??? ?????????? ??? ???? ???????? ????

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources