Merge Data Source

[mchatrvd]

I have two data sources, one coming from sequential file having data with start and end date. I will like to join it with another data source having all possible dates and then merge it into one output which will give me the data in sequential file by each date. Any help will be appreciated to accompolish the above.

Thanks,
Manish

[ray.wurlod]

Welcome aboard. :D

Can you be more precise about what you mean by "merge"? Perhaps describe the expected format of rows in your result set.

[mchatrvd]

I have a sequential file which has employee, department, start date,end date and have another source which holds all possible dates. I want to combine these two soruces and get my output as employee, department,date in DataStage, based on criteria date> = start date and date <= end date. I appreciate your help and let me know if you need any ifnormation.

Thanks,
Manish

[ray.wurlod]

So you want one row in the output for each date between start date and end date for each employee? Is that correct?

If so you need to effect a user-defined range query against the DATES table. This multi-row return lookup can only be accomplished with an ODBC stage, or a UV stage referring to a hashed file or UV table.

You would use a user-defined query of the form

Code: Select all

SELECT THE_DATE FROM DATES WHERE THE_DATE BETWEEN ? AND ?

and you would designate StartDate and EndDate as Key on the reference input link (and provide their values via the reference key expression field in the Transformer stage).