Comparison and Map Operation

dataservices job design
1

Hello Guys

i need a little help.

JOB:

Source Table
Table1 - ID1, ID2, ID3, ID4, Value, Value_Old
Table2 - AE_Number

Target Table
Table3 - ID1, ID2, ID3, ID4, Value_Old

Query
ID1, ID2, ID3, ID4, Value
JOIN - Table1.AE_Number = Table2.AE_Number

 Group by ID1, ID2, ID3, ID4

Table Comparison
Primary Keys - ID1, ID2, ID3, ID4

Map Operation
Normal = discard
Update = discard
Insert = Insert
Delete = discard

But everytime my JOB runs i get an error

Injury of the PRIMARY key restriction ’ table3_PK’. A double key can’t insert in ’ table3

I hope someone can help me

Hollow :de: (BOB member since 2009-04-08)

2

Are you trying to implement SCD Type1???

gssharma :india: (BOB member since 2006-10-30)

3

Just to be complete, what is the PK of the target table? I assume ID1,ID2, ID3, ID4?

Werner Daehn :de: (BOB member since 2004-12-17)

4

yes the PK is ID1,ID2, ID3, ID4

ID1 = Datetime
ID2 = INT
ID3 = INT
ID4 = INT

I’m only trying to insert new rows and no Updates.

It might be helpful to say that Query ID1 contains this mapping

add_months(TBL_AUFTRAGSEINGANG.AUFTRAG_DATUM,12)

Hollow :de: (BOB member since 2009-04-08)

5

it does not matter, you do a group by on the primary key, then compare and insert the not found.

So the question is, when does a group by do not return a primary key combination? And when does the comparison not work?

I have NULL values in mind and more likely, data type conversions.

Example: In the source ID1 is datetime like 2009.12.31 13:56, in the target it is a date only. So the group by will return different records per time and during the insert the time portion is truncated making multiple records of the same day with different time portions the same.
You TC tries to find a record for 13:56 but can’t due to the date datatype (not datetime).

Werner Daehn :de: (BOB member since 2004-12-17)

6

ok i have resolved the problem.

i added another Query after the first one.

and switched the group by function to the second one.

Solution


Query 1
ID1, ID2, ID3, ID4, Value
JOIN - Table1.AE_Number = Table2.AE_Number

Query 2
Group by ID1, ID2, ID3, ID4

Hollow :de: (BOB member since 2009-04-08)