RE: sql saga continues

You are right, I was doing it as a cursor to continue learning about 
cursors, using them with different select statements...



On Thu, 14 Jul 2005 09:22:51 -0500, Price, Brian wrote
> Doug,
> 
> Unless I am missing something, when you code an SQL cursor, you
> typically need an "open cursor" statement before you fetch from it 
> (as well as a "close cursor" statement when you are done).
> 
> That being said, it is not necessary to code a cursor when you expect
> only one result row (such as returning a simple count).  No fetch, open
> or close statement is necessary, either.  Like so...
> 
> 0046.00 C/EXEC SQL                      
> 0048.00 C+    SELECT COUNT(*)
> 0048.10 C+    INTO :TCOUNT  
> 0049.00 C+    FROM INVMARGIN           
> 0050.00 C+    WHERE ILNINVDATE >= :SDATE AND  
> 0051.00 C+          ILNINVDATE <= :EDATE      
> 0052.00 C/END-EXEC
> 
> Hope that helps
> 
> Brian Price
> Sr. Programmer/Analyst
> Jefferson Regional Medical Center
> 870.541.8770
> priceb@xxxxxxxx
> 
> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Douglas W. Palme
> Sent: Thursday, July 14, 2005 9:04 AM
> To: RPG Group
> Subject: sql saga continues
> 
> I spent the better portion of the night last night continuing to work
> with 
> the embedded sql code.....of course its a learning curve and at 
> times frustrating as hell but I am learning it slowly....
> 
> I have however run into a situation that after 4 hours I have not 
> been able to figure out.  I have created a very simple sqlrpgle 
> program that for right now is just to get the count of the records 
> and write it to a variable.
> 
> Sounds simple enough, however when running it in bebug mode it seems 
> to execute but when I F11 the variable to see its value, its all zeroes.
> 
> Here is the pertinent code:
> 
> 0029.03  ***********************************************************
> 0039.01  *       STANDALONE VARIABLES                               
> 0041.00  ***********************************************************
> 0041.03 DSDATE            S               D   DATFMT(*USA)          
> 0041.04 DEDATE            S               D   DATFMT(*USA)          
> 0041.05 DTCOUNT           S              6S 0                       
> 0041.06  ***********************************************************
> 0042.00  *       MAINLINE                                           
> 0043.00  ***********************************************************
> 0043.01 C     *USA          MOVE      '06/01/2005'  SDATE           
> 0043.02 C     *USA          MOVE      '06/30/2005'  EDATE           
> 0043.03 C                   EXSR      COUNTING                      
> 0043.29 C                   EVAL      *INLR = *ON                   
> 0043.30 C                   RETURN                                  
> 0043.31  ***********************************************************
> 0044.00  *       GET RECORD COUNT AND WRITE TOTAL TO TCOUNT VARIABLE
> 0045.00 C     COUNTING      BEGSR       
> 0046.00 C/EXEC SQL                      
> 0047.00 C+    DECLARE COUNTCURSOR CURSOR
> 0048.00 C+    FOR SELECT COUNT(*) FROM  
> 0049.00 C+    INVMARGIN WHERE           
> 0050.00 C+    ILNINVDATE >= :SDATE AND  
> 0051.00 C+    ILNINVDATE <= :EDATE      
> 0052.00 C/END-EXEC                      
> 0053.00 C/EXEC SQL  
> 0054.00 C+ FETCH COUNTCURSOR     
> 0055.00 C+ INTO :TCOUNT          
> 0056.00 C/END-EXEC                                   
> 0057.00 C                   ENDSR
> 0058.00  ************************
> 
> I did not see any problem with the cursor or the fetch statement, and
> since 
> the variable tcount is declared it should work....I also noticed 
> when I F11 on the :tcount it says that it cannot find the 
> variable...which I thought was strange but maybe its my brain having 
> one of its moments.
> 
> Any helpful suggestions would be appreciated.
> 
> -- 
> 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.
> 
> -- 
> 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.


If you bought, it was hauled by a truck - somewhere, sometime.