Lookup Stage. Lookup failure records to Rejected file.
Moderators: chulett, rschirm, roy
Lookup Stage. Lookup failure records to Rejected file.
I am using a SINGLE Lookup stage, which does lookups into 5 different tables and gets the corresponding values for each record. Now, I wanted to send each lookup fail record, into a different output link. So, I am trying to have 5 different output links coming from the Lookup stage and route the records to different outputs based on the lookup value. But the Lookup stage allows me to output to only one Output link.
Any help is appreciated.
Thanks
Any help is appreciated.
Thanks
Hi,
As we know that the Lookup stage can have reference links, a single input link, a single output link, and a single rejects link.
According to your specifications:Can you try to use join stage and pass them to transformer and then to output files.You can write constraints based on your specification too,if you need them.(else if the source is also a database and all tables are in same schema then we can write sql query and can pass them to transformer).
As we know that the Lookup stage can have reference links, a single input link, a single output link, and a single rejects link.
According to your specifications:Can you try to use join stage and pass them to transformer and then to output files.You can write constraints based on your specification too,if you need them.(else if the source is also a database and all tables are in same schema then we can write sql query and can pass them to transformer).
Thanks for your responses. But. let me frame my question much better.
I am actually loading my FACT table. In this process, I have 5 Dimension tables linked to the Fact table(PKs of the DIM tables as FK's in the Fact).
Before loading records to the Fact, I do a Lookup to each of the Dimension tables to see if the parent record exists in my DIM tables or not. If it does not exist, then I want to send them to the REJECT table and an Error message to notify that the Parent record does not exist.
I guess if DS had allowed me to define multiple Reject links, I guess my problem would have been solved. Any ideas..
Thanks
I am actually loading my FACT table. In this process, I have 5 Dimension tables linked to the Fact table(PKs of the DIM tables as FK's in the Fact).
Before loading records to the Fact, I do a Lookup to each of the Dimension tables to see if the parent record exists in my DIM tables or not. If it does not exist, then I want to send them to the REJECT table and an Error message to notify that the Parent record does not exist.
I guess if DS had allowed me to define multiple Reject links, I guess my problem would have been solved. Any ideas..
Thanks
Hi Ed,edwds wrote:Thanks for your responses. But. let me frame my question much better.
I am actually loading my FACT table. In this process, I have 5 Dimension tables linked to the Fact table(PKs of the DIM tables as FK's in the Fact).
Before loading records to the Fact, I do a Lookup to each of the Dimension tables to see if the parent record exists in my DIM tables or not. If it does not exist, then I want to send them to the REJECT table and an Error message to notify that the Parent record does not exist.
I guess if DS had allowed me to define multiple Reject links, I guess my problem would have been solved. Any ideas..
Thanks
have 5 transformers one after the other and have rejects define for each of them[you are the "Boss"].
![Cool 8)](./images/smilies/icon_cool.gif)
![Cool 8)](./images/smilies/icon_cool.gif)
regards,
Prabu
that's exactly what i meant to say ,ie., to have lookup filesets and do sequential lookups on them. but i have used transformer instead of lookupfile setsemma wrote:Try another approach.
Are you doing your lookup against entire dimension table ? Maybe creating your lookup files one step before might be more performing and gives you the possibility to have your rejects per table.
I am trying to use the Merge Stage.
I am getting the Master Input data for my Fact table from a Table in Oracle.
I also have 5 additional Input streams coming from Oracle tables(each table is an Input from the DIMENSION table). So the MERGE stage is getting 6 Input streams(1 Master and other 5 Input data sets from Dimension tables).
The master table has a PK, which is a combination of 5 columns, all FK's from the DIM tables(5 columns).
And each other Input stream has only ONE column as the key.
Looks like its not possible to use a Merge stage, when you DON'T have the same Keys for all imput streams.
I am getting the Master Input data for my Fact table from a Table in Oracle.
I also have 5 additional Input streams coming from Oracle tables(each table is an Input from the DIMENSION table). So the MERGE stage is getting 6 Input streams(1 Master and other 5 Input data sets from Dimension tables).
The master table has a PK, which is a combination of 5 columns, all FK's from the DIM tables(5 columns).
And each other Input stream has only ONE column as the key.
Looks like its not possible to use a Merge stage, when you DON'T have the same Keys for all imput streams.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The only solutions are:
- (1) five separate Lookup stages (only one reject link allowed)
(2) one Merge stage (one reject link per reference input)
(3) six-table Join and check for nulls (cumbersome!)
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.