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.
Meet all of our AS/400 experts
View all AS/400 questions and answers
Start the conversation
0 comments