Q
Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Embed a dynamic IM clause in a COBOL program

Is there a way to have a dynamic IN clause embedded in a COBOL program?

Is there a way to have a dynamic IN clause embedded in a COBOL program?

Select custname from custtable 
where custno in (:cno1, :cono2, ....)

Can have one to many cnos; it varies based on number passed to program.

I know about the parameter markers and prepare statement , but I can't figure out how to employ with the IN clause. Can you help me out?

Try building a dynamic select statement with the IN values based on which cno parameters are specified. Then prepare the statement and open the cursor.

I don't know COBOL, but here is some free format rpg code that hopefully you can interpret:

inList = %char(cno1);                                             
 if cno2 > 0;                                                       
   inList = %trim(inList) + ', ' + %char(cno2);                     
   if cno3 > 0;                                                     
     inList = %trim(inList) + ', ' + %char(cno3);                   
     if cno4 > 0;                                                   
       inList = %trim(inList) + ', ' + %char(cno4);                 
       if cno5 <> 0;                                               
         inList = %trim(inList) + ', ' + %char(cno5); 
           // add more as needed 
       endif;                                                       
     endif;                                                         
   endif;                                                           
 endif;                                                             
                                                                    
 SQLStatement = 'select custname from custtable where customer_no in (' +  %trim(inList) + ')'; 

 * Prepare the statement that you have built 
C/EXEC SQL                                                                   
C+ PREPARE custStatement FROM :SQLStatement                                 
C/END-EXEC                                                                   
 *                                                                           
 * Declare the cursor     
C/EXEC SQL                                                                   
C+ DECLARE C1 CURSOR FOR custStatement                                       
C/END-EXEC                                                                   
 *                                                                           
 * Open the cursor.                                                     
C/EXEC SQL                                                                   
C+   OPEN c1                                                                 
C/END-EXEC SQL                                                               

 *  Fetch the row 
C/EXEC SQL                                                                   
C+   FETCH c1 into :customerName                                             
C/END-EXEC
This was last published in September 2005

Dig Deeper on DB2 UDB (universal databases)

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataCenter

Close