Consider a scenerio, where I have to perform lookup on a reference table STROE based on a column say STORE_I and also check for the condition where BTCH_CRTE_TS is between ETL_PRCS_EFF_TS and ETL_PRCS_END_TS. ETL_PRCS_EFF_TS and ETL_PRCS_END_TS are obtained from the reference table and BTCH_CRTE_TS from the source.
Using a lookup stage it is possible only to perform a lookup on STORE_I and then fetch ETL_PRCS_EFF_TS and ETL_PRCS_END_TS, following which we need to use a transformer stage to find out if BTCH_CRTE_TS is between ETL_PRCS_EFF_TS and ETL_PRCS_END_TS, then choose the appropriate row and reject the others.
Could you help me find a solution where only a single stage could help us out as we have huge amount of data we are working with approx 25 million per day. Also have 3 such reference tables with the same condition to be used. In such a case we would need to use 3 lookups and 3 transformers which would be costly for our job.
Lookup condition -- Is there an alternative way to handle
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 247
- Joined: Thu Apr 27, 2006 6:38 am
- Location: Hyderabad
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
-
- Participant
- Posts: 247
- Joined: Thu Apr 27, 2006 6:38 am
- Location: Hyderabad
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You can check the condition in the "rules" part of the Lookup stage. If the lookup fails there are four possibilities; Continue, Drop, Fail or Reject. If the condition to undertake the lookup in the first place is not met, the same four possibilities obtain.
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: 247
- Joined: Thu Apr 27, 2006 6:38 am
- Location: Hyderabad
[quote="ray.wurlod"]You can check the condition in the "rules" part of the Lookup stage. If the lookup fails there are four possibilities; Continue, Drop, Fail or Reject. If the condition to undertake the lookup in the ...[/quote]
Thank you for the reply.
Here the issue is with checking two columns from reference table ETL_PRCS_EFF_TS and ETL_PRCS_END_TS if it has BTCH_CRTE_TS soruce column between them.
We would not be able to use two reference columns in the "rules" section.
Hence need an aditional transformer. Another way out?
Thank you for the reply.
Here the issue is with checking two columns from reference table ETL_PRCS_EFF_TS and ETL_PRCS_END_TS if it has BTCH_CRTE_TS soruce column between them.
We would not be able to use two reference columns in the "rules" section.
Hence need an aditional transformer. Another way out?
-
- Participant
- Posts: 247
- Joined: Thu Apr 27, 2006 6:38 am
- Location: Hyderabad