Re: Question about joins with default

On 17-Aug-2015 12:21 -0600, James H. H. Lampert wrote:

Given the following situation:

A "Machine" file and a "Package" file, with a one-to-many
relationship. Each "Package" record is tied to a "Machine" record; a
"Machine" record can have zero or more "package" records.

I've got a traditional (i.e., non-SQL) join-logical on the two
files, with a JDFTVAL keyword, so that if a machine has no "package"
defined on it, it still appears, with blank "package" fields, in the
join.

i.e., if we have "machine" records

M1
M2
M3

and package records

M1PA
M1PB
M2PX

then the join shows

M1 M1PA
M1 M1PB
M2 M2PX
M3

Is there a way to set this up so that even if the "machine" has one
or more "packages," the join logical will also show a "no package"
record? i.e., from the same PFs, the join would show

M1
M1 M1PA
M1 M1PB
M2
M2 M2PX
M3

The native join capabilities of the database Join Logical File (JLF) object are too restrictive [cannot mix join types, no cross-join, cannot union data, and can not perform distinct processing] and therefore would be unable to effect that result-set. The Open Query File (OPNQRYF) would also be unlikely to help, for similar reasons.

As Carel alludes, a query that combines two subselects with a UNION [and using COALESCE to effect default value in place of NULL values] could effect that fairly easily. The following just selects all of the columns from the "package" file for simplicity [with references to the column-name M as the "machine" in both files]; per lack of the DDS\DDL to describe the columns and the [default values (DFT)] for the files, and ignores collation beyond the "machine":

select m.m, z.*
from mach m
left outer join
( select p.* from pack p
where 1<>1
) z
on m.m = z.m
union distinct
select m.m, p.*
from mach m
left outer join
pack p
on m.m = p.m
order by 1