Is there a limit to the number of tables that WebFocus will join together? I am using the Dev Studion GUI and one icon I see is a new join and one is a add file. What is the purpose of doing a new join vs add file?

Also, when I have several tables joined together, and in my report I want to do a sort by several fields that are in several different tables, I keep getting a "ALL VERB OBJECTS MUST BE IN THE SAME PATH AS THEIR SORT FIELDS" error. Does this have to do with how I have my tables joined? Do you join the tables that have your sort fields linearly (table1 --> table2-->table3 and all other tables joined to table3) or in a tree structure (table1 joined to all other tables)?

Malinda

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

Where to start? Each of your questions is a pretty big subject in itself.

There is a limit on the noumber of tables that can be joined together. It use to be 16, but I don't know if that has increased over the years. Frankly, in 20 years of using FOCUS, I have never had to use more than 6-8 in a single join. I find it much easier an less confusing to join a few together, hold the filtered data set, and then do additional joins, if necessary. I'm sure others have differing opinions on the susbject.

Adding a new join means that you are beginning with a new host file at the top of your join. Adding a file means that you want to join an additional cross-reference (or child) file to your existing host-file or existing join structure. For the most part, you'll just stick with the "add a new file." You can only specify a single table in the table request anyway, which must must a single host-file in the join. Subsequent joins I usually place before the table request that will use them.

If you're getting the "all verb objects..." error, then you should definitely take a look at the way you're joining your tables. If you're sorting by fields in multiple tables, then that usually indicates that there is some sort of relationship between the sort fields, and they should be joined in som linear fashion (table1 to table 2, table 2 to table 3, etc.) so that all sort fields and displayed fields (verb objects) are in the same path. If you've joined them in the tree structure you describe, (table 1 to table 2, table 1 to table 3, etc.) that means that the fields in table 2 are unrelated to those in table 3 except through the parent segement. It takes a little study and a little experience to understand the concept better. I would refer you to Chapter 15 of the "Creating Reports with the WebFOCUS Language" manual - all about joining data sources.

Thanks for the reply Darin. I know joins are a huge can of worms! What you stated is pretty much what I have been able to derive from my own poking around in WebFoucs and it is nice to know that I am on the right path.

Would you agree then that the fields that you are going to use in defines and sorts, need to be joined in a linear fashion and then all other fields that are details could be joined in a tree structure to your last linearly joined table? Does this make sense? Just trying to come up with some "best practices" notes that I can write down and pass on as other ppl here start to develop reports (whenever that is )

Malinda

See: Joining Data Sources in the Creating Reports With WebFOCUS Language (DN4500804.1209) which can be found at Information Builders Technical Documentation Library.

Great information, thanks Darin and Neftali

quote:

Adding a new join means that you are beginning with a new host file at the top of your join. Adding a file means that you want to join an additional cross-reference (or child) file to your existing host-file or existing join structure. For the most part, you'll just stick with the "add a new file." You can only specify a single table in the table request anyway, which must must a single host-file in the join. Subsequent joins I usually place before the table request that will use them.

Darin -

I was looking at some code that another person wrote, and it one program they used only new joins instead of add a file. So joined table1 to table2. New join - table1 to table3, new join - table1 to table4 and so on. Why would they have done it this way vs just adding table2, 3 and 4 as cross-reference files? Any thoughts?

Malinda