Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I have two table with following kind of structure
Table A:
Filed1
1
2
3
4
5
6
7
8
9
Table B:
Field 2
1
2
3
4
5
6
7
8
9
Now I want the two tables to join such that Table A fields are cross joined with Table B fields such that table B Field is greater than or equaly to Table 1 Fields
For example
New Table:
Field1 Field2
1 1
1 2
1 3
.
.
.
.
2 2
2 3
2 4
3 3
3 4
3 5
. .
.
.
.
7 7
7 8
7 9
.
.
.
9 9
I think the syntax should be something like
New_Table:
Load Field1
RESIDENT A;
JOIN (New_Table)
LOAD FIELD2
RESIDENT B
WHERE B>=A;
Can osmeone help please
-br
Arif
One way of getting the result you want is using the following script:
TempTable:
NoConcatenate LOAD Field1 Resident TableA;
Join (TempTable) LOAD Field2 Resident TableB;
NewTable:
NoConcatenate LOAD Field1, Field2 Resident TempTable Where Field1 >= Field2;
DROP Table TempTable;
Hope this helps.
Hello,
Thanks for the reply. Actually i would like to avoid the full outer join, because the tables i am using are very large (in millions of rows), and this could cause a very considerable memory problem. Is there any way that we could acheive the same result, without going to full outer join first and then selecting our desired rows. I mean in the same step when we are making the full cross or outer join, I would like to restrict it so that it does not go into a full outer join and the result is restricted to the condition that i give to it
br
Arif