ODBC/Coding issue
Posted: Sat Dec 20, 2008 6:56 pm
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
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