RE: Use SQL instead of RPG?

[Michael_Schutte@xxxxxxxxxxxx]

I was going to suggest using JOIN instead of UNION again.  Maybe I'm off
base but I've had luck with this option.


SELECT
      SUM(A.UNNO), SUM(B.UNNO), SUM(C.UNNO)
      FROM MYLIB/BEF01 A
      JOIN MYLIB/BEF01 B ON A.CONO = B.CONO  AND A.UNNO = B.UNNO
      JOIN MYLIB/BEF01 C ON A.CONO = C.CONO  AND A.UNNO = C.UNNO


This select statement is just totaling the unit numbers, joining the same
file using the same key.  This will actuall result in the same values. but
I think this can get you going in the right direction.



Michael Schutte



                                                                           
             rob@xxxxxxxxx                                                 
             Sent by:                                                      
             rpg400-l-bounces@                                          To 
             midrange.com              RPG programming on the AS400 /      
                                       iSeries <rpg400-l@xxxxxxxxxxxx>     
                                                                        cc 
             05/18/2006 10:03                                              
             AM                                                    Subject 
                                       RE: Use SQL instead of RPG?         
                                                                           
             Please respond to                                             
              RPG programming                                              
              on the AS400 /                                               
                  iSeries                                                  
             <rpg400-l@midrang                                             
                  e.com>                                                   
                                                                           
                                                                           




CREATE TABLE QTEMP/STEEMA
(PRODA CHAR (1 ),
 AMTA DEC (5 , 0),
 PRODB CHAR (1 ), PRODC CHAR (1 ))
INSERT INTO QTEMP/STEEMA VALUES('A', 1, 'B', 'C')
INSERT INTO QTEMP/STEEMA VALUES('D', 2, 'E', 'F')
INSERT INTO QTEMP/STEEMA VALUES('A', 3, 'D', 'C')

select proda as prod, amta from qtemp/steema
union select prodb as prod, amta from qtemp/steema
union select prodc as prod, amta from qtemp/steema
PROD    AMTA
 A         1
 D         2
 A         3
 B         1
 E         2
 D         3
 C         1
 F         2
 C         3

with t1 as(
select proda as prod, amta from qtemp/steema
union select prodb as prod, amta from qtemp/steema
union select prodc as prod, amta from qtemp/steema)
select prod, sum(amta) as total from t1
group by prod
order by prod
....+....1....+....2....+....3....+....4....+..
PROD                                      TOTAL
 A                                            4
 B                                            1
 C                                            4
 D                                            5
 E                                            2
 F                                            2

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





steema@xxxxxxxxxxxxx
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
05/18/2006 09:43 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: Use SQL instead of RPG?






When I try to select on ADDitional options it says that is not allowed
when using UNION.

> Yes you can do the totalling.
>
> Rob Berendt
> --
> Group Dekko Services, LLC



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