You must Sign In to post a response.
  • Category: ASP.NET

    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
    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
    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
    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>


Sign In to post your comments