Replace empty return value with something

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)

Replace empty return value with something

Author

Topic

bshumake
Starting Member

3 Posts

Posted - 2009-05-12 : 10:22:40

I have a query that returns multiple ids of sensors if the sensor names match with the ones I specify after the 'IN' statement. This works fine, however, if a match is not found, then nothing is returned (By nothing, I don't mean 'NULL', I mean nothing). Instead of nothing, I would like it to return '-1' if no match is found.

Here is my query:

Select distinct sensor.id from sensor, sensorname where
sensorname.sensorId=sensor.id and
sensor.plantId=51 and
sensorname.sensor IN ('FE0122','FE0124')

This will return 1167 and 1169 if these sensor names exists, however, if I send it something that doesn't exist like

sensorname.sensor IN ('FE0122',FE012x')

Then all I get back is 1167. I would rather get back 1167, -1 in this case.

Can anyone help?

Brent

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-12 : 10:31:38

Try this

select coalesce(id, -1) AS ID
from (
Select sensor.id , count(*) AS dummy
from sensor
inner join  sensorname on sensorname.sensorId = sensor.id 
where sensor.plantId=51 and sensorname.sensor IN ('FE0122', 'FE0124')
group by sensor.id 
) AS d

E 12°55'05.63"
N 56°04'39.26"

bshumake
Starting Member

3 Posts

Posted - 2009-05-12 : 10:38:33

Thanks, Peso. However, when I tried replacing 'FE0124' with 'FE012x' ('FE012x' does not exist), it still only returns 1167, not 1167,-1 as I would like.

Brent

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-12 : 10:38:39

Select distinct coalesce(x.id, -1) AS ID
from (select 'FE0122' AS sensorid union all select 'FE0124') AS d
LEFT JOIN (select sensor.id, sensorname from sensor
inner join sensorname on sensorname.sensorId = sensor.id
where sensor.plantId=51) AS x on x.sensorname = on d.sensorid

E 12°55'05.63"
N 56°04'39.26"

bshumake
Starting Member

3 Posts

Posted - 2009-05-12 : 10:47:32

Thanks Peso. That didn't work either.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-12 : 13:01:58

AS x on x.sensorname = d.sensorid

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources