Joining on Incompatible Fields in Query/400

Hey, Ted:

Query/400 is still a popular tool in iSeries shops, for good reason. In spite of its numerous and well-known limitations, it's easy to use and can do many things. Nevertheless, I'm always amazed to read tips that allow the iSeries community to squeeze just a little more work out of Query/400. Here's another tip to add to the growing collection.

Say that you need to run a query against the following two tables:

Table PAYHST:
SSN     Dec(9,0) 
TDate   Date
Hours   Dec(5,2) 
Rate    Dec(7,3) 

Table EMPMAS:
EmpNo    Dec(5,0)
SSN      Char(9)
EmpName  Char(30)

The common field between the two tables is SSN (for "social security number"), but the data types are incompatible (DEC and CHAR). Think this one's too tough for Query? Not at all, as long as we're doing a matched record join (this scenario will not work for join with default or exception joins.) How is this possible? Easy, by following three simple steps.

Step 1: Specify your files without specifying a join.

Enter the tables (I ordered them as EMPMAS T01, followed by PAYHST T02) in the Specify File Selections panel. Next, enter a 1 (matched records) as the type of join and specify *ALL in the upper left most box of the Specify How to Join Files screen. You can't specify the join here, because Query will scream that you're joining incompatible data types. The *ALL option in Query/400 is the SQL equivalent of a cross join (Cartesian product), where every record in the secondary table is joined to the first table. You still will need to connect the tables by SSN, but you won't define the join in the usual place.

Step 2: Create a work field to convert the data type.

In the Define Result Fields panel, create an alpha version of the PAYHST table's SSN field, using the DIGITS function:

                             Define Result Fields 

Type definitions using field names or constants and operators, press
Enter.
  Operators:  +, -, *, /, SUBSTR, ||, DATE...

Field       Expression                         Column Heading
Len   Dec 
SSN_A       DIGITS(T02.SSN)                    Social
                                               Security
                                               Number

Creating field SSN_A converts T02.SSN to alpha. You now have compatible data types between the tables to do the join.

Step 3: Specify the join criteria in the Select Records option.

Use the Select Records option to specify the join that would normally occur in the "Specify file selections" by setting the EMPMAS SSN field (T01.SSN) equal to the alpha result field:

SSN_A             EQ     T01.SSN

That's it. When the query runs, the resulting data in the two tables will be joined by SSN.

Once upon a time, I wouldn't have thought Query/400 was capable of performing such a feat. Who knows what other little gems others will continue to uncover with this tool.

--Mike

Thanks for the tip, Mike. The Cartesian product can come in mighty handy. Here's another tip that uses a cross join.

If you need an outer join, you can have it. You have to do a little work, though.

Create source physical files QQMQRYSRC and QQMFORMSRC, if they don't exist already in the library you're working from. The two files should have record lengths of 91 and 162 respectively.

Run the Retrieve Query Management Query and Retrieve Query Management Form commands to extract the query definition into the two source files.

RTVQMQRY QMQRY(SSNJOIN) +
   SRCFILE(MYLIB/QQMQRYSRC) +
   SRCMBR(SSNQ1) ALWQRYDFN(*YES)

RTVQMFORM QMFORM(SSNJOIN) +
   SRCFILE(MYLIB/QQMFORMSRC) +
   SRCMBR(SSNQ1) ALWQRYDFN(*YES)

Modify the SQL source code in QQMQRYSRC to produce a left outer join. Here's an example of the code before modification.

H QM4 05 Q 01 E V W E R 01 03 03/05/29 23:52                                  
V 1001 050 Payroll Report                                          
V 5001 004 *HEX                                                               
SELECT                                                                        
  ALL       DIGITS(SOCSEC) AS ASOCSEC, T01.SSN, T01.NAME, 
            T02.SOCSEC, T02.NAME
  FROM      MYLIB/SSNA T01,                                                  
            MYLIB/SSNB T02                                                   
  WHERE     DIGITS(SOCSEC) = SSN                                              

Here's the same SQL command modified for a left outer join. Notice that the comma after T01 is gone. Don't forget to delete that comma. I also removed the library names so the query would run over the first appropriately named files it found in the library list.

H QM4 05 Q 01 E V W E R 01 03 03/05/29 23:52   
V 1001 050 join numeric to character           
V 5001 004 *HEX                                
SELECT                                         
  ALL       DIGITS(SOCSEC) AS ASOCSEC, T01.SSN,
            T01.NAME, T02.SOCSEC, T02.NAME     
  FROM      SSNA T01 
  left join SSNB T02                    
  on        DIGITS(SOCSEC) = SSN               

Create the Query Management query and form, using the Create Query Management Query (CRTQMQRY) and Create Query Management Form (CRTQMFORM) commands.

CRTQMQRY QMQRY(MYLIB/SSNQ1) SRCFILE(MYLIB/QQMQRYSRC)

CRTQMFORM QMFORM(MYLIB/SSNQ1) SRCFILE(MYLIB/QQMFORMSRC)

I hope you have AS/400 Query Manager installed. If so, use the Start Query Manager (STRQM) command. Use option 2 of the Query Manager menu to dress up the form. If you decide to make further changes to the SQL command, use option 1.

When you execute Query Manager, you'll have a query that runs over files in the library list and does an outer join, with very little work. To run the query, use the Start Query Management Query command.

OVRPRTF FILE(QPQXPRTF) PAGESIZE(*N 132)

STRQMQRY QMQRY(MYLIB/SSNQ1) +
   OUTPUT(*PRINT) +
   QMFORM(*QMQRY) +
   DATETIME(*NO) +
   PAGNBR(*NO)           

If you omit the override, Query Management uses a line length of 80.

For another example of converting a Query to a left outer join, read the article "Query Management and Outer Joins."

--Ted

Sponsored By INFINIUM USERNET

Infinium UserNet is the user-run organization for professionals who support, manage and rely on Infinium software applications. Attend the 2003 Infinium UserNet Conference & Expo from June 18-20 in Oak Brook, IL. Technical education, in-depth seminars, user experiences, new product showcase and networking opportunities abound. For the latest information and registration, visit www.infiniumusernet.org