Lookup on oracle table having dates,sparse lookup not workin
Posted: Tue Apr 21, 2009 1:45 am
Hi,
I have an oracle table with an customer id, customer name, start date,end date. This is a dimension table with SCD-Type2 logic.
I have a source txt file which has a date column and the customer id. I need to fetch the customer name from the dimension table(oracle) based on the customer id and the date value in the file should be between the starte end and end date in the oracle table. in some cases the end date in the oracle table can be null.
I tried with a sparse lookup for this with the following query but the job is aborting,
SELECT CUSTOMERID,CUSTOMERNAME FROM CUSTOMER WHERE CUSTOMERID=ORCHESTRATE.EMP_NO AND ORCHESTRATE.STDATE >= (to_char(RECEFFDATE,'yyyymmdd') and ORCHESTRATE.STDATE <= to_char(decode(RECENDDATE,'',sysdate,RECENDDATE),'yyyymmdd')
here, STDATE is the date column from the file, emp_no is the customer id from the file.
Can anyone please help me on how to handle this scenario?
Thanks
I have an oracle table with an customer id, customer name, start date,end date. This is a dimension table with SCD-Type2 logic.
I have a source txt file which has a date column and the customer id. I need to fetch the customer name from the dimension table(oracle) based on the customer id and the date value in the file should be between the starte end and end date in the oracle table. in some cases the end date in the oracle table can be null.
I tried with a sparse lookup for this with the following query but the job is aborting,
SELECT CUSTOMERID,CUSTOMERNAME FROM CUSTOMER WHERE CUSTOMERID=ORCHESTRATE.EMP_NO AND ORCHESTRATE.STDATE >= (to_char(RECEFFDATE,'yyyymmdd') and ORCHESTRATE.STDATE <= to_char(decode(RECENDDATE,'',sysdate,RECENDDATE),'yyyymmdd')
here, STDATE is the date column from the file, emp_no is the customer id from the file.
Can anyone please help me on how to handle this scenario?
Thanks