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


This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].