RE: embedded sql session error in subfile

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.