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 = PASSThis message has been edited. Last edited by: Kerry,
WebFOCUS 7.6.4 Windows XP Excel, PDF, HTML, txt,,,,,etc
March 23, 2010, 11:22 AM
Kathy P
Match logic will bring everything back regardless if there or not in the host file. AFTER MATCH HOLD OLD-OR-NEW.
Kathy Phillips Web FOCUS 8.2.05.14, 8.1.05, 8.08, 8.0.7, 8.0.5,8.0.2m, 7.6.10,7.7.03 Windows
March 23, 2010, 12:27 PM
Jean - Wu
I tried MATCH but only got result like: 1 1 1 2 2 2 5 5 5
did not catch 6 or 7
WebFOCUS 7.6.4 Windows XP Excel, PDF, HTML, txt,,,,,etc
March 23, 2010, 01:21 PM
Francis Mariani
-* TABLEA.fex
-SET &ECHO='ALL';
SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA
-RUN
FILEDEF TABLEA DISK tablea.txt
-RUN
-WRITE TABLEA 1 APPLE
-WRITE TABLEA 1 BANANA
-WRITE TABLEA 1 COCONUT
-WRITE TABLEA 2 ORANGE
-WRITE TABLEA 2 PINEAPPLE
-WRITE TABLEA 2 MANGO
-WRITE TABLEA 5 KIWI
-WRITE TABLEA 5 STRAWBERRY
-WRITE TABLEA 5 PEACH
FILEDEF MASTER DISK temp/tablea.mas
-RUN
-WRITE MASTER FILENAME=TABLEB, SUFFIX=FIX, $
-WRITE MASTER SEGNAME=TABLEB, $
-WRITE MASTER FIELDNAME=KEY1 , ALIAS=KEY1 , USAGE=A01, ACTUAL=A01, $
-WRITE MASTER FIELDNAME= , ALIAS=FIL1 , USAGE=A01, ACTUAL=A01, $
-WRITE MASTER FIELDNAME=FRUIT, ALIAS=FRUIT, USAGE=A10, ACTUAL=A10, $
-RUN
FILEDEF TABLEB DISK tableb.txt
-RUN
-WRITE TABLEB 1 TANGERINE
-WRITE TABLEB 2 MELON
-WRITE TABLEB 5 APRICOT
-WRITE TABLEB 6 GRAPE
-WRITE TABLEB 7 CANTELOUPE
FILEDEF MASTER DISK temp/tableb.mas
-RUN
-WRITE MASTER FILENAME=TABLEB, SUFFIX=FIX, $
-WRITE MASTER SEGNAME=TABLEB, $
-WRITE MASTER FIELDNAME=KEY1 , ALIAS=KEY1 , USAGE=A01, ACTUAL=A01, $
-WRITE MASTER FIELDNAME= , ALIAS=FIL1 , USAGE=A01, ACTUAL=A01, $
-WRITE MASTER FIELDNAME=FRUIT, ALIAS=FRUIT, USAGE=A10, ACTUAL=A10, $
-RUN
MATCH FILE TABLEA
PRINT
FIL1 NOPRINT
BY KEY1
BY FRUIT
RUN
FILE TABLEB
PRINT
FIL1 NOPRINT
BY KEY1
BY FRUIT
AFTER MATCH HOLD AS H001 OLD-OR-NEW
END
-RUN
TABLE FILE H001
PRINT
KEY1
FRUIT
END
-RUN
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
March 23, 2010, 01:35 PM
fatboyjim
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
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.
Kathy Phillips Web FOCUS 8.2.05.14, 8.1.05, 8.08, 8.0.7, 8.0.5,8.0.2m, 7.6.10,7.7.03 Windows
March 23, 2010, 09:03 PM
Jean - Wu
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.
March 24, 2010, 03:36 AM
Tony A
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
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
March 24, 2010, 12:48 PM
Jean - Wu
(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.