Dear All,
For range lookup,if I would like to use ODBC stage ,is it ok?
In our project ,we have some sonstraint of using UV stage .
My criteria is following:
There are 2 key fileds in REF table
SupplierCode
Accnt
in addition to that the following columns are there:
RunNBRStart
RunNBREND
LotNBR
In the source folloiwng columns are coming:
SupplierCode
Accnt
RunCaseNBR
IF source.SupplierCode=Ref.SupplierCode and
source.Accnt=Ref.Accnt
and ref.RunNBRStart<=source.RunCaseNBR AND ref.RunNBREND>=source.RunCaseNBR then pickup Ref.LotNBR.
Job Design:
ODBCLKP
SRC------>Tnsfr-------->Tgt
in Trnf stage I mapped source.SupplierCode=Ref.SupplierCode ,
source.Accnt=Ref.Accnt,source.RunCaseNBR=ref.RunNBRStart and
source.RunCaseNBR=source.RunNBREND
In the ODBC stage following query is used:
select LotNBR,RunNBRStart,RunNBREND,SupplierCode,Accnt from reftable
whre Ref.SupplierCode=? and Ref.Accnt=?and ref.RunNBRStart<=? AND ref.RunNBREND>=?
I have designed the above,but deu to unavailability of source data could not run and we donot have priviledge to create test data in DEV also.
Could you pls let me know if this is ok?
Many thanks for all your time.
Regards
Range Lookup
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Should be OK, provided you have four columns marked as Key with which the parameter markers can be substituted (the fourth "key" may need to be a copy of the third).
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.
Dear Rumu,
the solution depends a bit on how many pairs of RunNBRStart/RunNBRStart you have for a SupplierCode/Accnt pair. If that is not in a many thousands you can have the following solution for Hash-Stage-Solution.
You keep supplier/accnt as key
you make up an array for RunNBRStart/RunNBRStart/LotNBR (that is a special multidemension feature of a hashfile, look for details in the manual).
Then write after the stage of Lookup everything in an other HashFile and open on the other side the hashFile denormalised then you can make in the transformer thereafter your range derivation and write only that row from the array which fits your request. That is it with HashFile and this solution is rather rapid.
the solution depends a bit on how many pairs of RunNBRStart/RunNBRStart you have for a SupplierCode/Accnt pair. If that is not in a many thousands you can have the following solution for Hash-Stage-Solution.
You keep supplier/accnt as key
you make up an array for RunNBRStart/RunNBRStart/LotNBR (that is a special multidemension feature of a hashfile, look for details in the manual).
Then write after the stage of Lookup everything in an other HashFile and open on the other side the hashFile denormalised then you can make in the transformer thereafter your range derivation and write only that row from the array which fits your request. That is it with HashFile and this solution is rather rapid.
Wolfgang Hürter
Amsterdam
Amsterdam
Dear Ray,ray.wurlod wrote:Should be OK, provided you have four columns marked as Key with which the parameter markers can be substituted (the fourth "key" may need to be a copy of the third). ...
I hav marked all the four columns as key in the ODBC lookup stage and in the transformer i have matched source.RunCaseNBR to ref.RunNBRStart and ref.RunNBREnd.