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

On your join issue have you tried SET ALL=ON

What Product and Version are you using?

What type of databases are you using?

SET ALL=PASS is not supported in relational data adapters.

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.

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.)

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.

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.