Multiple row generation for a single row source

[noname001]

Hi Guys,

If my souce is having one row of data and for the source row if i have multiple rows in the lookup satisfying the criteria that i define, is it possible to create multiple rows in the target for each lookup ??

For Example.

Source Table

S.NO Name Parent
1 AB01 CD01
2 AB02 CD02


Lookup Table

S.NO Name Parent
1 CD01 5
2 CD01 5
3 CD02 7


I want my result set to be

S.NO Name Parent
1 AB01 5
2 AB01 5
3 AB02 7


please provide me your inputs.
Answers appreicated.

[anandsiva]

Use Join stage instead of look up...

[ray.wurlod]

No such animal in a server job.

If your secondary reference set is ODBC-accessible, then multiple rows per lookup can be returned.

If both inputs are sequential files (or can be made sequential files) then you could perform a join in a Merge stage.

[sajal.jain]

use join

or

in the look up stage --> goto constraints and select the link name in "Multiple rows return from link" drop box.

[noname001]

use join

or

in the look up stage --> goto constraints and select the link name in "Multiple rows return from link" drop box.

Do we have a constraints tab or sumthing in SERVER jobs ??

[ray.wurlod]

Nothing of the kind sajal.jain is suggesting - seems to be a locked-in parallel job mindset going on there.

[chulett]

For Server, but the ODBC and UV stages support a lookup multi-row result set and it is a property of the reference link itself.