× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  • Subject: RE: embeded SQL question
  • From: "Phil" <sublime78ska@xxxxxxxxx>
  • Date: Wed, 13 Jun 2001 19:17:12 -0400
  • Importance: Normal

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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.