Copy one row to multiple rows

vskr72 · Post by **vskr72** » Thu Jan 12, 2006 2:51 pm

Hi,

How you copy one row to multiple rows in data stage?

Thanks.

Kumar

kcbland · Post by **kcbland** » Thu Jan 12, 2006 2:54 pm

What do you mean by "copy"?

vskr72 · Post by **vskr72** » Thu Jan 12, 2006 2:59 pm

What I meant was is there way to copy 1 row from source and duplicate it into many rows in target.

Kumar

[quote="kcbland"]What do you mean by "copy"?[/quote]

kcbland · Post by **kcbland** » Thu Jan 12, 2006 3:04 pm

Do you mean update multiple target rows with values derived from one input row? Your question is poorly worded.

In SQL, you'd use a wildcard query. Your question, however, doesn't hint at which methods you prefer.

shawn_ramsey · Post by **shawn_ramsey** » Thu Jan 12, 2006 3:04 pm

vskr72 wrote:What I meant was is there way to copy 1 row from source and duplicate it into many rows in target.

Kumar

kcbland wrote:What do you mean by "copy"?

Kumar,

Are you attempting to take one row with multiple columns and put each column in a separate row in the output? If so then you need to look at the Pivot stage.

vskr72 · Post by **vskr72** » Thu Jan 12, 2006 3:11 pm

What I need is Record1 in source should be duplicated to 10 records ( all Record 1) in Target. Hope it is clear on what I need. Thank you.

Kumar

[quote="shawn_ramsey"][quote="vskr72"]What I meant was is there way to copy 1 row from source and duplicate it into many rows in target.

Kumar

[quote="kcbland"]What do you mean by "copy"?[/quote][/quote]
Kumar,

Are you attempting to take one row with multiple columns and put each column in a separate row in the output? If so then you need to look at the Pivot stage.[/quote]

kwwilliams · Post by **kwwilliams** » Thu Jan 12, 2006 3:28 pm

How do you identify the 10 records in the target? If you have the key available you could just do an update of your target table on your key.

Or you could place the value you want to be duplicated in a hash file and then have your target table pass through a transformer and perform a lookup.

Without more information, I can only answer in generalities.

shawn_ramsey · Post by **shawn_ramsey** » Thu Jan 12, 2006 3:37 pm

vskr72 wrote:What I need is Record1 in source should be duplicated to 10 records ( all Record 1) in Target. Hope it is clear on what I need. Thank you.

Kumar

In 10 separate tables?

I_Server_Whale · Post by **I_Server_Whale** » Thu Jan 12, 2006 4:23 pm

Kumar,

If you mean to say that you have to create 10 replications of 1 incoming record. Then first make sure that you have no keys defined in the target metadata, because you are trying to replicate here. Sorting the source data would be nice too but not necessary.

And as far as the replications are concerned, build a custom routine that takes in all the fields from the source file/table as input arguments. Concatenate all these fields together and then concatenate with a EOL character (char(10):char(13)). Repeat this code 10 times assuming you are replicating 1:10.

Call this routine into a Stage Variable and then load the target.

I think this will work fine if implemented correctly. Please let me know if you have any questions.

Thanks,
Naveen.

ray.wurlod · Post by **ray.wurlod** » Thu Jan 12, 2006 5:32 pm

Transformer stage with N output links ought to do it.

vskr72 · Post by **vskr72** » Fri Jan 13, 2006 8:06 am

Thank you Ray and Naveen. It worked.

gateleys · Post by **gateleys** » Fri Jan 13, 2006 8:47 am

I have a simple job which is as follows-
1. ODBC source reads from SQL Server.
2. Necessary logic perfomed in the Transformer which also gets a reference input via a Hash file.
3. Result passed to a sequential file.

Code: Select all

ODBC ==> Transformer ==> Seq File
                 ^
                ||
            Hash File

The resulting Seq File has over 100,000 rows. Now, I need to add a date field to the result. The date (lets call it some_date) comes from Oracle. The same date should be added to all the rows; no checking of any kind is required. How can this be done efficiently?

kiran_418 · Post by **kiran_418** » Fri Jan 13, 2006 8:59 am

I had a similar situation where i had to send 1 incoming row to 7 different target oracle tables based on day of the week. If its monday then hit monday table.
In that situation i used 7 OCI stages as output. only 1 input record is being passed. I put a constraint over there. Here if you dont have a constraint then 1 single record will hit all the 7 outputs.

gateleys · Post by **gateleys** » Fri Jan 13, 2006 10:05 am

That's what I did too, just used an OCI stage to feed the row via transformer.

Code: Select all

              OCI
               ||
               V
ODBC ==> Transformer ==> Seq File 
                 ^ 
                || 
            Hash File

It easily solves my problem, where I select the date where the key = 1 and set the key value of the OCI to '1' in the transformer. But the purpose of my question was whether it is a good idea to use reference link to feed data. Because, in my particular case, there is no need of the key value of the OCI stage.

ray.wurlod · Post by **ray.wurlod** » Fri Jan 13, 2006 3:14 pm

You can't perform any kind of lookup without a reference key.

DSXchange