RE: SQL Left Outer Join with WHERE phrase -- MIDRANGE-L

Or use T02.DELETE IS NULL in the WHERE clause. Although I like using the ON clause better. That's nice.

The default value on outer joins changed when null support came on the scene. It used to be blanks, now it's nulls. Query/400 is still blanks, AFAIK.

Also, you found one of the limitations of RTMQMQRY with QRYDFNs - the only JOIN type that will be used is matched records - the other 2 get changed to that. Gotcha! <g> eh? This is known but IBM is not changing it.

There IS the ANZQRY command, which tells you what may not work when you use RTVQMQRY. E.g., I made a type 3 (unmatched records) query which had no fields explicitly selected:

ANZQRY QRY(VERN/TEST2)
Type of join ignored.
Previously defaulted field selections used.
Analyze completed for query TEST2 in VERN, code 30.

The second-level text is very useful here.

Vern

At 08:40 AM 10/22/2003 -0500, you wrote:

We figured it out--put the WHERE phrase in the LEFT OUTER JOIN phrase like
this:
LEFT OUTER JOIN NOTBLDPF T02 ON
(T01.CONTRL = T02.CONTRL AND T01.ODCTR = T02.ODCTR AND T02.DELETE = ' ').

Hope this helps someone who does an archive search.

Roger Mackie

-----Original Message-----
From: Mackie, Roger L. (Precision Press) [mailto:RLMackie@xxxxxxxxxxxxx]
Sent: Wednesday, October 22, 2003 8:13
To: 'Midrange Systems Technical Discussion'
Subject: SQL Left Outer Join with WHERE phrase

Hi group,

How can we get the AS/400 server to respect a LEFT OUTER JOIN from a client
when there is a WHERE phrase on the outer file?

Currently we have a query that fills a file that is queried by MS Excel. We
are trying to get rid of the result file by having MS Excel query the files.
RTVQMQRY gave us a place to start. Using RTVQMQRY on a *QRYDFN, we retrieved
the following SQL statement:
SELECT

-snip-

The query result file on the AS/400 (v5r1, see below) has all the records in
T01, whether there is a record in T02 or not (LEFT OUTER JOIN on T02--what
we want). If we copy and paste this same query into STRSQL or MS Excel, the
result set has only inner join rows.

-snip-

This mailing list archive is Copyright 1997-2024 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].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.