May 13, 2005, 09:26 PM
<MikeBres>Combining data from two different tables
Using the HOLD command I have been able to combine two different queries into one file. This was done with the same TABLE.
Today I tried to do the same thing only with two different tables. I only got the results from the last query in my hold file.
What is the proper way to combine the results from two different tables?
Thanks
Mike
May 14, 2005, 10:51 PM
GCohenSince both results go to the same file I assume the fields in the two reports are the same.
So I would use the MORE command..eg.
TABLE FILE ONE
SUM X Y Z BY A
WHERE expression ;
MORE
TABLE FILE TWO
WHERE expression ;
ON TABLE HOLD AS RESULT
END
The field names in the second file, if not the same as the first file, should then be Defined to be the same, ie Define file two X= TWO.XX ;
May 14, 2005, 10:52 PM
GCohenCorrection.. take off the word TABLE in the second request..
see the doc for the rules on MORE.
May 16, 2005, 07:28 PM
<MikeBres>Thank you for the information. It looked very promissing when I read about it in the manual. Unfortunetly, my system doesn't recognize the MORE command. If I put it on a line by itself I get:
UNKNOWN FOCUS COMMAND FILE
BYPASSING TO END OF COMMAND
If I put it on the same line with the FILE command I get:
UNKNOWN FOCUS COMMAND MORE
BYPASSING TO END OF COMMAND
Any other ideas?
Mike
May 16, 2005, 09:28 PM
k.laneMike,
The MORE method that Gerry mentioned above is called universal concatenation. It is a very handy tool. But, as already mentioned, it only works if both files contain the same columns (and formats).
The syntax to use would be:
TABLE FILE ONE
SUM X Y Z BY A
WHERE expression ;
ON TABLE HOLD AS RESULT
MORE
TABLE FILE TWO
WHERE expression ;
END
Just make sure to check the layouts of file ONE and TWO by issuing:
?FF ONE
?FF TWO
If they match, you can use universal concatenation.
If they don't match, a JOIN will certainly help you.
But, it all depends on what you're trying to do with the single hold file.
Another method you can try is to create files that contain the data from both hold files and use (APPEND to put them in the same file. Something like the following will work for that:
FILEDEF ONE DISK \\IBIAPPS\HOLDTEMP\ONE.FTM
TABLE FILE TTIMEDIM
SUM MONTH_TOTAL_DAYS
BY REPORT_YEAR
BY REPORT_MONTH
WHERE REPORT_YEAR EQ 2002
WHERE REPORT_MONTH NE 00 OR 13
ON TABLE HOLD AS ONE FORMAT ALPHA
END
FILEDEF TWO DISK \\IBIAPPS\HOLDTEMP\ONE.FTM (APPEND
TABLE FILE TTIMEDIM
SUM MONTH_TOTAL_DAYS
BY REPORT_YEAR
BY REPORT_MONTH
WHERE REPORT_YEAR EQ 2003
WHERE REPORT_MONTH NE 00 OR 13
ON TABLE HOLD AS TWO FORMAT ALPHA
END
The results of this methold would yield:
2002 1 31
2002 2 28
2002 3 31
2002 4 30
2002 5 31
2002 6 30
2002 7 31
2002 8 31
2002 9 30
2002 10 31
2002 11 30
2002 12 31
2003 1 31
2003 2 28
2003 3 31
2003 4 30
2003 5 31
2003 6 30
2003 7 31
2003 8 31
2003 9 30
2003 10 31
2003 11 30
2003 12 31
As I said before, it really depends on what you're trying to accomplish.
Ken