Range Lookup

[rumu]

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

[ray.wurlod]

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).

[WoMaWil]

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.

[rumu]

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). ...

Dear Ray,

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.

[chulett]

And?

[rumu]

And?

Sorry,i have skipped the other two mapping .here is the mapping:
In the transfrmer ,i have matched the following:
source.SupplierCode=Ref.SupplierCode and
source.Accnt=Ref.Accnt and the other two mentioned in previous post.