Robb Hill
asked on
SQL CASE STATEMENT
How could I write this....
Essentially if you look at the joins in this query..I have created two left joins that return a set of ContactAddress and a set of ClientAddress.
I need the select to return the ContactAddress first...but if the nid value of Contact is null return the ClientAddress.
I know I could do this column by column....but was wondering if their was a better way.
select
n1.nid,n1.nidClient, n1.Designate,n1.[Role],n1. [First Name],n1.[Last Name],n1.Salutation,n1.Tit le,
n1.Email,n1.[Email 2],n1.[Email 3],n1.[Work Phone],n1.[Home Phone],n1.[Cell Phone],
case when ContactAddressNid is null then
ClientAddress1 as [Address 1], ClientAddress2 as [Address 2], ClientAddress3 as [Address 3],
ClientAddressCity as [City], ClientAddressState as [State], ClientAddressZip as [Zip],ClientAddressCountry as [Country],
n1.[Native/Linked],n1.Cont actStatus
else
from
--native
(Select ContactAddress.nId as ContactAddressNid, ContactAddress.cAddress1 as ContactAddress1, ContactAddress.cAddress2 as ContactAddress2,ContactAdd ress.cAddr ess3 as ContactAddress3,ContactAdd ress.cCity as ContactAddressCity,Contact Address.cS tate as ContactAddressState,Contac tAddress.c PostalCde as ContactAddressZip,ContactA ddress.cCo untry as ContactAddressCountry,
ClientAddress.nId as ClientAddressNid, ClientAddress.cAddress1 as ClientAddress1, ClientAddress.cAddress2 as ClientAddress2,ClientAddre ss.cAddres s3 as ClientAddress3,ClientAddre ss.cCity as ClientAddressCity,ClientAd dress.cSta te as ClientAddressState,ClientA ddress.cPo stalCde as ClientAddressZip,ClientAdd ress.cCoun try as ClientAddressCountry,
c.nid,c.nidClient, c.cdesignate as Designate,'' as [Role],
c.cFirstName as [First Name], c.cLastName as [Last Name], c.cSalutation as [Salutation],c.cTitle as [Title],
c.cEmail as [Email],c.cEmailAlt as [Email 2],c.cEmailAlt2 as [Email 3],
c.cPhone as [Work Phone], c.cHomePhone as [Home Phone], c.cCellPhone as [Cell Phone],
'Native' as 'Native/Linked',
(CASE WHEN c.lActive = 0 THEN 'Inactive' ELSE 'Active' END) AS ContactStatus
FROM cadoc_crm.dbo.tContact c
inner join cadoc_system..tsitexcrmcli ent xc
on c.nidclient = xc.nidclient
inner join cadoc_system..tsite s
on xc.nidsite = s.nid
left join cadoc_crm..ContactXPrimary Address cp
on cp.nIdContact = c.nid
left join cadoc_crm..[Address] ContactAddress
on ContactAddress.nId = cp.nIdAddress
left join cadoc_crm..ClientXAddress ca
on c.nidClient = ca.nIdClient
left join cadoc_crm..[Address] ClientAddress
on ClientAddress.nId = ca.nIdAddress
WHERE
c.cLastName not in('POOL','USER','ADMINIST RATOR') and s.cPSiteCode = 'Root')n1
Essentially if you look at the joins in this query..I have created two left joins that return a set of ContactAddress and a set of ClientAddress.
I need the select to return the ContactAddress first...but if the nid value of Contact is null return the ClientAddress.
I know I could do this column by column....but was wondering if their was a better way.
select
n1.nid,n1.nidClient, n1.Designate,n1.[Role],n1.
n1.Email,n1.[Email 2],n1.[Email 3],n1.[Work Phone],n1.[Home Phone],n1.[Cell Phone],
case when ContactAddressNid is null then
ClientAddress1 as [Address 1], ClientAddress2 as [Address 2], ClientAddress3 as [Address 3],
ClientAddressCity as [City], ClientAddressState as [State], ClientAddressZip as [Zip],ClientAddressCountry
n1.[Native/Linked],n1.Cont
else
from
--native
(Select ContactAddress.nId as ContactAddressNid, ContactAddress.cAddress1 as ContactAddress1, ContactAddress.cAddress2 as ContactAddress2,ContactAdd
ClientAddress.nId as ClientAddressNid, ClientAddress.cAddress1 as ClientAddress1, ClientAddress.cAddress2 as ClientAddress2,ClientAddre
c.nid,c.nidClient, c.cdesignate as Designate,'' as [Role],
c.cFirstName as [First Name], c.cLastName as [Last Name], c.cSalutation as [Salutation],c.cTitle as [Title],
c.cEmail as [Email],c.cEmailAlt as [Email 2],c.cEmailAlt2 as [Email 3],
c.cPhone as [Work Phone], c.cHomePhone as [Home Phone], c.cCellPhone as [Cell Phone],
'Native' as 'Native/Linked',
(CASE WHEN c.lActive = 0 THEN 'Inactive' ELSE 'Active' END) AS ContactStatus
FROM cadoc_crm.dbo.tContact c
inner join cadoc_system..tsitexcrmcli
on c.nidclient = xc.nidclient
inner join cadoc_system..tsite s
on xc.nidsite = s.nid
left join cadoc_crm..ContactXPrimary
on cp.nIdContact = c.nid
left join cadoc_crm..[Address] ContactAddress
on ContactAddress.nId = cp.nIdAddress
left join cadoc_crm..ClientXAddress ca
on c.nidClient = ca.nIdClient
left join cadoc_crm..[Address] ClientAddress
on ClientAddress.nId = ca.nIdAddress
WHERE
c.cLastName not in('POOL','USER','ADMINIST
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.