create relation

dataservices job design
1

Hi,

As you can see I’m new in the DI-world. I’m facing this problem:

I select a record from table A in database A, the record consist a startyear end an endyear. This record gets insterted in table B in database B. Soo far so good :smiley: …

For each year, between startyear and endyear I have to insert in table C in database B a record, how can i achieve this :crazy_face:

Thanks in advance for your time and support
Peter

piekepotloed :netherlands: (BOB member since 2009-04-09)

2

It’s one of the rare situations in which you need to switch from a “column based” approach to a “row based” one, and this something that DI is not so good at. But this only at first glance, as DI is wonderful, and can achieve this too! :mrsbob:

  1. While loading start and end year, put them into a staging table described this way:

ID, START_YR, END_YR

and store each record giving sequential IDs

  1. browse the table with a loop component containing a script with a sql statement that assigns the values to a set of variables (see the following):

&startYr = sql(‘datastore’, 'SELECT START_YR FROM WHERE ID = ’ || &i);

&endYr = sql(‘datastore’, 'SELECT END_YR FROM WHERE ID = ’ || &i);

&i=&i+1;

(of course, &i is initialized to 1 outside the loop)

  1. insert a line for each year with an inner loop starting from &startYr with an increment of 1 ending to &endYr:

sql(‘datastore’, ‘insert into

(Year) VALUES (’ || &year || ‘)’);

&year=&year+1;

Have fun
Claudio

CLS69 :it: (BOB member since 2009-06-11)

3 

Source ----> Query1 -----> Target1
                   \
                     \
          Row_Gen--> Query2 ----> target2

I would join the created record with a Row_Generation transform that creates 500 rows. The join condition is

startyear >= DI_ROW_ID+1900 and endyear <= DI_ROW_ID+1900

As an example, I have an input row

X, 1999, 2008

and due to above join you would output

X, 1999, 2008, 1999
X, 1999, 2008, 2000
X, 1999, 2008, 2001
X, 1999, 2008, 2002
X, 1999, 2008, 2003
X, 1999, 2008, 2004
X, 1999, 2008, 2005
X, 1999, 2008, 2006
X, 1999, 2008, 2007
X, 1999, 2008, 2008

Don’t worry about performance, this in-memory join is capable of processing a million of rows per second, much more than your target database.

Werner Daehn :de: (BOB member since 2004-12-17)