Hi
I am having a problem implementing a logic. I have a fixed width flat file which has Customer, Account,Group and Product IDs (all characters) as columns.
Firstly I would like to reject all records which have any of these values as Null.
Secondly I have to see that a concatenation of these 4 values already exists in a database and continue processing the record only if its present.
Ex: Customer1+Account1+Group1+ProductID1 is present in the database then only pass the record for further processing else reject it.
I have tried defining 4 values as keys in hash file (loading data from database) to do a equijoin in transformer, but that does n't seem to be working.
I have tried many different ways to execute the logic, but both cases seem to be failing in the job.
I would really appreciate if anyone could explain how to do this for me.
DESIGN
Database
|
hashfile
|
V
Tx------------->database
^
|
|
flatfile
Thanks
Bob[/img]
Validating records using Key Expression in transformer
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Your design as sketched is OK, but we can't see the stage and link properties.
Populate your hashed file with the four key columns. Make sure that they are all marked as key columns.
In your Transformer stage you will be required to enter four reference key expressions - presumably one from each relevant stream input column.
On the output link of the Transformer stage add a constraint expression to test whether the lookup succeeded or not. Either use the NOTFOUND link variable (with a Not() function), or test any of the returned key columns with an IsNull() function (again within a Not() function).
Populate your hashed file with the four key columns. Make sure that they are all marked as key columns.
In your Transformer stage you will be required to enter four reference key expressions - presumably one from each relevant stream input column.
On the output link of the Transformer stage add a constraint expression to test whether the lookup succeeded or not. Either use the NOTFOUND link variable (with a Not() function), or test any of the returned key columns with an IsNull() function (again within a Not() function).
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.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom