top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration

Facebook Login
Site Registration

Ambiguous error in Lookup Override for teradata query

+3 votes
422 views

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.

posted Oct 30, 2014 by Amit Sharma

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

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 

--

answer Oct 31, 2014 by Shweta Singh
Similar Questions
0 votes

What will happen if the SELECT list COLUMNS in the Custom override SQL Query and the OUTPUT PORTS order in SQ transformation do not match?

+2 votes

Within Informatica I have a Mapping which uses a unconnected lookup. This Mapping I use in a Session wherein I define a Relational DB connection for this lookup transformation.

Now, which repository tables will I have to query to get the connection names for all my lookups in my sessions? I tried REP_SESS_WIDGET_CNXS, but there are only connection names for sources and targets of my sessions.

Any idea on this one?

+3 votes

I am using a mapping variable of date/time data-type and aggregation MAX In Source Qualifier SQL Override i am giving select col1,col2 from tbl where col1>$$dt

(col1 is also date data type)

When i am validating the query it is giving Invalid Query and error was

 [Microsoft][ODBC driver for Oracle][Oracle]ORA-00933 : SQL command not properly ended

I have also done extraction of characters like select col1,col2 from tbl where to_char(col1,'DD-MM-YY')>to_char($$dt,'DD-MM-YY') but still the same same

I am not getting what the error is and the same thing if i am trying for number column the it is validating fine and performing incremental extract also.

Any suggestions regarding the same will be helpful

+1 vote

I am calling an unconnected lookup in Informatica and I am getting this error:

ERROR : Lookup port rfb_id in lkp_V_CLAIM_ELIGBLTY_EFF_LTCG_seqid not specified in lookup override
.
.
.

Transformation Parse Fatal Error; transformation stopped...
TE_7002 [<<PM Parse Error>> [:LKP.RLKP_V_CLAIM_ELIGBLTY_EFF_LTCG_SEQID(rfb_id)]: : invalid function reference
... >>>>:LKP.RLKP_V_CLAIM_ELIGBLTY_EFF_LTCG_SEQID(rfb_id)<<<<]

My lookup is as follows

Ports:

in_rfb_id [Input]
rfb_id [Lookup]
CLAIM_ELIG_EFF_LTCG_SEQ_ID [Output, Lookup, Return]
SQL Override:

select 
   max(CLAIM_ELIG_EFF_LTCG_SEQ_ID) as CLAIM_ELIG_EFF_LTCG_SEQ_ID
from
   $$FAR_CR_VW.V_CLAIM_ELIGBLTY_EFF_LTCG
   -- where rfb_id = in_rfb_id
 group by rfb_id  --
Condition:

rfb_id = in_rfb_id

Other information:

data types are the same between lookup and expression, both integer
SQL returns a one column list of 962 integers

+2 votes

I have been trying a lookup and update strategy transformation but for some reason its not working.

I have a disconnected lookup in my mapping which is called based on a particular condition and when the look up is null , I tried to set a variable to 'INS',REJ,UPD,DEL..

The new variable in my agg_trans-INS_UPD_DEL is not getting linked to the upd_strategy trans. I'm able to move the variable from expression to update_strategy but dont see the link being displayed..

enter image description here

Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...