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     Report dropping tables in join

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Report dropping tables in join
 Login/Join
 
<kvn>
posted
How do I force WF to join a table even though data isn't being used from it in the report? Is there a set command for this?

I created this join of about 4 tables but only use fields from 3 of them in the report. So when the report comes back it will only join the tables I used fields from and completely leave out the other table with fields not being used. All tables are needed for the data to join properly.
 
Report This Post
susannah
Expert
posted Hide Post
kvn, put a ? JOIN statement after your join and then another one after your tablefile.
this will show you if your joins are still in effect.
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
N.Selph
Guru
posted Hide Post
I am assuming you are querying a relational database.
To get that fourth table to join in, you will have to use it somehow. You might include a field from it as "NOPRINT" in your request, or use its copy of the value you joined on in the report. If the join was a cross-reference table, and you are already using fields on both sides of it, it should be present already in the SQL.
If you haven't already, do a client trace of the SQL to see what is being passed by WF to your RDBMS.

I can't think of an example, though, of why you would need it for a proper join, if it is not really used in any way.
 
Posts: 391 | Location: California | Registered: April 14, 2003Report This Post
Anonymouse
Silver Member
posted Hide Post
Try using the JOINed fields from the bypassed table in the next JOIN. Assume you're joining 3 tables like this:

JOIN KEY1 IN HOSTFILE TO KEY1 IN XREF1 AS J1
JOIN KEY1 AND KEY2 IN HOSTFILE
TO KEY1 AND KEY2 IN XREF2 AS J2
END

You're not printing any of the fields from XREF1, so FOCUS bypasses it. But in order for you to pull the correct data, there must be a record in XREF1. Try changing the JOIN to this:

JOIN KEY1 IN HOSTFILE TO KEY1 IN XREF1 AS J1
JOIN XREF1.KEY1 AND KEY2 IN HOSTFILE
TO KEY1 AND KEY2 IN XREF2 AS J2
END

This will force FOCUS to use a field in the bypassed table.
 
Posts: 34 | Location: Minneapolis, MN | Registered: June 10, 2003Report This Post
<kvn>
posted
My joins look similar to what you expected Jeff, but I'm not familiar with a some parts of your join. The parts using 'XREF1' and 'XREF2' are not familiar. In my joins I just use the following syntax.

JOIN
HOSTFILE1.KEY IN HOSTFILE1 TO HOSTFILE2.KEY IN HOSTFILE2 AS J1
END

I tried looking up the syntax your using but with no luck. So what is 'XREF1' and 'XREF2'?
 
Report This Post
Anonymouse
Silver Member
posted Hide Post
Sorry to have taken so long to reply...
XREF1 is merely the "name" of the second file in the join. In 'olden' FOCUS times, we referred to that file as the "cross-referenced" file.

The concept to take away here is that you should specify that FOCUS use the fields from the bypassed table in your second join-- that will force FOCUS to build SQL that references the bypassed table. Taking your example and expanding it, I'm suggesting this:
JOIN
HOSTFILE1.KEY IN HOSTFILE1 TO HOSTFILE2.KEY IN HOSTFILE2 AS J1
END
JOIN
HOSTFILE2.KEY IN HOSTFILE1 TO HOSTFILE3.KEY IN HOSTFILE3 AS J1
END
Note that the second join uses the key field from hostfile2 as though it was in hostfile1 (which, logically, it is, after the first join). Now that you've referenced a field in hostfile2, it shouldn't be bypassed.
 
Posts: 34 | Location: Minneapolis, MN | Registered: June 10, 2003Report This Post
Anonymouse
Silver Member
posted Hide Post
...of course, that second join should have been named J2...
 
Posts: 34 | Location: Minneapolis, MN | Registered: June 10, 2003Report 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     Report dropping tables in join

Copyright © 1996-2020 Information Builders