RE: Help with SQL exception join -- MIDRANGE-L

Run the following command

RTVQMQRY QMQRY(TULTBQRY03)
         SRCFILE(YOURLIB/YOURSRC)
         ALWQRYDFN(*YES)


Thank you,
Matt Tyler
Mattt@wincofoods.com

-----Original Message-----
From: Karl Keller [mailto:kkeller@girling.com]
Sent: Monday, October 14, 2002 10:49
To: midrange-l@midrange.com
Subject: Help with SQL exception join

This is a multi-part message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
Hello group,

    I'm trying to replace a Query/400 query with an equivalent SQL
statement. The query does an exception join into a output file. Here's are
the Query/400 query specs:

5722QU1  V5R1M0 010525   IBM Query for AS/400 S10221FD 10/14/02    9:36:02
Page    1


   Query . . . . . . . . . . . . . . . . . TULTBQRY03
     Library . . . . . . . . . . . . . . . P400NEW
   Query text  . . . . . . . . . . . . . .
   Query CCSID . . . . . . . . . . . . . . 65535
   Query language id . . . . . . . . . . . ENU
   Query country id  . . . . . . . . . . . US

     *** . is the decimal separator character for this query ***
   Collating sequence  . . . . . . . . . . Hexadecimal





   Processing options
     Use rounding  . . . . . . . . . . . . Yes (default)
     Ignore decimal data errors  . . . . . No (default)
     Ignore substitution warnings  . . . . Yes
     Use collating for all compares  . . . Yes



 Selected files

   ID     File           Library       Member        Record Format

   T01    PEMASTP        NETFIL        *FIRST        PEMASTF1
   T02    TB001PF        P400NEW       *FIRST        TOFILEF1


 Join tests

   Type of join  . . . . . . . . . . . . . Unmatched records with primary
file

   Field              Test              Field

   T01.PECONO         EQ                T02.TOCONO
   T01.PEEMNO         EQ                T02.TOEMNO


 Select record tests

   AND/OR   Field             Test     Value (Field, Numbers, or
'Characters')

                PECONO          EQ       160
    AND     PELVL2            LIST     160 162
    AND     PELVL1            EQ       75000
    AND     PEJCLS            EQ       5000
    AND     PESTAT            EQ       'A'

                 IBM Query for AS/400                  10/14/02    9:36:02
Page    2


 Ordering of selected fields

   Field           Sort      Ascending/  Break  Field
   Name            Priority  Descending  Level  Text

   T01.PEEMLN      10        A                  LAST NAME
   T01.PEEMFN      20        A                  FIRST NAME
   T01.PEEMA1                                   EMPLOYEE ADDRESS LINE 1
   T01.PEEMA2                                   EMPLOYEE ADDRESS LINE 2
   T01.PEECTY                                   EMPLOYEE CITY
   T01.PEESTA                                   EMPLOYEE STATE
   T01.PEEZIP                                   EMPLOYEE ZIP CODE
   T01.PEEZP2                                   EMPLOYEE NEW ZIP CODE


 Report column formatting and summary functions
   Summary functions:  1-Total, 2-Average, 3-Minimum, 4-Maximum, 5-Count
Overrides
   Field           Summary    Column                                Dec
Null       Dec  Numeric
   Name            Functions  Spacing  Column Headings         Len  Pos  Cap
Len  Pos  Editing

   T01.PEEMLN                 0        LAST NAME                   15
   T01.PEEMFN                 2        FIRST NAME                  10
   T01.PEEMA1                 2        ADDRESS LINE 1           30
   T01.PEEMA2                 2        ADDRESS LINE 2           30
   T01.PEECTY                 2        CITY                               21
   T01.PEESTA                 2        STATE                             2
   T01.PEEZIP                  2        ZIP
