Re: embedded SQL....

Don't use the prepare statement...


     C                   EVAL      mystmt ='Select csnumb1, cat1, amt1,
amt2'
     C                                        +'from mylib.table1 left
outer'
     C                                        +'join mylib.table2 on'
     C                                        +'
table1.csnumb1=table2.csnumb2'
     C                                        +'and
table1.cat1=table2.cat2'




C/EXEC SQL
C+ declare c1 cursor for
C+ Select csnumb1, cat1, Sum(amt1), Sum(Amt2)
C+ from mylib.table1 left outer join mylib.table2 on
C+ table1.csnumb1=table2.csnumb2 and table1.cat1=table2.cat2
C/END-EXEC

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




Dow SQLCOD >= 0 And SQLCOD <> 100
C/EXEC SQL
C+ FETCH C1 INTO :Number, :Category, :Amount1, :Amount2
C/END-EXEC
 if SQLCOD < 0 Or SQLCOD = 100
    leave
 endif
  print results
enddo
*Inlr = *on
return





Michael Schutte
Work 614-492-7419
email  michael_schutte@xxxxxxxxxxxx


                                                                           
             "Jake M"                                                      
             <jakeroc@xxxxxxxx                                             
             m>                                                         To 
             Sent by:                  "RPG programming on the AS400 /     
             rpg400-l-bounces@         iSeries" <rpg400-l@xxxxxxxxxxxx>    
             midrange.com                                               cc 
                                                                           
                                                                   Subject 
             04/14/2006 02:53          embedded SQL....                    
             PM                                                            
                                                                           
                                                                           
             Please respond to                                             
              RPG programming                                              
              on the AS400 /                                               
                  iSeries                                                  
             <rpg400-l@midrang                                             
                  e.com>                                                   
                                                                           
                                                                           




Pro's,
I have two tables as shown below...
TABLE : 1
   CSNUMB1       CAT1             AMT1

   CHE1000    SI            100.00
   CHE1100    MR            200.00
   CHE1100    MS            300.00
   CHE1100    OR            100.00
   CHE1100    SI            400.00
   CHE1200    MS            500.00
   CHE1200    SI            100.00
   CHE1200    OR            600.00
   CHE1200    MR            200.00

TABLE : 2
   CSNUMB2      CAT2            AMT2

   CHE1000    MR            600.00
   CHE1000    MS            400.00
   CHE1000    OR            100.00
   CHE1000    SI             500.00
   CHE1100    SI             600.00
   CHE1100    OR            200.00
   CHE1100    MS            800.00
   CHE1100    MR            900.00
   CNA0038    OR            100.00
   CNA0038    MS            400.00
   CNA0038    MR            800.00
   CNA0161    OR            700.00

I am trying to build a query which would give me something like the
following...

   CSNUM        CAT           AMT2           AMT1
   CHE1000    MR             600.00         0.00
   CHE1000    MS             400.00         0.00
   CHE1000    OR             100.00         0.00
   CHE1000    SI               500.00          600.00
   CHE1200    MR             0.00            200.00
   CHE1200    MS             0.00            500.00
   CHE1200    OR             0.00            600.00
   CHE1200    SI              0.00            100.00

I tried high and low but in vain. I was wondering if there was any easy way
that I am missing. I am all open for a combination of queries too....Is
chain a better way to go for this problem?

Here is the code....

     D MYSTMT          S           5000A   varying


     C                   EVAL      mystmt ='Select csnumb1, cat1, amt1,
amt2'
     C                                        +'from mylib.table1 left
outer'
     C                                        +'join mylib.table2 on'
     C                                        +'
table1.csnumb1=table2.csnumb2'
     C                                        +'and
table1.cat1=table2.cat2'




     C/EXEC SQL
     C+ Prepare state from :mystmt
     C/END-EXEC



     C/EXEC SQL
     C+ DECLARE cursor SCROLL CURSOR FOR state
     C/END-EXEC



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




     C/EXEC SQL
     C+ FETCH.....
     C/END-EXEC

     C                   EVAL      *INLR = *ON
     C                   RETURN


Any help would be much appreciated.

Thanks in advance.

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