Page 1 of 1
Cartesian join/lookup
Posted: Wed Oct 06, 2004 9:58 am
by asarat
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.
Posted: Wed Oct 06, 2004 10:17 am
by KeithM
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.
Posted: Wed Oct 06, 2004 10:33 am
by asarat
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.
Posted: Wed Oct 06, 2004 10:55 am
by KeithM
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.
Posted: Wed Oct 06, 2004 12:14 pm
by asarat
Thanks Keith!
I had a lookup in mind all the time.But a simple join does the work.
Appreciate your help.
SARAT.
Posted: Wed Oct 06, 2004 12:36 pm
by KeithM
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.
Posted: Thu Oct 07, 2004 1:47 pm
by asarat
I am using OCI stage, so that is not an option.
This is quite simple anyway.
SARAT.