Using value in variable as part of SQL

[HFChristie]

I've got a question about using a variable to contain a segment of an SQL statment, and then using it in an Informix Procedure.

For instance;

DEFINE col_values char(255);

LET col_values = "'ABC', 'DEF', '123'"
SELECT MAX(col_a)
FROM table_a
WHERE col_b IN (col_values)

What I'm trying to do is have the SQL statement above evaluated as;
SELECT MAX(col_a)
FROM table_a
WHERE col_b IN ('ABC', 'DEF', '123')

My actual procedure is building the content of col_values, not using a literal.

Does anyone know if there is there a way to do this?

 

[olded]

Hi:

Unfortunately, the Informix SPL is rather braindead. The FOREACH command implicitly declares and opens a cursor, but there's no facility for preparing a dynamic cursor similar to Informix 4GL.

You might do something like this:

# untested
create procedure ret_maxvalue(col_value char(3))
RETURNING INTEGER;

define ret_value integer;

let ret_value = (SELECT MAX(col_a) FROM table_a
WHERE col_b = col_value);

return ret_value;
end procedure;

Now, whatever calls ret_maxvalue has to call it for each

"ABC", "DEF", and "123" - not a good situation. If you always know there's 3 arguments, you might create an SP with 3 arguments and to this:


let ret_value = (SELECT MAX(col_a) FROM table_a
WHERE col_b = col_value1 or col_b = col_value2 or col_b = col_value3);

Still not very dynamic.

Wish I had better info for you.

Regards,

Ed

 

[HFChristie]

Thanks.

I was afraid of that.