Ambiguous error in Lookup Override for teradata query

Question

I am using below self join query as Lookup override in informatica. This is running fine in teradata.

SELECT A.region_cd AS REGION_CODE, 
       A.enp_no    AS ENP_NBR, 
       B.sla_cd    AS SLA_CODE 
FROM   edb_man_work.emp A, 
       edb_man_work.emp B 
WHERE  A.company_no = Trim(Cast(B.enp_no AS INTEGER)) 
       AND A.region_cd = B.region_cd 

This is running fine in teradata but while running in mapping it is giving error

as Column SLA_CD is ambiguous.

I am not sure why this is giving this type of error.

Answer 1

Since you're using multiple source tables, make sure you end the Lookup SQL override with --.

If you look at the session log, you'll see that Informatica automatically appends Lookup SQL overrides with an ORDER BY statement. Adding the -- will comment out this addition.

SELECT A.region_cd AS REGION_CODE, 
       A.enp_no    AS ENP_NBR, 
       B.sla_cd    AS SLA_CODE 
FROM   edb_man_work.emp A, 
       edb_man_work.emp B 
WHERE  A.company_no = Trim(Cast(B.enp_no AS INTEGER)) 
       AND A.region_cd = B.region_cd 

--