RE: embedded sql

When I run the query in interactive mode it runs fine and returns the 
correct number of results....however after the fourth record is read in the 
program it generates an sqlstt code of '01004' which appears to be some kind 
of sort error.  Here is the code:

C     NOCATEGORY    BEGSR                            
                                                     
C/EXEC SQL                                           
C+    DECLARE NOCAT CURSOR                           
C+    FOR SELECT                                     
C+        ILNSOLDTO,                                 
C+        ICMNAME,                                   
C+        ILNSLSMN,                                  
C+        SUM(ILNEPRICE),                            
C+        SUM(ILNEUAVCST),                           
C+        SUM(ILNEPRICE) - SUM(ILNEUAVCST) AS MARGIN 
C+    FROM CLASSCATLF                                
C+    WHERE                                          
C+        ILNINVDATE >= :DSDATE AND                  
C+        ILNINVDATE <= :DEDATE AND                  
C+        ITMCLASS = :DCLASS                         
 C+    GROUP BY                        
 C+        ILNSLSMN, ILNSOLDTO, ICMNAME
 C+    ORDER BY                        
 C+        ILNSLSMN,                   
 C+        MARGIN DESC                 
 C/END-EXEC                            
                                       
 C/EXEC SQL                            
 C+    OPEN NOCAT                      
C/END-EXEC                                                   
C                   WRITE     HEADINGS                       
C                   DOW       sqlstt = SQLSTATEOK            
C/EXEC SQL                                                   
C+                                                           
C+    FETCH NOCAT                                            
C+    INTO :DCUSTNO, :DNAME, :DSLS, :DSALES, :DCOST, :DMARGIN
C/END-EXEC                                                   
C                   IF        *IN99 = *ON                    
...




On Wed, 26 Oct 2005 10:20:24 -0500, Holden Tommy wrote
> You can check the SQLCOD or the SQLSTT values to determine if the
> statement was successful or not.
> 
> SQLCOD=*zeros = success
> SQLCOD=100 = end of cursor(EOF)
> 
> For SQLSTT values you can find most of them at 
> http://wiki.midrange.com/index.php/SQLSTATE_Constants
> 
> Thanks,
> Tommy Holden
> 
> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Douglas W. Palme
> Sent: Wednesday, October 26, 2005 10:09 AM
> To: RPG Group
> Subject: embedded sql
> 
> When using embedded SQL in RPG, I need to know when there are no more
> records to process from the query, I know that I can use a count(*)
> statement but that seems completely inefficient.
> 
> Is there not an SQL variable that I can use to determine that the SELECT
> INTO statement has no more records to process?
> 
> I checked the embedded SQL programming guide but did not find 
> anything relevant to RPG
> 
> If you bought it, it was hauled by a truck - somewhere, sometime.
> 
> -- 
> 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, it was hauled by a truck - somewhere, sometime.