Wednesday, March 28, 2012

outerjoint

Hi all - I am new to MsSQL and need a bit of help.
I have a query that works perfectly in Oracle but not in MsSQL. I understand that there are different commands in MsSQL and for the life of me I cannot find them. Can someone help? Here is the query.

Select c.*, ins.id from CASES c, INSPECTOR ins
where c.id = &intID
and ins.id (+) = decode(c.inspector_id,1,null,c.inspector_id)

Cheers
HekiSelect c.*, ins.id
from CASES c
LEFT OUTER JOIN
INSPECTOR ins ON
ins.id = decode(c.inspector_id,1,null,c.inspector_id) AND
c.id = &intID;|||sql server doesn't support DECODE

select c.*
, ins.id
from CASES c
left outer
join INSPECTOR ins
on case when c.inspector_id = 1
then null
else c.inspector_id end = ins.id
and c.id = &intID

No comments:

Post a Comment