My query as follows
select Av.Associate_ID as Associateid,Av.IsActive,CAD.value,CAD.ValidateFlag from
[CentralRepository].[dbo].[vw_CentralRepository_Associate_Details] Av
inner join [CentralRepository].[dbo].[vw_CentralRepository_Contacts] CAD
ON CAD.[Associate_ID]=Av.[Associate_ID] ((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 error as follows
Cannot find either column "Av" or the user-defined function or aggregate "Av.Associate_ID", or the name is ambiguous.
please help me what is the problem in my query.
Vinay SinghPosted Jun 14, 2016, 6:35 AM
Baimey RajeshPosted Jun 14, 2016, 6:14 AM