Embed a dynamic IM clause 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
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