Brain Teaser...Complex Scneario
Posted: Fri Apr 16, 2010 6:08 pm
Hi DSXians,
I came up with a Brain Teaser on Datastage. This problem is bugging us, hope the Datastage world will help me.
The Scenario is, my source data have a constrain on the Length of the String Field (10), if it crosses that limit, it is storing in a new row with the same primary keys by adding a Sequence number it. See the example below:
SOURCE_D KEY1 KEY2 SEQ_I String
2010-04-10 100 200 1 HELLO HOWRU
2010-04-10 100 200 2 I AM FINE
In my destination warehouse table, I am concatenating the String from SEQ_I 1 & 2. I need to see this data as
KEY1 KEY2 String START_D END_D
100 200 HELLO HOWRU I AM FINE 2010-04-10 9999-12-31
The above scenario is a simple scenario. Now I have scenario like below and all the data whatever I am mentioning is coming in a single source file and it is a History Load of 2 months at 1 time.
SOURCE_D KEY1 KEY2 SEQ_I String
2010-04-10 100 200 1 HELLO HOWRU
2010-04-10 100 200 2 I AM FINE
2010-04-12 100 200 3 WAT ABT YOU?
2010-04-14 100 200 4 ME FINE
2010-04-16 100 200 2 I AM NOT FINE
Now, I need my output should be show in warehouse as mentioned below.
KEY1 KEY2 String START_D END_D
100 200 HELLO HOWRU I AM FINE 2010-04-10 2010-04-11
100 200 HELLO HOWRU I AM FINE WAT ABT YOU? 2010-04-12 2010-04-13
100 200 HELLO HOWRU I AM FINE WAT ABT YOU? ME FINE 2010-04-14 2010-04-15
100 200 HELLO HOWRU I AM NOT FINE WAT ABT YOU? ME FINE 2010-04-16 9999-12-31
I tried many ways by using SQL, by using stage variables, I am not succeeded. Only thing you have to remember is everything is coming in one single file and we have to generate 4 output records as mentioned above.
Please do let me know, your inputs on this problem.
I came up with a Brain Teaser on Datastage. This problem is bugging us, hope the Datastage world will help me.
The Scenario is, my source data have a constrain on the Length of the String Field (10), if it crosses that limit, it is storing in a new row with the same primary keys by adding a Sequence number it. See the example below:
SOURCE_D KEY1 KEY2 SEQ_I String
2010-04-10 100 200 1 HELLO HOWRU
2010-04-10 100 200 2 I AM FINE
In my destination warehouse table, I am concatenating the String from SEQ_I 1 & 2. I need to see this data as
KEY1 KEY2 String START_D END_D
100 200 HELLO HOWRU I AM FINE 2010-04-10 9999-12-31
The above scenario is a simple scenario. Now I have scenario like below and all the data whatever I am mentioning is coming in a single source file and it is a History Load of 2 months at 1 time.
SOURCE_D KEY1 KEY2 SEQ_I String
2010-04-10 100 200 1 HELLO HOWRU
2010-04-10 100 200 2 I AM FINE
2010-04-12 100 200 3 WAT ABT YOU?
2010-04-14 100 200 4 ME FINE
2010-04-16 100 200 2 I AM NOT FINE
Now, I need my output should be show in warehouse as mentioned below.
KEY1 KEY2 String START_D END_D
100 200 HELLO HOWRU I AM FINE 2010-04-10 2010-04-11
100 200 HELLO HOWRU I AM FINE WAT ABT YOU? 2010-04-12 2010-04-13
100 200 HELLO HOWRU I AM FINE WAT ABT YOU? ME FINE 2010-04-14 2010-04-15
100 200 HELLO HOWRU I AM NOT FINE WAT ABT YOU? ME FINE 2010-04-16 9999-12-31
I tried many ways by using SQL, by using stage variables, I am not succeeded. Only thing you have to remember is everything is coming in one single file and we have to generate 4 output records as mentioned above.
Please do let me know, your inputs on this problem.