Not All Outer Joins Are Equal

Hey, Ted:

We have been using your technique to convert Query/400 queries to SQL ["Query Management and Outer Joins"]. All but one converted query works like the original. The one that doesn't work has us baffled.

The query joins two files using a type 2 join (Matched Records with Primary File). We select records where a field from the secondary file is blank. This gets two types of records: those that have no match in the secondary file and those that have a match with blanks in the record selection field. Even though we converted the generated SQL to a left outer join, the SQL query only returns the latter type of record. Can you tell us why?

--Bobby

To illustrate Bobby's predicament, assume two files: invoices and customers. I'll list only the fields necessary for this example.

INVOICES
==============
INVNBR
COMPANY
CUSTNBR
AMOUNT

CUSTOMERS
==============
COMPANY (key)
CUSTNBR (key)
CUSTYPE

INVOICES is the primary file, joined to CUSTOMERS on COMPANY and CUSTNBR.

Assume that a query selects the records where CUSTYPE is equal to blanks. This retrieves invoices for customers who are not in the customer master file, and also for customers who are on file but whose CUSTYPE field is blank.

                    Customer
Invoice             account             Customer
number   Company    number     Amount   name
=============================================================
1214         5          1      125.00   -
1215         3         56       65.00   Jak's Liver Emporium

In this example, invoice 1214 references a customer that is not on file, while invoice 1215 refers to a customer with a blank customer type field.

Using the Retrieve Query Management Query (RTVQMQRY) command produces a source member that should carry out an equivalent join.

Here is the command.

RTVQMQRY QMQRY(MYLIB/CUSTYPE1) SRCFILE(MYLIB/SRC)
   SRCMBR(CUSTYPE2) ALWQRYDFN(*YES)

Here is the generated source member.

SELECT
  ALL       T01.INVNBR, T01.COMPANY, T01.CUSTNBR, T01.AMOUNT,
            T02.CUSTNAME
  FROM      MYLIB/INVOICES T01,
            MYLIB/CUSTOMER T02
  WHERE     T01.COMPANY = T02.COMPANY
    AND     T01.CUSTNBR = T02.CUSTNBR
    AND(    CUSTYPE = ' ')

Create and run the following query:

CRTQMQRY QMQRY(MYLIB/CUSTYPE2)
   SRCFILE(MYLIB/SRC) SRCMBR(CUSTYPE2)

STRQMQRY QMQRY(MYLIB/CUSTYPE2)

You receive this output:

                   Customer
Invoice            account              Customer
number  Company    number      Amount   name
-------  -------  --------  ----------  --------------------
1215        3        56         65.00   Jak's Liver Emporium

You see only the customers who are in the CUSTOMERS database file and have a blank customer type. Customers not in the CUSTOMERS file are omitted.

The difference is in the way that Query Management and Query/400 handle the fields from unmatched records. Query Management uses the SQL standard, which is to fill the fields with null values. However, Query/400 fills the fields of unmatched records with default values, normally blanks for alpha fields and zero for numeric ones. In other words, a Query/400 left outer join is not like an SQL left outer join.

To make the Query Management query work like the original Query/400 query, you have to specify that you want a blank value instead of a null value for unmatched records. Use the COALESCE, IFNULL, or VALUE function. Put the name of the field in the first parameter of the function and the default value in the second parameter.

Here's the revised source member from which you can generate the QM query.

SELECT
  ALL       T01.INVNBR, T01.COMPANY, T01.CUSTNBR, T01.AMOUNT,
            T02.CUSTNAME
  FROM      MYLIB/INVOICES T01
  LEFT JOIN MYLIB/CUSTOMER T02
    ON      T01.COMPANY = T02.COMPANY
    AND     T01.CUSTNBR = T02.CUSTNBR
  WHERE     COALESCE(CUSTYPE,' ') = ' '

--Ted

Sponsored By ADVANCED SYSTEMS CONCEPTS

Quoted from an experienced programmer new to the iSeries (AS/400): "The best thing about working on the AS/400 is using ASC's SEQUEL product." If you're tired of the limitations imposed by Query/400 or ODBC-based query and reporting tools, you need SEQUEL. Discover how thousands of sites around the world have improved access to iSeries data using SEQUEL's Windows- and Web-interfaces. It's the one tool you can rely on for virtually all your iSeries data access needs. FREE trial available. Read More about SEQUEL