logic needed for following

[tring007]

How can I do the following in informatica?

I have a source table as follows
start and end dates are in mm/dd/yyyy

col1 col2 start_date end_date
----- ----- ----------- ---------
x y 01/01/2005 03/01/2005
z u 05/01/2003 09/01/2003

target should be as follows

col1 col2 month_year
x y 012005
x y 022005
x y 032005
z u 052003
z u 062003
z u 072003
z u 082003
z u 092003

 

[blom0344]

Personally, I think the period as defined year+month is a better object for a time period,
as it is build as

year(date)*100+month(date)

Anyway I use that all the time ...

Here's a solution (with my period object)

Use a calender file that holds objects like:

year month period

2005 1 200501
2005 2 200502
.... .. ......
2010 12 201012

Use a SQL override in the SQ of the source and the timetable:

Select col1,col2,period from  source-table,time-table where period between (year(start_date)*100+month(start_date)) and  (year(end_date)*100+month(end_date))

Now if you want your own object back , then recreate it as:

substr(to_char(period),5,2)||substr(to_char(period),1,4)

furtheron in the mapping

Ties Blom
Information analyst