Getting Ambiguous Column Name Error

Question

So I'm pretty new to this program and cannot figure out why I am getting this error. Here is the qry.

SELECT policy_no, 
       phase_code, 
       sub_phase_code, 
       Duplicate_Indicator = CASE 
                               WHEN Sum(1) <> 1 THEN 'DUPLICATE' 
                               ELSE 'NOT DUPLICATE' 
                             END 
FROM   qrypolicylistfornydefpremasset_re 
       RIGHT JOIN (SELECT TOP 100 PERCENT tblinsurance.policy_no, 
                                          tblinsurance.phase_code, 
                                          tblinsurance.sub_phase_code, 
                                          tblinsurance.prodtype, 
                                          tblinsurance.paid_to_date, 
                                          tblinsurance.val_date, 
                                          tblinsurance.issue_date, 
                                          tblinsurance.schednp, 
                                          Sum(tblinsurance.gross_annlzd_prem) AS 
                  SumOfGROSS_ANNLZD_PREM, 
                                          Sum(tblinsurance.statnetpremium)    AS 
                  SumOfStatNetPremium, 
                                          Sum(tblinsurance.netdefpremium)     AS 
                  SumOfNetDefPremium, 
                                          Sum(tblinsurance.netdefextra)       AS 
                                                           SumOfNetDefExtra, 
                                          Sum(tblinsurance.netdefpremiumadj)  AS 
                  SumOfNetDefPremiumAdj, 
                                          Sum(tblinsurance.netdefextraadj)    AS 
                  SumOfNetDefExtraAdj, 
                                          NetvsGrossInd = CASE 
                                                            WHEN 
                                          [netdefpremium] = [netdefpremiumadj] 
                                                          THEN 
                                                            'Net' 
                                                            ELSE 'Gross' 
                                                          END, 
                                          Sum(tblinsurance.amount_inforce)    AS 
                  SumOfAMOUNT_INFORCE, 
                                          Sum(tblinsurance.pua_face)          AS 
                                                           SumOfPUA_FACE, 
                                          Sum(tblinsurance.oyt_face)          AS 
                                                           SumOfOYT_FACE 
                   FROM   tblinsurance 
                   WHERE  ( ( ( tblinsurance.company_code ) = 'NL' ) 
                            AND ( ( tblinsurance.term_reason ) = 'A' ) 
                            AND ( ( tblinsurance. issue_date ) <= [val_date] ) ) 
                   GROUP  BY tblinsurance.policy_no, 
                             tblinsurance.phase_code, 
                             tblinsurance.sub_phase_code, 
                             tblinsurance.prodtype, 
                             tblinsurance.paid_to_date, 
                             tblinsurance.val_date, 
                             tblinsurance.issue_date, 
                             tblinsurance.schednp, 
                             CASE 
                               WHEN [netdefpremium] = [netdefpremiumadj] THEN 
                               'Net' 
                               ELSE 'Gross' 
                             END 
                   HAVING (( ( Sum(tblinsurance.netdefpremium) ) <> 0 )))Work 
               ON policy_no = qrypolicylistfornydefpremasset_re.policy_no 
                  AND phase_code = qrypolicylistfornydefpremasset_re.phase_code 
                  AND sub_phase_code = 
qrypolicylistfornydefpremasset_re.sub_phase_code 
GROUP  BY policy_no, 
          phase_code, 
          sub_phase_code 

Where I am getting the ambiguous colum name is on the Fields POLICY_NO, PHASE_CODE, SUB_PHASE_CODE, but only in the On and GROUP by at the end and the ones in the SELECT in the beginning.

Answer 1

well, you've got these fields in both

qryPolicyListforNYDefPRemAsset_Re

and in your subquery Work

so prefix them with the right table (or better, use aliases).

I prefixed with Work in SELECT and GROUP BY and JOIN I may be wrong for SELECT and GROUP BY (maybe you need q, alias for qryPolicyListforNYDefPRemAsset_Re) :I'm like Sql, your query is too ambiguous for me ;)

SELECT 
    Work.POLICY_NO, 
    Work.PHASE_CODE, 
    Work.SUB_PHASE_CODE,
    Duplicate_Indicator = case when 
    SUM(1) <> 1 then 'DUPLICATE'
    else 'NOT DUPLICATE'
    end 
    FROM 

    qryPolicyListforNYDefPRemAsset_Re q

    RIGHT JOIN 

        (SELECT top 100 percent 
        t.POLICY_NO, 
        t.PHASE_CODE, 
        t.SUB_PHASE_CODE, 
        t.ProdType, 
        t.PAID_TO_DATE, 
        t.VAL_DATE, 
        t.ISSUE_DATE, 
        t.SchedNP,
        Sum(t.GROSS_ANNLZD_PREM) AS SumOfGROSS_ANNLZD_PREM, 
        Sum(t.StatNetPremium) AS SumOfStatNetPremium, 
        Sum(t.NetDefPremium) AS SumOfNetDefPremium, 
        Sum(t.NetDefExtra) AS SumOfNetDefExtra, 
        Sum(t.NetDefPremiumAdj) AS SumOfNetDefPremiumAdj, 
        Sum(t.NetDefExtraAdj) AS SumOfNetDefExtraAdj, 
        NetvsGrossInd = Case when [NetDefPremium]=[NetDefPremiumAdj] then 'Net' Else 'Gross' END, 
        Sum(t.AMOUNT_INFORCE) AS SumOfAMOUNT_INFORCE, 
        Sum(t.PUA_FACE) AS SumOfPUA_FACE, 
        Sum(t.OYT_FACE) AS SumOfOYT_FACE
        FROM tblInsurance t
        WHERE (((t.COMPANY_CODE)='NL') AND ((t.TERM_REASON)='A') AND ((t.ISSUE_DATE)<=[VAL_DATE]))
        GROUP BY 
        t.POLICY_NO, 
        t.PHASE_CODE, 
        t.SUB_PHASE_CODE, 
        t.ProdType, 
        t.PAID_TO_DATE, 
        t.VAL_DATE, 
        t.ISSUE_DATE,
        t.SchedNP,
        Case when [NetDefPremium]=[NetDefPremiumAdj] then 'Net' Else 'Gross' END
        HAVING (((Sum(t.NetDefPremium))<>0))
        )Work


    ON 
    Work.POLICY_NO = q.POLICY_NO AND 
    Work.qPHASE_CODE= q.PHASE_CODE AND 
    Work.SUB_PHASE_CODE = q.SUB_PHASE_CODE 
    GROUP BY 
    Work.POLICY_NO, 
    Work.PHASE_CODE, 
    Work.SUB_PHASE_CODE