You don't do anything special (just basic string concatenation). However, the SQL statement that you create is syntactically incorrect if IN_LOB is NULL. Other than that, it should work. What you could do is to modify the procedure (for testing purposes only) and return the generated statement as OUT parameter (remove the PREPARE/OPEN statements as well). Then you can verify the SQL statements inside the procedure.
Originally Posted by nivin
I'm not sure if this DECLARE statement must come after the PREPARE. In case DB2 complains about that, move it further down and nest it into a BEGIN ... END block (because it has to be at the beginning of such a block.
DECLARE selectCursor CURSOR WITH RETURN FOR sqlPrepare;
You can simplify this a bit to:
SET dynamicString = 'PG.PLAN_CODE =' CONCAT '''' CONCAT IN_PLAN_CODE CONCAT '''';
You can also use || instead of CONCAT. Both are synonyms. It depends on personal preferences which one you use. I like || better because it cannot be confused as being part of the string to be build.
SET dynamicString = 'PG.PLAN_CODE = ''' CONCAT IN_PLAN_CODE CONCAT '''';
Another question here is: you test whether IN_LOB is NULL. But what happens in IN_PLAN_CODE is NULL? You should test for that as well!
Here you have a problem: the first token after the keyword WHERE will be "AND". That is not allowed, of course.
SET conditionString='AND (PG.LOB IS NULL OR PG.LOB='')'CONCAT dynamicString;[/COLOR]
IBM DB2 Analytics Accelerator
IBM Germany Research & Development