RE: SQL Left Outer Join with WHERE phrase

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

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-