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.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataCenter

Close