|
midrange.com
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
