|
midrange.com
Boy, are you a glutton for punishment! <g> Before you pore over this, you
should know that I removed the () and got the same results. Also,
T03.SCAMSS was used for the TOTAL summary function, which is the only thing
I can think of. Here goes:
5722QU1 V5R2M0 020719 IBM Query for AS/400 DUROLAST
8/12/04 15:41:56
Query . . . . . . . . . . . . . . . . . SPECIALS2
Library . . . . . . . . . . . . . . . DJBTEST03
Query text . . . . . . . . . . . . . . Join CUSINVL0, CIVSPCL0,
COSSPCL0, CUSMAS
Query CCSID . . . . . . . . . . . . . . 65535
Query language id . . . . . . . . . . . ENU
Query country or region 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 CUSINVL0 AMFLIB *FIRST CUSINVR0
T02 CIVSPCL0 AMFLIB *FIRST CIVSPCR0
T03 COSSPCL0 AMFLIB *FIRST COSSPCR0
T04 CUSMAS AMFLIB *FIRST CUSMASMA
Join tests
Type of join . . . . . . . . . . . . . Matched records
Field Test Field
T01.INVNCV EQ T02.INVNVS
T01.INSQCV EQ T02.INSQVS
T02.ORDRVS EQ T03.ORDRSS
T02.SHSQVS EQ T03.SHSQSS
T02.SCSQVS EQ T03.SCSQSS
T01.CONOCV EQ T04.COMNO
T01.CUNOCV EQ T04.CUSNO
Result fields
Name Expression Column Heading
Len Dec
GLACCT substr( SCDSSS, 1, 6) G/L
Acct
No.
IBM Query for AS/400
8/12/04 15:41:56
Select record tests
AND/OR Field Test Value (Field, Numbers, or
'Characters')
CONOCV EQ 01
AND ICDTCV RANGE 1040701 1040731
AND SCCDSS EQ '2'
Ordering of selected fields
Field Sort Ascending/ Break Field
Name Priority Descending Level Text
GLACCT 10 A 1
T01.INVNCV 20 A INVOICE NUMBER
T01.CUNOCV CUSTOMER NUMBER
T04.CUSNM CUSTOMER NAME
T01.IVDTCV OPTIONAL INVOICE DATE
T02.LISQVS LINE ITEM SEQUENCE
T03.SCDSSS SPECIAL CHARGE DESCRIPTION
T03.SCAMSS SPECIAL CHARGE AMOUNT
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
GLACCT 0 G/L 6
Acct
No.
T01.INVNCV 2 Inv. 6 0
Yes
No.
T01.CUNOCV 1 Customer 8 0
Yes
Number
T04.CUSNM 2 CUSTOMER NAME 25
T01.IVDTCV 2 OPTIONAL 7 0
INVOICE
DATE
T02.LISQVS 1 LINE 12 0
Yes
ITEM
SEQUENCE
T03.SCDSSS 2 SPECIAL CHARGE 30
Description
T03.SCAMSS 1 0 SPECIAL 13 2
CHARGE
AMOUNT
IBM Query for AS/400
8/12/04 15:41:56
Report breaks
Break New Suppress Break
Level Page Summaries Text
1 No No G/L Account: &GLACCT
Selected output attributes
Output type . . . . . . . . . . . . . . Printer
Form of output . . . . . . . . . . . . Detail
Line wrapping . . . . . . . . . . . . . No
Printer Output
Printer device . . . . . . . . . . . . *PRINT
Report size
Length . . . . . . . . . . . . . . . 66 (default)
Width . . . . . . . . . . . . . . . . 132
Report start line . . . . . . . . . . . 1
Report end line . . . . . . . . . . . . 63
Report line spacing . . . . . . . . . . Single space
Print definition . . . . . . . . . . . No
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 . . . . . . . . . . . Yes
Cover page title
Page headings and footings
Print standard page heading . . . . . . Yes
Page heading
Page footing
* * * * * E N D O F Q U E R Y P R I N T * * * * *
Thanks Vern!
Dan
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx / Vern Hamberg
> Sent: Thursday, August 12, 2004 3:31 PM
>
> db
>
> Did you have a result field defined on T03.SCAMSS? No, that
> should have had
> an AS after it.
>
> Are you willing to print the QRYDFN and post it here, or send it
> to me offlist?
>
> Vern
>
> At 08:09 AM 8/12/2004, you wrote:
> >O.k., I've got this working now with:
> >
> >SELECT
> > ALL SUBSTR(T03.SCDSSS,1,6) AS GLACCT, T01.INVNCV,
> > T01.CUNOCV, T04.CUSNM, T01.IVDTCV, T02.LISQVS,
> > T03.SCDSSS, (T03.SCAMSS)
> > FROM AMFLIB/CUSINVL0 T01
> > inner join AMFLIB/CIVSPCL0 T02
> > on ( T01.INVNCV = T02.INVNVS
> > AND T01.INSQCV = T02.INSQVS )
> > inner join AMFLIB/CUSMAS T04
> > on ( T01.CONOCV = T04.COMNO
> > AND T01.CUNOCV = T04.CUSNO )
> > inner join AMFLIB/COSSPCL0 T03
> > on ( T02.ORDRVS = T03.ORDRSS
> > AND T02.SHSQVS = T03.SHSQSS
> > AND T02.SCSQVS = T03.SCSQSS )
> > Where T01.CONOCV = 01
> > AND T01.ICDTCV BETWEEN 1040701 AND 1040731
> > AND T03.SCCDSS = '2'
> > ORDER BY GLACCT ASC, T01.INVNCV ASC
>
> -snip-
>
> >Oh, and I would ask again, why did RTVQMQRY surround the
> T03.SCAMSS field in
> >the SELECT in parentheses?
>
> -snip-
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> 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-2024 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.
