RE: embeded SQL question

["Phil" <sublime78ska@xxxxxxxxx>]

Yes, you can prepare the statement first.

 

This:

 

C/EXEC SQL DECLARE MYCUR CURSOR FOR                
C+  SELECT   field1a ,  field2a ,  field3a,  field4a, field5a          
C+           field5a,  field1b,  field2b, field3b, field4b          
 C+  FROM     FileA , FileB                          
C+  WHERE                
C+           field1a  = field1b AND                    
C+           field2a  = field2b AND                    
C+           field3a  = field3b                        
C/END-EXEC                                          

Becomes this:

 

C                   select

C                   when             whatever

C                   eval               @select = ‘SELECT field1a . . . ‘ etc.

C                   when             a different condition

C                   eval               @select = ‘SELECT field1a. . . ‘ etc

C                   when             another different condition

C                   eval                @select = ‘SELECT field1a . . . ‘ etc

C                   endsl

 

C/exec sql

C+ prepare @select from :@select

C/end-exec

 

C/exec sql

C+ declare @cursor cursor for @select

C/end-exec

 

Hth,

Phil

 

 

 

 

-----Original Message-----
From: owner-rpg400-l@midrange.com [mailto:owner-rpg400-l@midrange.com]On Behalf Of Refaie.Heba@khb.hu
Sent: Wednesday, June 06, 2001 9:17 AM
To: RPG400-L@midrange.com
Subject: embeded SQL question

 

Hi Group

     I don't use embeded SQL statement in my RPG program frequently,  but I had to use them this time due to the lack of logical file. what is the best way to do the following

I have to filter the selected records from two files fileA & fileB according to some conditions (coming to me as parameter from the caller)
so I have the basic select statement joing the two files

C/EXEC SQL DECLARE MYCUR CURSOR FOR                
C+  SELECT   field1a ,  field2a ,  field3a,  field4a, field5a          
C+           field5a,  field1b,  field2b, field3b, field4b          
 C+  FROM     FileA , FileB                          
C+  WHERE                
C+           field1a  = field1b AND                    
C+           field2a  = field2b AND                    
C+           field3a  = field3b                        
C/END-EXEC                                          

and I have to filter the records according to input parameter in the following sequence

Parm1 if not eq *blanks

C/EXEC SQL DECLARE MYCUR CURSOR FOR                
C+  SELECT   field1a ,  field2a ,  field3a,  field4a, field5a          
C+           field6a,  field1b,  field2b, field3b, field4b          
 C+  FROM     FileA , FileB                          
C+  WHERE                        
C+           field1a  = field1b AND                    
C+           field2a  = field2b AND                    
C+           field3a  = field3b  and field5a  = : parm1                    
C/END-EXEC                                      

Parm2 if it is not equal blanks

C/EXEC SQL DECLARE MYCUR CURSOR FOR                
C+  SELECT   field1a ,  field2a ,  field3a,  field4a, field5a          
C+           field6a,  field1b,  field2b, field3b, field4b          
 C+  FROM     FileA , FileB                          
C+  WHERE                      
C+           field1a  = field1b AND                    
C+           field2a  = field2b AND                    
C+           field3a  = field3b  and    field5a = : parm1    and field6a  = : parm2                    
C/END-EXEC                                      
 
and so on for the rest of parameters
Is there a smart method that saves me from writing the sql statement many times with different where conditions

Thanks in advance and sorry for the long mail
Heba