My query as follows
select Av.Associate_ID as Associateid,Av.IsActive,CAD.value,CAD.ValidateFlag,CGA.facility
from
[CentralRepository].[dbo].[vw_CentralRepository_Associate_Details] Av
inner join [CentralRepository].[dbo].[vw_CentralRepository_Contacts] CAD
ON CAD.[Associate_ID]=Av.[Associate_ID]
inner join [CentralRepository].[dbo].[vw_CentralRepository_GSMS_AssociateSeatInfo] CGA
ON CGA.[Associate_ID]=Av.[Associate_ID]
where CGA.[Associate_ID] = '101012'(select
"Address" = LTRIM(RTRIM((LTRIM(RTRIM(CAA.Address1)) + ', '+
CASE WHEN LTRIM(RTRIM(CAA.Address2))!='' THEN LTRIM(RTRIM(CAA.Address2)) + ', ' ELSE '' END +
CASE WHEN LTRIM(RTRIM(CAA.Address3))!='' THEN LTRIM(RTRIM(CAA.Address3)) + ', ' ELSE '' END+
LTRIM(RTRIM((COALESCE(replace(CAA.address4,' ',''),'')+ CASE WHEN COALESCE(caa.Address4,'') !='' THEN + ', '+
CASE WHEN COALESCE(RTRIM(CAA.city),'')!='' THEN COALESCE(RTRIM(CAA.city),'') END + ' '+ ', '+
CASE WHEN COALESCE(RTRIM(CAA.Pincode),'')!='' THEN COALESCE(RTRIM(CAA.Pincode),'') ELSE '' END
ELSE COALESCE(RTRIM(CAA.city),'')+', '+COALESCE(RTRIM(CAA.Pincode),'') END )))))) FROM
[OneC_988].[dbo].[988_Details_AssociateProfile] AP
LEFT JOIN [CentralRepository].[dbo].[vw_CentralRepository_Associate_Details] CAD
ON CAD.[Associate_ID]=AP.[varchar_AssociateID]
LEFT JOIN [CentralRepository].[dbo].[vw_CentralRepository_Contacts] CONT
ON CONT.Associate_ID=AP.[varchar_AssociateID] AND CONT.Contact_Type ='MBL1'-- AND CONT.ValidateFlag = 'S'
LEFT OUTER JOIN [CentralRepository].[dbo].[vw_CentralRepository_GSMS_AssociateSeatInfo] CGA
ON CGA.[Associate_ID]=AP.[varchar_AssociateID]
LEFT JOIN CentralRepository..vw_CentralRepository_AssociateAddress CAA
ON CAA.Associate_ID=AP.[varchar_AssociateID]
AND AddressType IN (SELECT CASE WHEN COALESCE(COUNT(Associate_ID),0) >= 1 THEN 'Mail' ELSE 'Home' END
FROM CentralRepository..vw_CentralRepository_AssociateAddress))
When i run the above query shows output as follows
Assoicate IsActive Value Validateflag Facility
101012 A 984/123-4567 S CHN - NVL
Address
Street
Using above query i want output as follows
Assoicate IsActive Value Validateflag Facility Address
101012 A 984/123-4567 S CHN - NVL Street
for getting a above output how can i do.
1 Reply
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Raja TPosted Jun 2, 2016, 9:30 AM