5
   T01.PEEZP2                 2        NEW                                4
                                       ZIP


 Selected output attributes

   Output type . . . . . . . . . . . . . . Database file
   Form of output  . . . . . . . . . . . . Detail
   Line wrapping . . . . . . . . . . . . . No



 Printer Output

   Printer device  . . . . . . . . . . . . *PRINT
   Report size
     Length  . . . . . . . . . . . . . . .  66 (default)
     Width . . . . . . . . . . . . . . . . 139
   Report start line . . . . . . . . . . .   6
   Report end line . . . . . . . . . . . .  60
   Report line spacing . . . . . . . . . . Single space
   Print definition  . . . . . . . . . . . No

                 IBM Query for AS/400                  10/14/02    9:36:02
Page    3


 Printer Spooled Output

   Spool the output  . . . . . . . . . . . (Defaults to value in print file,
QPQUPRFIL)
   Form type . . . . . . . . . . . . . . . (Defaults to value in print file,
QPQUPRFIL)
   Copies  . . . . . . . . . . . . . . . .   1
   Hold  . . . . . . . . . . . . . . . . . (Defaults to value in print file,
QPQUPRFIL)



 Cover Page

   Print cover page  . . . . . . . . . . . No
     Cover page title



 Page headings and footings

   Print standard page heading . . . . . . Yes

     Page heading


     Page footing



 Database file output

   File  . . . . . . . . . . . . . . . . . TB003PF
     Library . . . . . . . . . . . . . . . P400NEW
   Member  . . . . . . . . . . . . . . . . *FILE
   Data in file  . . . . . . . . . . . . . Add to member
   For a new file:
     Authority . . . . . . . . . . . . . . *LIBCRTAUT
     Text about
       the file  . . . . . . . . . . . . .
   Print definition  . . . . . . . . . . . No

                 IBM Query for AS/400                  10/14/02    9:36:02
Page    4


 Output file record format

   Output record length  . . . . . . . . .   117
   Output CCSID value  . . . . . . . . . .    37

   Field list:

     Field          Begin   Len  Dec   Null  Data Type            Text

     PEEMLN             1    15              Character            LAST NAME
     PEEMFN            16    10              Character            FIRST NAME
     PEEMA1            26    30              Character            EMPLOYEE
ADDRESS LINE 1
     PEEMA2            56    30              Character            EMPLOYEE
ADDRESS LINE 2
     PEECTY            86    21              Character            EMPLOYEE
CITY
     PEESTA           107     2              Character            EMPLOYEE
STATE
     PEEZIP           109     5              Character            EMPLOYEE
ZIP CODE
     PEEZP2           114     4              Character            EMPLOYEE
NEW ZIP CODE


                 * * * * *    E N D   O F   Q U E R Y   P R I N T   * * * *
*

Here's my equivalent (I Think) SQL statement:

INSERT INTO P400NEW.TB003PF
(TEEMLN, TEEMFN, TEEMA1, TEEMA2,  PEECTY, TEESTA, TEEZIP, TEEZP2)
SELECT PEEMLN, PEEMFN,  PEEMA1, PEEMA2, PEECTY, PEESTA, PEEZIP, PEEZP2
FROM NETFIL.PEMASTP
EXCEPTION JOIN
P400NEW.TB001PF ON
PECONO = TOCONO AND
PEEMNO = TOEMNO AND
PECONO = 160 AND
PELVL2 IN (160, 162) AND
PELVL1 = 75000 AND
PEJCLS = 5000 AND
PESTAT = 'A'
ORDER BY PEEMLN ASC, PEEMFN ASC

When I run the Query/400 query, 64 records are written to my output file.
When I run my SQL query, 70907 records are written to my output file.
It looks like it's ignoring the:
PECONO = 160 AND
PELVL2 IN (160, 162) AND
PELVL1 = 75000 AND
PEJCLS = 5000 AND
PESTAT = 'A'
part of the statement.

Does anyone have any ideas or advice?

Thanks in advance for your help!


Karl Keller
Information Systems
Girling Health Care, Inc.

Confidentiality Notice:  This e-mail message, including any attachments, is
for the sole use of the intended recipient(s) and may contain confidential
and privileged information.  Any unauthorized review, use, disclosure or
distribution is prohibited.  If you are not the intended recipient, please
contact the sender by reply e-mail and destroy all copies of the original
message.

--

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.