ODBC/Coding issue

[surendra_ds]

Hi All,

We have a requirement that related with Range lookup.We have data as given below,..

Source table

PNo ----SDate------Edate -----Mat
111 12/05/2008 12/06/2008 ABC
111 12/06/2008 12/07/2008 ABC
111 12/07/2008 12/08/2008 ABC
111 12/08/2008 12/09/2008 ABC
111 12/09/2008 12/10/2008 ABC
111 12/10/2008 12/11/2008 ABC
111 12/11/2008 12/12/2008 ABC
111 12/12/2008 12/13/2008 ABC
111 12/13/2008 12/14/2008 ABC
111 12/14/2008 12/15/2008 ABC

LookUp table

PNo ----SDate------Edate ---Volume--Mat
111 12/05/2008 12/08/2008 200 ABC
111 12/08/2008 12/11/2008 250 ABC
111 12/11/2008 12/15/2008 300 ABC

our requirement is to get the volume from lookup if source date is in between the range of sdate and edate.

Here is the expected data :

PNo ----SDate------Edate ---Volume--Mat
111 12/05/2008 12/06/2008 200 ABC
111 12/06/2008 12/07/2008 200 ABC
111 12/07/2008 12/08/2008 200 ABC
111 12/08/2008 12/09/2008 250 ABC
111 12/09/2008 12/10/2008 250 ABC
111 12/10/2008 12/11/2008 250 ABC
111 12/11/2008 12/12/2008 300 ABC
111 12/12/2008 12/13/2008 300 ABC
111 12/13/2008 12/14/2008 300 ABC
111 12/14/2008 12/15/2008 300 ABC

I created a stage variable and I wrote the below logic to get the volume from Lookup table.

If Source.sdate>=lookup.sdate and source.sdate<lookup.edate then lookup.volume else 0

My actual result is

PNo ----SDate------Edate ---Volume--Mat
111 12/05/2008 12/06/2008 200 ABC
111 12/06/2008 12/07/2008 200 ABC
111 12/07/2008 12/08/2008 200 ABC
111 12/08/2008 12/09/2008 000 ABC
111 12/09/2008 12/10/2008 000 ABC
111 12/10/2008 12/11/2008 000 ABC
111 12/11/2008 12/12/2008 000 ABC
111 12/12/2008 12/13/2008 000 ABC
111 12/13/2008 12/14/2008 000 ABC
111 12/14/2008 12/15/2008 000 ABC

To design this job I used ODBC as source and lookup.I joined on Pno and Mat in the joins. I am not sure why ODBC stage is not able to get the volume from between date if it matches the record.Please let me know if I am doing wrong.The same code working some time back but I dont know what happend after some time it stopped working and i end up with the above results.If I am not wrong ODBC and UV stages are supports the range lookup concept.I dont want to use UV here since we need to create hash file and read hash file from UV.

Please let me if I am doing wrong coding.

Thanks,

Regards
Suri

[ray.wurlod]

Pno and Mat does not give you a unique return result from the lookup. Did you enable multi-row return from the lookup? Otherwise, to get a unique result, include SDate as a lookup key, since this is also available in your source.

[surendra_ds]

Pno and Mat does not give you a unique return result from the lookup. Did you enable multi-row return from the lookup? Otherwise, to get a unique result, include SDate as a lookup key, since this is ...

Hi Ray,

Thanks for quick responce.I tried with Multi row option in ODBC and I have used Sdate as joins in lookup.Its working if we have one plan number(pno) and one Material(mat).if we have multiple plan numbers and multiple materials for each plan number,its not working.

Can't we do in between operator in Stage variable.If source sdate between look up sdate and lookup edate?

Please clarify me ..

Regards,
Suri

[ray.wurlod]

Before you look at mechanisms, look at solutions. What do you want to get, if there are multiple plan numbers and/or multiple materials for a given plan number? Multiple rows? Some kind of yes/no answer? Once you have come up with a specification only then is it time to investigate mechanisms.