cross join with condition

[#1]

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

[nagaiank]

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.

[#3]

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