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.