RE: Arrays

[rob@xxxxxxxxx]

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





"Douglas W. Palme" <dpalme@xxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
03/29/2005 10:02 AM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


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

Subject
RE: Arrays






In some cases a salesman has the authority to view several salesman 
numbers 
and their respective customer numbers.

IE: WF has access to salesman 100, 110, 120, 200, and 300.


On Tue, 29 Mar 2005 08:46:39 -0600, Bruce Guetzkow wrote
> Doug:
> 
> <snip>
> I thought about creating a logical file for each salesman in customer
> order 
> but maintenance would be heavy, as each time we add a new salesman I
> have to 
> create a new logical file and then modify the code.
> 
> I suppose I could create a logical joined file with the pertinent 
> info and then key in on the salesman and then customer, but I am not 
> sure if that is the best answer.
> 
> I am open ears to anyone with any suggestions :)
> </snip>
> 
> Be careful what you ask for...you just might get it!   ;-)
> 
> I don't know the layout of your file, but I don't see why you'd need 
> to create a new logical every time you add a salesman.  If the key 
> to the file is CUSTOMER/SALESMAN, then your data records could be 
> retrieved by doing a SETLL for the Customer/Salesman combination 
> specified and do a READE to retrieve ONLY the records for that 
> combination.  If you want a different salesman, clear the subfile 
> and repeat the process with the new Customer/Salesman combo.  One LF 
> should be all that you need.
> 
> Am I missing something?
> 
> --Bruce Guetzkow
> 
> -- 
> 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.


--
Open WebMail Project (http://openwebmail.org)

-- 
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.