RE: Making Matched Records with Primary File in Query an Outer joinin SQL

Note that one thing to keep in mind is the join fields being specified in Query or SQL

There's two options in both:
F1 joined to F2
F2 joined to F3

Or

F1 joined to F2
F1 joined to F3

The join chosen changes the results. For instance, with Chucks example data slightly modified (F1 has
1-10, F2 has 1-5, and F3 has 2,4,6,8 ) option 1 returns:

SEQNBR SEQNBR SEQNBR
1 1 -
2 2 2
3 3 -
4 4 4
5 5 -
6 - -
7 - -
8 - -
9 - -
10 - -

Whereas option 2 returns
SEQNBR SEQNBR SEQNBR
1 1 -
2 2 2
3 3 -
4 4 4
5 5 -
6 - 6
7 - -
8 - 8
9 - -
10 - -

Pete, make sure you double check the joins being done in query vs. the joins being done in SQL.

HTH,
Charles

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Thursday, October 11, 2007 5:54 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Making Matched Records with Primary File in
Query an Outer joinin 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
--
All comments provided "as is" with no warranties of any
kind whatsoever and may not represent positions, strategies,
nor views of my employer

Pete Helgren wrote:

Should I use coalesce for all the select fields then? I

tried using

coalesce(fieldName,' ') on the two fields that appear in

the query as

"blank" values but I still have no "missing" records as I run the
Query and the SQL side by side. I am guessing that it is due to my
join logic or the where clause, but I haven't tripped over

the "magic" combination yet.

--
This is the Midrange Systems Technical Discussion
(MIDRANGE-L) mailing list To post a message email:
MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change
list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting,
please take a moment to review the archives at
http://archive.midrange.com/midrange-l.

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.