Query Management and Outer Joins

Hey, Ted:

We have used Query/400 to generate a significant number of queries to which we pass parameters at runtime. We have a good technique, but there is one problem I can't figure out.

Here is the technique we use.

1. We generate a Query/400 query with hard-coded literals in record selection criteria.
   
   
2. When the Query/400 query is working to our satisfaction, we replace the literals with parameter expressions.
   
   
3. We run the Retrieve Query Management Query (RTVQMQRY) command, specifying the Query/400 query we just wrote. We specify ALWQRYDFN(*ONLY) in order to place the retrieved source code into source physical file QQMQRYSRC.
   
   
4. We edit the source member, removing the library names from the FROM clause. We do not want to keep the hard-coded library names, because we need the ability to run the query in both production and test environments.
   
   
5. We run the Create Query Management Query (CRTQMQRY) command against the modified statements to generate a Query Management query object.

To run the query, we use the Start Query Management Query (STRQMQRY) command, passing the parameters to the query from a selection screen through the SETVAR parameter.

However, this technique fails us when we specify a join type of 2 when building the Query/400 query. Join type 2 causes the query to select all records from the primary file, even if there are no matching records in the secondary files. The generated QM query drops unmatched primary-file records as if we had specified join type 1 when we created the Query/400 query.

Is there a way to select unmatched primary records using the technique I've described?

--Rich

Good question, Rich!

Yes, you can fix this. When you tell Query Management to retrieve the SQL from a Query/400 query, it joins the files in the WHERE clause, like this:

H QM4 05 Q 01 E V W E R 01 03 03/03/03 22:28                            
V 1001 050 My Query/400 query converted to QM                         
V 5001 004 *HEX                                                         
SELECT                                                                  
  ALL       T01.SHIPNBR, T01.COMPANY, T01.CUSTNBR, T01.DATE, T01.PRONBR 
            T02.COMPANY, T02.CUSTNBR, T02.CUSTNAME, T02.CADDRESS1,      
            T02.CADDRESS2, T02.CCITY, T02.CSTATE, T02.CZIP, T02.CREDITL 
            T02.TSTAMP                                                  
  FROM      MYLIB/SHIPMENTS T01,                                       
            MYLIB/CUSTOMER T02                                         
  WHERE     T01.COMPANY = T02.COMPANY                                   
    AND     T01.CUSTNBR = T02.CUSTNBR                                   
    AND(    T01.DATE BETWEEN &FROMDATE AND &THRUDATE)

You need to change it to use the JOIN clause, like this:

FROM      SHIPMENTS T01 LEFT OUTER JOIN 
          CUSTOMER T02                  
   ON     T01.COMPANY = T02.COMPANY     
  AND     T01.CUSTNBR = T02.CUSTNBR     
WHERE     T01.DATE BETWEEN &FROMDATE AND &THRUDATE

A left outer join is what you're using when you specify join type 2 in Query. The join criteria have been moved from the WHERE clause to the ON clause. The record selection criterion remained in the WHERE clause, since it has nothing to do with joining the files.

You can also specify the right outer join, the left and right exception joins, the inner join, and the cross join. The inner join and the left exception join are like Query/400 join types 1 and 3, respectively.

--Ted

Sponsored By COMMON

Get the IT training you need by attending COMMON Users Group's Spring 2003 IT Education Conference and Expo, March 9 - 13, in Indianapolis. Choose from hundreds of sessions and labs covering the widest range of industry topics, including RPG IV, LPAR, WebSphere, and High Availability. COMMON is the best value in IT education, so don't miss out! Click and visit www.common.org for details!