RE: SQL Counts

["Tyler, Matt" <mattt@xxxxxxxxxxxxxx>]

Try something like this (warning not tested code at all).  Just remember
that a union query requires the results from all queries to match in
position, data type and length.  The record type keeps the counts ordered as
you expect them.

D RecCountDS     DS
D  RecType                      1A
D  RecCount                     10I 0   

        ...
C/EXEC SQL                 
C+ SET OPTION DATFMT = *USA
C+   ,ALWCPYDTA = *NO      
C+   ,CLOSQLCSR = *ENDMOD  
C+   ,DLYPRP = *YES        
C/END-EXEC                 


C/EXEC SQL                                                       
C+    DECLARE CSR CURSOR FOR
C+    SELECT 'A',COUNT(*) FROM CKEMPLLA WHERE EEAC01='Y'
C+     UNION
C+    SELECT 'B',COUNT(*) FROM CKEMPLLA WHERE EEAC01='Q' --(or another file)
       ...
       ...
C/END-EXEC                  

C/EXEC SQL                                                       
C+   OPEN CSR
C/END-EXEC                                                       

        ...
        DO LOOP UNTIL SQLSTT <> '00000'
C/EXEC SQL   
C+  FETCH FROM CSR INTO :RECCountDS

C/END-EXEC                           
        END DO LOOP                            
        ...

C/EXEC SQL                                                       
C+   CLOSE CSR
C/END-EXEC                                                       

Thank you,
Matt Tyler
WinCo Foods, LLC
mattt@xxxxxxxxxxxxxx


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Boman, James
Sent: Wednesday, July 13, 2005 1:21 PM
To: RPG programming on the AS400 / iSeries
Subject: SQL Counts

Does anyone know if it is possible to perform multiple counts within one
/EXEC SQL group? If it is possible, does someone have an example they
would share with me?

 

See example of single select below:

 

C/EXEC SQL                                                       

C+    SELECT COUNT(*) INTO:RECORDS FROM CKEMPLLA WHERE EEAC01='Y'

C/END-EXEC                                                       

 

 

TIA