Stage variable on duplicates..

[meena]

Good Morning..
I am designing a job which has to handle duplicates.

Job design: Extract data from sequential file to load into oracle table( using DRS Stage) and a transformer stage to check for constraint & for some transformations logic.

Logic: For handling duplicates I am working on stage variables and on a constraint in the transformer stage:

Stage variable=in.field1
Stage variable1=in.field2
Stage varaible2=in.field3
Stage variable3=If (in.field1=Stage variable and in.field2=Stage variable1 and in.field3=Stage variable2) then 1 else 0

And

Constraint in transfomer: Stage variable3<>1
I also checked with Stage variable3=0..

All rows goes into reject file instead of duplicates. I have worked with this logic earlier but know I am facing problem to implement.

Thanks in advance,
Meena..

[ray.wurlod]

To remember the previous row you need to record the value AFTER performing the comparison.

Code: Select all

svIsChanged  <==  (InLink.TheColumn <> svPrevValue) And (@INROWNUM > 1)
svPrevValue  <==  InLink.TheColumn

[chulett]

Why complicate it so much? Three variables like so:

svNewKey: in.field1 : '|' : in.field2 : '|' : in.field3
svIsDup: svNewKey = svOldKey
svOldKey: svNewKey

Make sure you have a decent initial value for the stage variables and you handle nulls in svNewKey if any of the three fields are nullable. Then your constraint could be:

Not(svIsDup)

Isn't that easier to understand?

[meena]

Thanks Ray & Craig,

Both logics worked for me.

regards,
Meena

[ganesh123]

Please mark the topic resolved.