Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Combining data from two different tables

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Combining data from two different tables
 Login/Join
 
<MikeBres>
posted
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
 
Report This Post
Platinum Member
posted Hide Post
Since 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 ;
 
Posts: 226 | Registered: June 08, 2003Report This Post
Platinum Member
posted Hide Post
Correction.. take off the word TABLE in the second request..
see the doc for the rules on MORE.
 
Posts: 226 | Registered: June 08, 2003Report This Post
<MikeBres>
posted
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
 
Report This Post
Platinum Member
posted Hide Post
Mike,

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
 
Posts: 177 | Location: Calgary, Alberta, Canada | Registered: April 25, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Combining data from two different tables

Copyright © 1996-2020 Information Builders