Hi,

Please let me know the way to get the following result.

Table 1
--------
col1 col2
1 a
b
c
2 a
b
c
d

Table 2
--------
col2 col3
a aaaa
b bbbb
c cccc
d dddd
e eeee
f ffff
g gggg
h hhhh

Output
-------
col1 col2 col3
1 a aaaa
b bbbb
c cccc
d dddd
e eeee
f ffff
g gggg
h hhhh
2 a aaaa
b bbbb
c cccc
d dddd
e eeee
f ffff
g gggg
h hhhh

Thanks

This message has been edited. Last edited by: Kerry,

You mean:

Table 1
--------
col1  col2
1      a
1      b
1      c
2      a
2      b
2      c
2      d

Table 2
--------
col2  col3
a     aaaa
b     bbbb
c     cccc
d     dddd
e     eeee
f     ffff
g     gggg
h     hhhh

Output
-------
col1 col2 col3
1    a    aaaa
     b    bbbb
     c    cccc
     d    dddd
     e    eeee
     f    ffff
     g    gggg
     h    hhhh
2    a    aaaa
     b    bbbb
     c    cccc
     d    dddd
     e    eeee
     f    ffff
     g    gggg
     h    hhhh

It's not clear how this output relates to these inputs. How are values f,g,h of col2 in Table 2 associated with either value (1,2) of col1 in Table 1? Do you just want to replicate all columns and rows of table2 for each distinct value of col1 in Table 1?

YES, I want to replicate all columns and rows of table2 for each distinct value of col1 in Table1.

So you want a Cartesian product. One method is a "Conditional" join without conditions:

JOIN CLEAR *
SET HOLDLIST=PRINTONLY

-* set up two data sources without common fields
TABLE FILE CAR
BY COUNTRY
WHERE COUNTRY CONTAINS 'Y';
ON TABLE HOLD AS TABLE1 FORMAT FOCUS
END

TABLE FILE CAR
BY CAR BY MODEL BY RCOST 
WHERE COUNTRY OMITS 'Y';
ON TABLE HOLD AS TABLE2 FORMAT FOCUS
END

-* join and report
JOIN FILE TABLE1 AT E01 TO MULTIPLE FILE TABLE2 AT E01
END

TABLE FILE TABLE1
PRINT SEG.CAR
BY COUNTRY
ON TABLE SET ONLINE-FMT STANDARD
END  

Output:

 
NUMBER OF RECORDS IN TABLE=        2  LINES=      2 
NUMBER OF RECORDS IN TABLE=        7  LINES=      7 
NUMBER OF RECORDS IN TABLE=       14  LINES=     14

  
  
  COUNTRY     CAR               MODEL                     RETAIL_COST           
  -------     ---               -----                     -----------           
  ITALY       DATSUN            B210 2 DOOR AUTO                3,139
              JAGUAR            V12XKE AUTO                     8,878
              JAGUAR            XJ12L AUTO                     13,491
              JENSEN            INTERCEPTOR III                17,850
              PEUGEOT           504 4 DOOR                      5,610
              TOYOTA            COROLLA 4 DOOR DIX AUTO         3,339
              TRIUMPH           TR7                             5,100
  W GERMANY   DATSUN            B210 2 DOOR AUTO                3,139
              JAGUAR            V12XKE AUTO                     8,878
              JAGUAR            XJ12L AUTO                     13,491
              JENSEN            INTERCEPTOR III                17,850
              PEUGEOT           504 4 DOOR                      5,610
              TOYOTA            COROLLA 4 DOOR DIX AUTO         3,339
              TRIUMPH           TR7                             5,100

This is not working for me.

In your case, COUNTRY is in other table and doing the sort by COUNTRY will work but my case just CAR is in different table but after we join, I want to do the sort by COUNTRY.

something like this

TABLE FILE CAR
BY CAR
WHERE COUNTRY CONTAINS 'Y';
ON TABLE HOLD AS TABLE1 FORMAT FOCUS
END

TABLE FILE CAR
BY COUNTRY BY MODEL BY RCOST
WHERE COUNTRY OMITS 'Y';
ON TABLE HOLD AS TABLE2 FORMAT FOCUS
END

-* join and report
JOIN FILE TABLE1 AT E01 TO MULTIPLE FILE TABLE2 AT E01
END

TABLE FILE TABLE1
PRINT CAR MODEL RCOST
BY COUNTRY
ON TABLE SET ONLINE-FMT STANDARD
END

quote:

This is not working for me.

quote:

... my case just CAR is in different table but after we join, I want to do the sort by COUNTRY.

You could have provided all those particular details about your data from the very beginning when you posted your question, therefore reducing a lot of guesswork.

I honestly don't see how j.gross could have come up with such a good example given so limited specs ... perhaps I'm just becoming lazy

Anyway, it seems as if you already managed to envision a solution based on your last piece of code, didn't you?