sqloracleora-00918

How to fix PL/SQL: ORA-00918: column ambiguously defined in Oracle


I'm creating a package in Oracle, and when I've compiled the body of the package, i am getting the PL/SQL: ORA-00918: column ambiguously defined error.

I've gone through the code, and double checked the aliases, so am a bit stumped as to why I am receiving this error.

The error in question is on Line 10. The PERSON_CODE, FUND_YEAR and UIO_ID in the WHERE clause are the arguments on the function that I am creating in the package.

    SELECT CASE 
            WHEN LH.PROP_NOT_TAUGHT > 50 AND LA.DELIVERY_PROVIDER IS NOT NULL THEN TO_NUMBER(OU.UKPRN)
            ELSE LA.UK_PROV_NO 
           END AS UKPRN_T
    FROM FES.LEARNER_AIMS LA
     JOIN FES.LEARNER_HE LH
      ON LH.PERSON_CODE = LA.PERSON_CODE
       AND LH.FUNDING_YEAR = LA.FUNDING_YEAR
     LEFT JOIN FES.ORGANISATION_UNITS OU
      ON OU.ORGANISATION_CODE = LA.DELIVERY_PROVIDER
    WHERE LA.PERSON_CODE = PERSON_CODE
     AND LA.FUNDING_YEAR = FUND_YEAR
     AND LA.UIO_ID = UIO_ID;

Solution

  • Your function parameter name and the name of the field are clashing, creating a shadowing effect. You can prefix the name of the parameter with the function name to remove the ambiguity

     AND LA.UIO_ID = MyfunctionName.UIO_ID;
    

    Alternatively, rename the parameter to avoid such occurrences.