Skip to Content
0
Former Member
May 05, 2010 at 02:30 PM

Transformation routine referencing start routine variable

25 Views

Hello group,

I need to define a routine in my transformation for 0CALMONTH by looking up the data that is coming. Let me clarify my process.

I have a filter define on my DTP which will limit the retrieved record based on the date it is run; I need to get last 3 months of data. By that I mean that if the DTP runs in May, I need to filter the records to only get February, March and April. This filtering works fine.

Now what I have to do is to adjust the mapping of this data to my DSO within my transformation so the 0CALMONTH field gets the value of April so what I'd like to do is to perform something like:

START ROUTINE

SELECT MAX(MyDateField) INTO (a_variable) FROM SOURCE_PACKAGE.

Transformation Rule for 0CALMONTH

RESULT = a_variable.

I'd like to check for the maximum value within the start routine to avoid performing useless SQL Statement for every records because it could becomes a performance issue. Any help or better idea would be appreciated. Thanks.

John.