change condition i WHERE clause

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

SQL Server Development (2000)

change condition i WHERE clause

Author

Topic

Jenny
Starting Member

2 Posts

Posted - 2002-08-07 : 02:52:12

I have a query that should return a code depending on a postalcode is between 2 values. If the first postalcode is Null then use the other postalcode one instead. The 2 postalcodes are in the same table.

Select Code from Supro, Person
where "if person.Postal1 isNull then use Person.postal2"
between Supro.P1 and Supro.P2
and person.personid = 6789

Any idea on how to achieve this? Is it possible to do it in one statement?

Thanks
//Jenny

CMartin
Starting Member

13 Posts

Posted - 2002-08-07 : 03:25:07

Jenny,

Try this.



SELECT Code FROM Supro, Person 
WHERE
	CASE
	  WHEN person.Postal1 IS NULL THEN Person.postal2
	  ELSE person.Postal1
	END
BETWEEN Supro.P1 AND Supro.P2 
AND person.personid = 6789

Carlos

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-07 : 04:39:08



SELECT Code FROM Supro, Person 
WHERE COALESCE(Person.Postal1,Person.postal2) BETWEEN Supro.P1 AND Supro.P2 
AND Person.personid = 6789

HTH
Jasper Smith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources