DSXchange

Hello,

I need informations on this case :
I have two ORACLE tables

TABLE 1 : 3 rows
---------
field1 / field2
X / W
X / Y
X / Z

TABLE 2 : 2 rows
---------
field1 / field2
X / A
X / B


I would like to have as result
RESULT TABLE : 6 rows
---------
field1 / field2 / field3
X / A / W
X / A / Y
X / A / Z
X / B / W
X / B / Y
X / B / Z

For maintenance reason, my direction doesn't allow me to do directly the join in one source stage. They want an explicit design
TABLE1 stage ->
TABLE2 stage -> TRANSFORMER -> RESULT TABLE

I don't know how to "transform a reference into a full join"

In my tries, I had only 2 or 3 rows in result table

Thanks for help

Hi, I'm sure there are several ways to the solution. If ODBC connections are allowed in your environment and architecture, you could use an ODBC stage. An ODBC stage can be set to "Reference link with multi row result set". The same can be done with an Universe stage, but never used that before.

Joshi

JP wrote:Hello,

I need informations on this case :
I have two ORACLE tables

TABLE 1 : 3 rows
---------
field1 / field2
X / W
X / Y
X / Z

TABLE 2 : 2 rows
---------
field1 / field2
X / A
X / B


I would like to have as result
RESULT TABLE : 6 rows
---------
field1 / field2 / field3
X / A / W
X / A / Y
X / A / Z
X / B / W
X / B / Y
X / B / Z

For maintenance reason, my direction doesn't allow me to do directly the join in one source stage. They want an explicit design
TABLE1 stage ->
TABLE2 stage -> TRANSFORMER -> RESULT TABLE

I don't know how to "transform a reference into a full join"

In my tries, I had only 2 or 3 rows in result table

Thanks for help

JP wrote:For maintenance reason, my direction doesn't allow me to do directly the join in one source stage. They want an explicit design

For 'maintenance reason'? You would typically only go with your 'explicit' design when you had no other choice, but with both tables in the same database, I really don't see any good reason not to join them directly in the source sql. [shrug]

You'd have to load the 'looked up' table into an account-based hashed file (or create a VOC record for a pathed hashed file) then you could read it with a UV stage.

Thanks a lot Joshi.
I try it with ODBC and it works fine ! I never used ODBC stage before. Does it exist performance issue for large number of data or is it quite similar as Oracle stage ?

Chulett, I agree you but I don't have the choice . They require something graphical in case of many joins to do.

Thanks again.

Those damn "they"s again and their silly requirements. Ah, well.

A direct database lookup is never really the most 'performant' option. ODBC used to always be a poorer choice to a 'native' connection but I don't know if that's the case any more. It can also depend on if you are using a 'wired' driver or not...