Category: ASP.NET

02 Jun 2016 00:28

Joined:	23/08/2014
Level:	Silver
Points:	104

Points:	5

Cannot find either column "Av" or the user-defined function or aggregate "Av.Associate_ID", or the n

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 above query.

#766301

02 Jun 2016 02:25

Joined:	29/05/2009
Level:	Gold
Points:	10380

Points:	2

Hi

your query line here
SELECT CASE WHEN COALESCE(COUNT(Associate_ID),0

you did not mention alias name

try this sample query



SELECT (a.CountryName) FROM TB_COUNTRY a

aggregate means group need then i think your count part is throw error so.

Name : Dotnet Developer-2015
Email Id : kumaraspcode2009@gmail.com

'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

#766307

02 Jun 2016 03:01

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	4

Hi,

While preparing SQL Query don't make query blindly based on your output expectation make it step by step, you did no of joins in your query be careful while joining multiple tables and be careful while calling the columns because if the column is available more than one table and you didn't call it using alias name / table name then obviously you will get Ambiguous column error, In your case in the below line you got that error


SELECT CASE WHEN COALESCE(COUNT(Associate_ID),0)

In the above line you didn't call the Associate_ID with either alias name / table name, since the Associate_ID is available in more than one table you should call it either any one of them.

Hope this will helpful to you...

--------------------------------------------------------------------------------
Give respect to your work, Instead of trying to impress your boss.

N@veen
Blog : http://naveens-dotnet.blogspot.in/

#766327

03 Jun 2016 04:29

Joined:	30/03/2016
Level:	Bronze
Points:	58

Points:	2

Hi,

I think you have to give instance name for <b>addresstype</b> column.
<pre>AddressType IN (SELECT CASE WHEN COALESCE(COUNT(chk.Associate_ID),0) >= 1 THEN 'Mail' ELSE 'Home' END
FROM CentralRepository..vw_CentralRepository_AssociateAddress chk)))
</pre>

Cannot find either column "Av" or the user-defined function or aggregate "Av.Associate_ID", or the n

About dotnetspider.com

Quick Links

General

Services