I am not clear if the COALESCE() was being used only in the SELECT
list, or in the _SELECTION_. The IFNULL result must be used in the
selection. What appears on a report [or is returned to a program] as
values for the columns is moot for what rows are going to be returned.
Given three tables CREATE TABLE F# (SEQNBR INT) where the first has
rows with all values 0 to 10, the second has all values 0 to 5, and the
third all even numbers 0 to 6, the Query Definition with Type=2 join for
equivalence on SEQNBR between F1->F2 and F2->F3, will give the following
In the above two reports the selection against the columns on the
secondary files requires tests like IFNULL(SEQNBR, 0) IN (0, 2, 4) or
(SEQNBR IS NULL OR SEQNBR = 6) because NULL is generated. The Query/400
definition would require tests like SEQNBR LIST (0, 2, 4) or (SEQNBR =
0 OR SEQNBR = 6) because zero as default is generated.
This thread ...
Re: Making Matched Records with Primary File in Query an Outer join in SQL, (continued)
This mailing list archive is Copyright 1997-2019 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