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.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
JOIN question
 Login/Join
 
Member
posted
I am doing JOIN F1 to F2. SET ALL=PASS.
My query print fields from both tables. There is a record in F1 which has no descendants. If I use no WHERE clauses at all, I get that record from F1. (The F2 fields are 'missing', as they should be.) But if I put a WHERE filter on F2, I don't get that F1 record. Why not?

Thanks,
Phyllis
 
Posts: 15 | Registered: May 17, 2004Report This Post
Virtuoso
posted Hide Post
On your join issue have you tried SET ALL=ON
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Master
posted Hide Post
What Product and Version are you using?
 
Posts: 865 | Registered: May 24, 2004Report This Post
Guru
posted Hide Post
What type of databases are you using?
 
Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003Report This Post
Virtuoso
posted Hide Post
SET ALL=PASS is not supported in relational data adapters.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Member
posted Hide Post
This is mainframe Focus, v 7.2.5. I am joining a HOLD file (parent) to DB2 data (child). I also would like this to work joining two HOLD files or two DB2 tables.
SET ALL=PASS is not supported with this??? When IS SET ALL=PASS supported?

The idea is that I want the parent even though a
WHERE clause is used on the child.

Sounds like I have to:
SET ALL=ON,
then omit my WHERE clauses and run the query, then HOLD the result,
then include the WHERE clauses and run a query that reads that HOLD file.
 
Posts: 15 | Registered: May 17, 2004Report This Post
Virtuoso
posted Hide Post
Educated guess: support for PASS depends on whether the condition is "optimized" (i.e., passed to the DB2 server as part of the SQL Select.)

If Focus "optimizes" the request's screening condition, then whenever DB2 returns zero rows for a particular JOIN-key value of a row of the HOLD file, Focus has no way of knowing whether that is because there were no rows matching the join key, or there were such rows but none passed the screening condition. (In the former case, the parent should in principle be included if ALL=PASS but excluded if ALL=ON; in the latter, excluded regardless of ALL). So Focus reverts to ALL=ON logic if the Where is optimized.


If Focus cannot optimize the condition (which is often the case if the condition is expressed in terms of DEFINEd fields), then all child rows that match the join key are returned to Focus and Focus then applies the screening condition; in that case Focus is in a position to distinguish "no children" from "no suitable children", and can support ALL=PASS logic.


So, if you revise the Where condition so that Focus cannot express it as a SQL SELECT WHERE clause to pass to DB2, you may get the desired answer set. (But performance may be degraded.)
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
An alternative is MATCH FILE, with AFTER MATCH HOLD OLD. That forces the parent ("old") row to be included regardless of presence of suitable matching child ("new") rows.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
Looking back at the original post, I have some questions.

quote:
Originally posted by koslo:
[qb] I am doing JOIN F1 to F2. SET ALL=PASS.
My query print fields from both tables. There is a record in F1 which has no descendants. If I use no WHERE clauses at all, I get that record from F1. (The F2 fields are 'missing', as they should be.) But if I put a WHERE filter on F2, I don't get that F1 record. Why not?

Thanks,
Phyllis [/qb]
1. Is that a one-one join (JOIN without ALL), or one-many (JOIN ... TO ALL)?

If it's written as a one-one join, the "SET ALL=ON / OFF / PASS" setting is irrelevant, and the WHERE condition is applied to the default values (zero or blank) of F2's fields, so the omission of childless parents is an appropriate result.

If it's written as one-many (as it would appear from your reference to missing values), the next question is -

2. Is ALL=PASS what you really want?

Let's paraphrase the request as "list housholds and their teenage children" (F1=households, F2=children in households).

With ALL=ON or OFF, the report would include households with teenagers, listing just the teenage children in each (omitting any households with no children, and any with just children not in their teens)

With ALL=PASS, the report would include households with teenagers, listing just the teenage children in each, plus households with no children -- but would omit households with only toddlers.

If what you need is "all households (with or without children), showing any teenage children (or '.' if none)", that's something that the JOIN ALL + SET ALL + WHERE combo won't provide. Instead use either

a. MATCH FILE F1 ...
FILE F2 ... WHERE ...
AFTER MATCH HOLD OLD ...

or

b. JOIN ... F1 TO ALL ... F2
TABLE FILE F1 ...
WHERE TOTAL <condition>;
...
wording the condition to take rows with either null values or suitable "real" values for the fields from F2.

Hope that clarifies things.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders