Lookup on oracle table having dates,sparse lookup not workin

[vimali balakrishnan]

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

[Pagadrai]

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

Hi,
1) your date comparision is incorrect. You are comparing strings here instead of dates. ( I assume that incoming date value is a string )
to_date(ORCHESTRATE.STDATE,'yyyymmdd') between RECEFFDATE AND decode(RECENDDATE,'',sysdate,RECENDDATE)

2) You can try this without sparse lookup.
Use a normal lookup with key cols (other than start date & end date)
Select the option 'lookup returns multiple records'.
Then in a transfomer stage or filter stage, you can select only the record for which input date is between lookup start date and end date.

[vimali balakrishnan]

Thanks a lot, I tried the 2 option without sparse lookup and it worked.


Hi,
1) your date comparision is incorrect. You are comparing strings here instead of dates. ( I assume that incoming date value is a string )
to_date(ORCHESTRATE.STDATE,'yyyymmdd') between RECEFFDATE AND decode(RECENDDATE,'',sysdate,RECENDDATE)

2) You can try this without sparse lookup.
Use a normal lookup with key cols (other than start date & end date)
Select the option 'lookup returns multiple records'.
Then in a transfomer stage or filter stage, you can select only the record for which input date is between lookup start date and end date.[/quote]

[Pagadrai]

So, You can Resolve the issue.

[vimali balakrishnan]

So, You can Resolve the issue.

I have a new issue here. Since I gave multiple rows return option in the lookup, for a source record if there are more than one matching record in the lookup, then the output of the lookup is 2 identical rows(all columns from source) except for the other column fetched from the lookup.

Out of this if one records passes the filter stage satisfying the date condition, then the next identical record should not be paased to the reject if it does not pass the date criteria.

I'm currently using the reject link option of the filter stage where the nest identical record is passing to reject.

any option for this?

Thanks...