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.
Cartesian join/lookup
Moderators: chulett, rschirm, roy
Based on your example your just doing a simple join between two table.
You can do that in any stage including OCI, ODBC, ect.
Code: Select all
Select *
From Table1 T1
,Table2 T2
Where T1.C1 = T2.C1
And T1.C2 = T2.C2
Keith
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.
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.
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.
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.
Keith
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.
Keith