Re: Making Matched Records with Primary File in Query an Outer join in SQL

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 report:

SEQNBR SEQNBR SEQNBR
0 0 0
1 1 0
2 2 2
3 3 0
4 4 4
5 5 0
6 6 6
7 0 0
8 0 0
9 0 0
10 0 0
******** End of report ***

The following SQL SELECT and join, and its output:

select a.seqnbr,b.seqnbr,c.seqnbr
from F1 a
left outer join F2 b on a.seqnbr = b.seqnbr
left outer join F3 c on b.seqnbr = c.seqnbr

SEQNBR SEQNBR SEQNBR
0 0 0
1 1 -
2 2 2
3 3 -
4 4 4
5 5 -
6 6 6
7 - -
8 - -
9 - -
10 - -
******** End of data ****

The following SQL SELECT and join, and its output:

select
a.*,ifnull(b.seqnbr,0) as seqnbr,ifnull(c.seqnbr,0) as seqnbr
from F1 a
left outer join F2 b on a.seqnbr = b.seqnbr
left outer join F3 c on b.seqnbr = c.seqnbr

SEQNBR SEQNBR SEQNBR
0 0 0
1 1 0
2 2 2
3 3 0
4 4 4
5 5 0
6 6 6
7 0 0
8 0 0
9 0 0
10 0 0
******** End of data ********

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.

Regards, Chuck