We need to do a dynamic lookup against a reference table against columns A,B. ie if the values for A,B do not exist in the reference table, insert a new row along with a new surrogate key id. How ever the reference table has duplicate values for columns A,B. As a result the session log throws the error "
A duplicate row was attempted to be inserted into a dynamic lookup cache [lkp_12]. The dynamic lookup cache only supports unique condition keys.
Ideally the reference table should not be having duplicate values and getting it cleaned is beyond the scope of our project.
Hence can not use dynamic lookups. A work around would be
mapping 1. read from source --> static lookup against reference table --> determine new inserts --> insert onto reference table
mapping 2 . read from source --> static lookup against reference table --> get id --> use it to populate onto target.
this requires 2 pass reading from source.
are there any other alternatives in this case. Dynamic lookups with duplicates. thanks.
Have u tries using lookup query to get only distinct records.....
Does ur source also contains duplicates?
I don't think u have to use 2 mappings here. If it is only inserts mapping2 is enough..no need to use dynamic lookup also...
basically need to lookup against columns A,B and get the value X. Tried the option of SQL override and in the override
SELECT A,B,MAX(X) from lookup table.
With the above SQL over ride, the mapping runs succesfully. how ever if there is a new value for A,B which is not present in the target table, ideally it should insert the value onto the target table. But even though it is a dynamic cache does not insert new values.
It should be like
Source-----> SQ-----> lookup----->filter---> exp------>target table
in lookup (static) condition id LKP_A=SRC_A and LKP_B=SRC_B. get X from lookup into filter.
filter condition is ISNULL(X)
and in expression generate new value for X and insert into the target table.
Is there anu other option ur trying?
If u want mapping send me some sample source data and which records to be inserted....
Here is the lookup will be static. It wont be getting synchronized as and when the data on the target table is getting changed.
ANy work around for this. thanks in advance.
Thanks for your reply. Here are the details.
We have values A,B coming from source. Look for this values on a look up table and obtain the value of X. If the value exists fetch, if not insert a new value for X in lookup along with A,B. X is a sequentially incrementing number.
To suit the above requirement, the lookup needs to be dynamic. But the problem is that there are duplicate values of a,b in the lookup table. Since this is a dynamic lookup INFA throws the error
"A duplicate row was attempted to be inserted into a dynamic lookup cache [lkp_12]. The dynamic lookup cache only supports unique condition keys"
If ur source also contains duplicates, then u hv to use dynamic lkp,else static lkp will solve ur prblm.
Hv u tried my mapping. Confirm whether ur siurce hv duplicates, I ill provide u
yes the source has duplicates. can not use dynamic lookup as there are duplicates in the lookup reference table also.
the mapping solution provided by you will work as long as there are no duplicates from source. but in our case, we have duplicates from source.
Hi try following mapping. I have created this as per my understanding..just le me know if i miss something here.
create table dummy
insert into dummy values(1,1);
insert into dummy values(1,2);
insert into dummy values(1,3);
insert into dummy values(2,4);
insert into dummy values(3,5);
Source i have taken
Let me know if this solves ur problem.
Thanks for your inputs. I tried the below and it is working.
SRC --> Dynamic lkp --> Target
in Dynamic lookup, do SQL override
select a,b,max(X) from lookup
then click on the option SYNCHRONIZE table
it is working now as expected.
Your help is appreciated.
I am also facing the same issue
My source is DB2 and i am applying Dynamic LKP on Teradata Target and comapring ID column coming from src to target column.
The Error message is "CMN-1650 A duplicate row was attempted to be inserted into a dynamic lookup cache The dynamic lookup cache only supports unique condition keys"
Please provide your inputs.