How can I open a declared cursor that is a dynamically prepared select statement behind it (different number of parameters for the using clause)?

I built the open statement as a variable, now I need to 'run' it..
(i.e.: depending on the criteria entered, the where clause of the prepared select statement can have 1 to 5 variables.. Can have any combination of the 5 criteria, so if a user wants all programs, there would be no 'and program = ?' clause. The full where clause is:where fy = ? and quarter = ? and program = ? and facility = ? and agency_code = ?. But as mentioned, it could have just 'where fy = ?'

Does anyone have any ideas?


Thanks!

Hi,

I think that can you use built-in functions ARG_VAL(), NUM_ARGS().
Exemp:
DEFINE pa_args ARRAY[5] OF CHAR(10),
i SMALLINT
FOR i = 1 TO NUM_ARGS()
LET pa_args[i] = ARG_VAL(i)
END FOR


Gustavo.

Hi,

First,I think you need to form the null query strings like "1=1".
Secondly,Add more query string like "and ?" if the input condition is not blank.
Thirdly,prepare the sql statement using the query stings you created.

I think that will work.

You have to use the CONSTRUCT statement to dynamically prepare the WHERE clause. Then prepare that statement to declare the cursor.