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..
Stage variable on duplicates..
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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?
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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers