I want the result to have everything from two tables. I tied to use JOIN TO ALL but it did not bring the result.
For example:
TABLE A
KEY
1
1
1
2
2
2
5
5
5


TABLE B
KEY
1
2
5
6
7

Is it possible to have result from both:
1
1
1
2
2
2
5
5
5
6
6
6
7
7
7

I already have:
SET ALL = ON
SET ALL = PASS

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

Match logic will bring everything back regardless if there or not in the host file.
AFTER MATCH HOLD OLD-OR-NEW.

I tried MATCH but only got result like:
1
1
1
2
2
2
5
5
5

did not catch 6 or 7

Hi,

You can try joining like this:

-* Extract first table
TABLE FILE EMPDATA
BY PIN
ON TABLE HOLD AS EMPHOLD
END
-*
-* Extract 2nd table
DEFINE FILE EMPLOYEE
PIN/A9=EMP_ID;
END
TABLE FILE EMPLOYEE
BY PIN
ON TABLE HOLD AS EMPHOLD2
END
-*
-* Join them up
MATCH FILE EMPHOLD
  BY PIN
RUN
FILE EMPHOLD2
  BY PIN
AFTER MATCH HOLD OLD-OR-NEW
END
TABLE FILE HOLD
PRINT
     PIN
END

See if that works.

Best Regards,

Jimmy Pang

Can you please show us your code? Everyone here is using AFTER MATCH HOLD OLD-OR-NEW. You WILL get all the records in one file. Perhaps you are tabling it in a way that prevents you from seeing all the records? It is hard to know without seeing your code. Thank you.

Part of my codes are as followings:

..
TABLE FILE MASTERHOLD
SUM
COL_1/D15CBN
COL_2/D15CBN
COL_3/D15CBN
COL_4/D15CBN
COL_5/D15CBN
COL_6/D15CBN
COL_7/D15CBN
COL_8/D15CBN
COMPUTE COL_9/D15CBN = ( COL_2 + COL_3 + COL_4 + COL_5 + COL_6 + COL_7 + COL_8 ) ;
COMPUTE PRE_COL_9/D15CBN = ( COL_2 + COL_3 + COL_4 + COL_5 + COL_6 + COL_7 + COL_8 ) ;
BY FUND_CODE
BY ROW_NUM
WHERE REPORT_ID EQ 'R-10';
WHERE FUND_CODE FROM '10001' TO '10999';
ON TABLE HOLD AS PHOLD FORMAT ALPHA
END



TABLE FILE MASTERHOLD
SUM
COL_1/D15CBN
COL_2/D15CBN
COL_3/D15CBN
COL_4/D15CBN
COL_5/D15CBN
COL_6/D15CBN
COL_7/D15CBN
COL_8/D15CBN
COMPUTE COL_9/D15CBN = ( COL_2 + COL_3 + COL_4 + COL_5 + COL_6 + COL_7 + COL_8 ) ;
COMPUTE PRE_COL_9/D15CBN = ( PRE_COL_2 + PRE_COL_3 + PRE_COL_4 + PRE_COL_5 + PRE_COL_6 + PRE_COL_7 + PRE_COL_8 ) ;
BY FUND_CODE
BY ROW_NUM
WHERE REPORT_ID EQ 'R-10';
WHERE FUND_CODE FROM '10001' TO '10999';
ON TABLE HOLD AS CHOLD FORMAT ALPHA
END


TABLE FILE PHOLD
SUM
COL_1
COL_2
COL_3
COL_4
COL_5
COL_6
COL_7
COL_8
COL_9
PRE_COL_9
BY FUND_CODE
BY ROW_NUM
ON TABLE HOLD AS FINAL FORMAT ALPHA
MORE
FILE CHOLD
END

MATCH FILE FINAL
PRINT
COL_1
COL_2
COL_3
COL_4
COL_5
COL_6
COL_7
COL_8
COL_9
PRE_COL_9
BY FUND_CODE
BY ROW_NUM
RUN
FILE FD_INST_VW
PRINT
FUND_CODE
CITY
STATE
BY FUND_CODE
AFTER MATCH HOLD OLD-OR-NEW
END


TABLE FILE HOLD
PRINT
FUND_CODE
ROW_NUM
COL_1
COL_2
COL_3
COL_4
COL_5
COL_6
COL_7
COL_8
COL_9
PRE_COL_9
CITY
STATE
BY FUND_CODE NOPRINT
BY ROW_NUM NOPRINT
ON TABLE PCHOLD FORMAT EXL2K
END
-GOTO DONE

However, the result is like:

FUND ROW COL_1 COL_2 STATE CITY
100 1 $100 $200 TX DALLAS
100 2 $100 $200
100 3 $100 $200
200 0 0 CA IRVINE
300 0 0 FL MIAMI


But I want the result to be like:

FUND ROW COL_1 COL_2 STATE CITY
100 1 $100 $200 TX DALLAS
100 2 $100 $200 TX DALLAS
100 3 $100 $200 TX DALLAS
200 1 0 0 CA IRVINE
200 2 0 0 CA IRVINE
200 3 0 0 CA IRVINE
300 1 0 0 FL MIAMI
300 2 0 0 FL MIAMI
300 3 0 0 FL MIAMI

Is it possible to get it done like that?

Thanks for everyone's input.

To get the state and city to repeat, change the PRINT in the second part of your MATCH FILE to SUM.

For the next part of your requirement, you will need to force the values of ROW_NUM that you want. So, in the TABLE FILE PHOLD, add a line after BY ROW_NUM to specify the ROWS that you need to have. e.g. ROWS 1 OVER 2 OVER 3

T

(1) change to SUM in Match - it works.
(2) change BY ROW to FOR ROW in PHOLD - it did not work well. However, I add DEFINE FILE afterwards to bring every ROW_NUM. Now everything works the way I want it.

Thank you very much.