IF Statement in a Select Query

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 2000 Forums

Transact-SQL (2000)

IF Statement in a Select Query

Author

Topic

jmiskey
Starting Member

15 Posts

Posted - 2007-06-14 : 13:09:18

I am writing a Select Query in T-SQL 2000. I have two fields that I need to compare. One has values of -1 and 0, while the other has values of "Y" and "N" (-1="Y" and 0="N"). So I am trying to convert one field so that I can compare it to the other.

How would I go about doing this? There doesn't appear to be an immediate if (IIF) statement in T-SQL. Can I do this with some sort of IF statement right in my Select Query code, or is there some sort of conversion function I can use?

Thanks.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-06-14 : 13:20:39

Use the CASE() statement to implement this conditional logic.

e4 d5 xd5 Nf6

jmiskey
Starting Member

15 Posts

Posted - 2007-06-14 : 13:22:27

Thanks blindman. I was searching around the web and just found that myself. For future reference, I post my solution here:

(CASE M.CLAIMS_COORD
   WHEN 0
      THEN ‘N’
   ELSE ‘Y’
END) AS CLAIM_COOR,

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-06-15 : 10:17:57

Yup, though be aware that if CLAIMS_COORD is NULL (even bit datatypes can be nulls) then your logic will evaluate to 'Y'. I'm guessing that is not what you want, so consider this instead:
(CASE M.CLAIMS_COORD
WHEN 1
THEN ‘Y’
ELSE ‘N’
END) AS CLAIM_COOR,

e4 d5 xd5 Nf6

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-15 : 10:39:30

or even:


CASE M.CLAIMS_COORD
   WHEN 1 THEN 'Y'
   WHEN 0 THEN 'N'
END AS CLAIM_COOR

this will return NULL if CLAIMS_COORD is NULL which seems appropriate, since NULL means unknown, not false.

elsasoft.org

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-06-15 : 13:04:06

I concur and extend:

CASE M.CLAIMS_COORD
   WHEN 1 THEN 'Y'
   WHEN 0 THEN 'N'
   ELSE NULL
END AS CLAIM_COOR

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources