RE: embedded sql session error in subfile

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

Douglas, 
        The LF is just fine in the SQL statement.  Typically, letting the
query optimizer pick all the access plans is a better option.   We (you)
need the SQLSTT value after the FETCH statement.  Then it can be looked up
in
http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/rzala/rzala
mst02.html .  This is the SQL code and message finder book.

I would presume that no data in the fetch fields means the fetch failed.
Then that means the open might have failed.  You need to check the value of
SQLSTT after each SQL operation, just to make certain its okay.  I wrote a
small subroutine in one of my programs that sends the specific SQL message
each time I get a non-zero error in SQLSTT, but for testing purposes you can
just do;
C       SQLSTT          DSPLY



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


-----Original Message-----
From: rpg400-l-bounces+mattt=wincofoods.com@xxxxxxxxxxxx
[mailto:rpg400-l-bounces+mattt=wincofoods.com@xxxxxxxxxxxx] On Behalf Of
Douglas W. Palme
Sent: Tuesday, July 26, 2005 3:07 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: embedded sql session error in subfile

I have a logical file which is built across four physical files.....they are

joined files....the sql statement works in the sql interactive mode, I do 
not see how the query optimizer is going to figure out from four separate 
physical files which logical file I am using, not to mention that the reason

I used a LF was to exclude certain records at the file level, instead of 
having the query system do it.


On Tue, 26 Jul 2005 16:56:41 -0400, Haas, Matt wrote
> Is SPECGASLF the name of the logical file? If so, part of your 
> problem is that you need to use the physical file name. The query 
> optimizer will figure out which logical it needs to use.
> 
> Matt
> 
> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Douglas W. Palme
> Sent: Tuesday, July 26, 2005 4:53 PM
> To: RPG Group
> Subject: embedded sql session error in subfile
> 
> I am trying desperately to get this embedded sql down and thought I had
> it 
> after the last go around, unfortunately I must be 1. dumber than I
> thought,
> 
> 2. losing my mind or 3. had too much gaterade today.  In any event,
>  I am
> 
> using the following code to extract the records from the logical file:
> 
> C     BLDSQL        BEGSR                                        
> C/EXEC SQL                                                       
> C+    DECLARE SQLCURSOR CURSOR                                   
> C+    FOR SELECT                                                 
> C+    ILNSOLDTO,                                                 
> C+    ICMNAME,                                                   
> C+    SUM(ILNEPRICE),                                            
> C+    SUM(ILNEUAVCST),                                           
> C+    SUM(ILNEPRICE) - SUM(ILNEUAVCST) AS MARGIN,                
> C+    (SUM(ILNEPRICE) - SUM(ILNEUAVCST)) / SUM(ILNEPRICE) AS MPCT
> C+    FROM SPECGASLF                                             
> C+    WHERE                            
> C+          ILNINVDATE > :STARTDATE AND
> C+          ILNINVDATE <= :ENDDATE     
> C+    GROUP BY                         
> C+             ILNSOLDTO,              
> C+             ICMNAME                 
> C+    ORDER BY                         
> C+             MPCT DESC               
> C/END-EXEC
> 
> I am then opening the cursor with the very next sql statement:
> 
> C/EXEC SQL          
> C+    OPEN SQLCURSOR
> C/END-EXEC
> 
> Then I set the indicators for my subfile:
> 
> C                   EVAL      *IN52 = *ON 
> C                   WRITE     SPECCTL     
> C                   EVAL      *IN52 = *OFF
> C                   DOU       RRN = 10
> 
> I am then using a fetch statement to extract the record, move them 
> into variables and then write it to the subfile, however when I was 
> in debug mode and viewed the variables at it was moving them into 
> the subfiles fields it wasn't passing any data.....so of course it 
> errors on a blank record.
> 
> Ideas, suggestions? or just tell me to get lost......
> 
> If you bought, 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 was hauled by a truck - somewhere, sometime.