Cartesian join/lookup

asarat · Post by **asarat** » Wed Oct 06, 2004 9:58 am

Hi,
I have a situation where I have 2 tables and I need a kind of cross join (not a full cross join). I am giving an example of my problem here.Please let me know your suggestions

input:

Table1:
C1 C2 C3
C 04 01
C 04 02
C 05 03
C 05 04

Table2:
C1 C2 C3
C 04 h
C 04 p
C 05 q
C 05 r

My required output is:

t1C1 t1C2 t1C3 t2C3
C 04 01 h
C 04 01 p
C 04 02 h
C 04 02 p
C 05 03 q
C 05 03 r
C 05 04 q
C 05 04 r

Please tell me how I can do this.

Thanks in advance for your help.

SARAT.

KeithM · Post by **KeithM** » Wed Oct 06, 2004 10:17 am

Based on your example your just doing a simple join between two table.

Code: Select all

Select *
From Table1 T1
        ,Table2 T2
Where T1.C1 = T2.C1
And T1.C2 = T2.C2

You can do that in any stage including OCI, ODBC, ect.

asarat · Post by **asarat** » Wed Oct 06, 2004 10:33 am

Hi Keith,

When I do
Select *
From Table1 T1
,Table2 T2
Where T1.C1 = T2.C1
And T1.C2 = T2.C2
I will loose some data. The output I get when I do this is

C 04 01 h
C 04 02 h
C 05 03 q
C 05 04 q

which is not the same as

C 04 01 h
C 04 01 p
C 04 02 h
C 04 02 p
C 05 03 q
C 05 03 r
C 05 04 q
C 05 04 r

Any comments/suggestions?

SARAT.

KeithM · Post by **KeithM** » Wed Oct 06, 2004 10:55 am

If that is the result you're get then something is wrong with your join. The results that you are displaying are accurate if you're reading from table1 and doing a lookup to table2. They are not accurate if you are joining the tables as I suggested. The join will pull all combinations from your two tables where C1 and C2 match which is the result you specified.

I would suggest posting your actual SQL from datastage if you are still having a problem. Maybe something else is going on which is masking the real problem.

asarat · Post by **asarat** » Wed Oct 06, 2004 12:14 pm

Thanks Keith!
I had a lookup in mind all the time.But a simple join does the work.

Appreciate your help.

SARAT.

KeithM · Post by **KeithM** » Wed Oct 06, 2004 12:36 pm

If you really want to use the lookup you have the option to return multiple rows if you're looking up to an ODBC stage. In the Input properties of the transformation stage there is a check box titled 'Reference link with multi row result set.' Check this and your lookup will return multiple rows if found and this should give you the same results. However, this option will not be as efficient as joining the two tables together.

asarat · Post by **asarat** » Thu Oct 07, 2004 1:47 pm

I am using OCI stage, so that is not an option.
This is quite simple anyway.

SARAT.