RE: SQL Statements embedded in RPG

[Buck Calabro <buck.calabro@xxxxxxxxxxxxxxxxx>]

> JFinney wrote:
> 
>Say, for instance, in traditional RPG logic + 
>LF's, one would chain/reade (loop) a logical 
>and, for each record, go to another loop and 
>chain/reade based on a key (keeping it simple 
>to start, assume the unique key to be one 
>field from the original LF).
>
> What would the SQL statement look like to 
>do the equivalent of what's just been explained 
>in the previous paragraph ?

You can substitute SQL more or less directly in place of the native
constructs:

D LikeVar         S             20                                      
D TT#             S              7S 0                                   
D DescHdr         S             30                                      
D DescDtl         S             50                                      
D MatchOne        S                   Like(LikeVar)                     
D                                     Inz(*All'_')                      
D SqlCodHdr       s             10i 0 inz                               
D SqlCodDtl       s             10i 0 inz                               
                                                                        
 * Set up the host variable                                             
C                   Eval      LikeVar='TOLL%'                           
 * Pad the host variable with "match a single character"                
C     LikeVar       Cat       MatchOne:0    LikeVar                     
                                                                        
C* Define the cursor (sort of like an F spec)                           
C/Exec SQL Declare TTHCrsr Cursor for                                   
C+ select ttnum,ttdesc from tthdr where ttdesc like :LikeVar order by 1 
C/End-Exec                                                              
                                                                        
C/Exec SQL Declare TTDCrsr Cursor for                                   
C+ select ttdata from ttdetail where tt# = :TT#                         
C/End-Exec                                            
                                                      
C* Open the header file (sort of like OPEN)           
C/Exec SQL Open TTHCrsr                               
C/End-Exec                                            
                                                      
C* Read the header records                            
C                   Eval      SqlCodHdr=0             
C                   DoU       SqlCodHdr<>0            
C* (sort of like READE)                               
C/Exec SQL Fetch TTHCrsr into :TT#, :DescHdr          
C/End-Exec                                            
C                   Eval      SqlCodHdr=SqlCod        
C                   If        SqlCodHdr=0             
                                                      
C* have a header record, get the corresponding detail 
C* Open the detail file (sort of like OPEN)           
C/Exec SQL Open TTDCrsr                               
C/End-Exec                                            
                                                      
C* Read the detail records                            
C                   Eval      SqlCodDtl=0      
C                   DoU       SqlCodDtl<>0     
C* (sort of like READE)                        
C/Exec SQL Fetch TTDCrsr into :DescDtl         
C/End-Exec                                     
C                   Eval      SqlCodDtl=SqlCod 
C                   If        SqlCodDtl=0      
C* your detail processing here                 
C                   EndIf                      
                                               
C* Close the cursor (sort of like CLOSE)       
C/Exec SQL Close TTDCrsr                       
C/End-Exec                                     
                                               
C                   EndDo                      
                                               
C                   EndIf                      
                                               
C                   EndDo                      
                                               
C* Close the cursor (sort of like CLOSE)       
C/Exec SQL Close TTHCrsr               
C/End-Exec                             
                                       
C                   Eval      *INLR=*ON

Although you can use SQL this way, you may want to re-think your design.
SQL can do some cool things that native cannot, so a simple substitution may
not take advantage of the capabilities that SQL offers.

Buck Calabro
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---