RE: Arrays

SQL definitely is made for stuff like this. It's made to work with sets of
data. One record at a time, use tradition I/O. Want to grab a bunch of
records at once, use SQL. Boy, I hope I didn't start another holy war.

Thanks,

Mark

Mark D. Walter
Senior Programmer/Analyst
CCX, Inc.
mwalter@xxxxxxxxxx
http://www.ccxinc.com


                                                                           
             fkany@averittexpr                                             
             ess.com                                                       
             Sent by:                                                   To 
             rpg400-l-bounces@         CN=RPG programming on the           
             midrange.com              AS400/O=iSeries                     
                                       <rpg400-l@xxxxxxxxxxxx>             
                                                                        cc 
             03/29/2005 11:43                                              
             AM                                                    Subject 
                                       RE: Arrays                          
                                                                           
             Please respond to                                             
              RPG programming                                              
              on the AS400 /                                               
                  iSeries                                                  
             <rpg400-l@midrang                                             
                  e.com>                                                   
                                                                           
                                                                           






Printed and archived for future use......Thanks Rob.  I'll give it a shot,
depending how large the files are, my "old school" method of using work
files is pretty slow.

Frank






rob@xxxxxxxxx@midrange.com on 03/29/2005 09:54:17 AM

Please respond to RPG programming on the AS400 / iSeries
       <rpg400-l@xxxxxxxxxxxx>

Sent by:    rpg400-l-bounces@xxxxxxxxxxxx


To:    RPG programming on the AS400/iSeries <rpg400-l@xxxxxxxxxxxx>
cc:

Subject:    RE: Arrays


You mean like:
SLSPSN - Sales person file
SLSMGR    SLSPSN
100       100
100       110
100       120
100       200
100       300
700       700

CUSTMAST
CUSTNO    SLSPSN     YTDHG
12345     100     10000.95
12346     110       575.23
12347     700       999.47

LINEITEM
CUSTNO    ORDERNO   LINE#  CYLRNT
12345           1       1    1.95
12345           1       2    4.25
...

CREATE VIEW ROB/DOUGGIE
 (SLSMGR, SLSPSN, CUSTNO, YTDHG, ORDERNO, LINE#, CYLRNT)
 AS SELECT
 SLSPSNF.SLSMGR, SLSPSNF.SLSPSN,
 CUSTMAST.CUSTNO, CUSTMAST.YTDHG,
 LINEITEM.ORDERNO, LINEITEM.LINE#, LINEITEM.CYLRNT
 FROM
 (SLSPSNF
  LEFT OUTER JOIN CUSTMAST USING (SLSPSN))
  LEFT OUTER JOIN LINEITEM USING (CUSTNO)


SELECT SLSMGR, SLSPSN, MAX(CUSTNO), YTDHG, SUM(CYLRNT)
FROM ROB/DOUGGIE
GROUP BY SLSMGR, SLSPSN, YTDHG
ORDER BY SLSMGR, SLSPSN, YTDHG DESC

SLSMGR          SLSPSN   MAX ( CUSTNO )       YTDHG   SUM ( CYLRNT )
   100             100           12,345   10,000.95             6.20
   100             110           12,346      575.23                 -
   100             120                 -           -                -
   100             200                 -           -                -
   100             300                 -           -                -
   700             700           12,347      999.47                 -





CREATE TABLE ROB/SLSPSNF (SLSMGR INT NOT NULL, SLSPSN INT NOT NULL,
CONSTRAINT SLSATH_SLSPSN PRIMARY KEY (SLSPSN))

CREATE TABLE ROB/CUSTMAST (CUSTNO INT NOT NULL, SLSPSN INT NOT
NULL, YTDHG DEC ( 7, 2), CONSTRAINT CUSTMAST_CUSTNO PRIMARY KEY
(CUSTNO), CONSTRAINT CUSTMAST_SLSPSN FOREIGN KEY (SLSPSN)
REFERENCES ROB/SLSPSNF (SLSPSN) ON DELETE NO ACTION ON UPDATE NO
ACTION)

CREATE TABLE ROB/LINEITEM (CUSTNO INT NOT NULL, ORDERNO INT NOT
NULL, LINE# INT NOT NULL, CYLRNT DEC (7 , 2), CONSTRAINT
LINEITEM_PRIKEY PRIMARY KEY (ORDERNO, LINE#), CONSTRAINT
LINEITEM_CUSTNO FOREIGN KEY (CUSTNO) REFERENCES ROB/CUSTMAST
(CUSTNO) ON DELETE NO ACTION ON UPDATE NO ACTION)


Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com


--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.