I'm running DB2 z/os 9.1.5 and I'm writing a SQL PL program that is using a cursor and needs to dynamically assign different cursor columns to a variable. In one loop I might need to set v_temp = cur1.this_field and in another loop I might need to assign v_temp = cur1.that_field. I need to have this controlled by a passed control record which will indicate the field name to save.

In Oracle I would have done it this way:

set v_field = p_field_to_use; -- example 'cur1.this_field'
set v_data = &p_field_to_use;
or
insert into mytable values (&p_field_to_use);
-- ampersand means don't use field, use the contents

Since there is no equivalent in DB2 I tried using dynamic sql, but it doesn't like the cursor variable.

-- example p_field_to_use = 'cur1.this_field'
set v_data = 'insert into mytable values (' || p_field_to_use || ')';
execute immediate v_data;
SQL0206N "cur1.this_field" is not valid in the context where it is used. SQLSTATE=42703

I have tried many, many variations on this, but have not found a work-around for the Oracle substitution variable or a way to dynamically return different columns values based on a passed parameter.

Any idea?

Am I right to assume that you have some cursor over a result set and that you want to get some value from the current row the cursor is positioned on and then insert the value into table "mytable"? You can do that in DB2 with standard SQL like this:
You can do whatever you want instead of the FOR loop, e.g. use a regular cursor and fetch the values from the cursor's row into a variable.

In short, the redirection doesn't appear to be necessary.

The field to use will be passed inside the variable "p_field_to_use"
p_field_to_use = "last_name"
p_field_to_use = "first_name"
p_field_to_use = "order_total"

So I need to dynamically use the value of the cursor field that is passed by the control variable.

Ok, I thought your "dynamic column name" referred to the column name that DB2 generates for expressions in the SELECT list where no column alias was assigned.

In your case, you will have to use a CASE expression:

Yes, I can do it with a CASE statement, but I might have a hundred or more columns. I was looking for something more elegant... What about the SQLDA? Can that be used used somehow in SQL PL?

I doubt that will work either. Judging by this:

the columns can be of incompatible datatypes. If davebert99 stated the actual problem, instead of the "elegant solution", we'd have a better chance of finding the right answer.

The actual problem is that I am reading a cursor that will contain 50 or columns and I need to be able to return the value of any column based on a column name contained in a column read from another cursor - which is the control table.

set v_temp = cur1.(cur2.field_name));

If cur2.field_name = 'LAST_NAME' then I need to v_temp = cur1.last_name
If cur2.field_name = 'FIRST_NAME' then I need to v_temp = cur1.first_name

Yes, this can be done with a very big case statement.

This is still a solution. The problem statement would explain why you think you need to read that cursor.

If I knew why the code below fails then I would know a lot more about the way DB2 (v9.1.5) does dynamic sql.

The code below is just a test snippet to illustrate the problem. In reality 'v_tmp' would be a dynamically changing passed field name. The Test_Table has a single varchar column.

CREATE PROCEDURE P_TEST ( )
VERSION V1
LANGUAGE SQL
begin
declare v_sql varchar(254);
declare v_tmp varchar(32);
set v_tmp = 'Test String';
insert into test_table values (v_tmp); -- works, no problem
set v_sql = 'insert into test_table values (v_tmp)';
execute immediate v_sql; -- error
-- SQL0206N "V_TMP" is not valid in the context where it is used.
-- SQLSTATE=42703
end

It's not about DB2, it's about basic programming concepts. "v_tmp" is the name of a variable in the stored procedure source code. Once the SP is compiled, all symbolic names lose their meaning, being converted into memory pointers. When you ask DB2 to insert something called "v_tmp" it has no idea what it might have been.

It's like wearing a name badge on your lapel while speaking on the phone, and wondering that the person on the other end has to ask who you are although your name is clearly written on the badge.

I can do it one way in Oracle as such:

v_temp := 'c1.last_name'; -- (actual passed into routine) c1=cursor
v_sql := 'select '||v_temp||' from dual';
execute immediate v_sql into v_results;

I'm just trying to figure out how to do it in DB2.

You can do the same with dynamic SQL. Take a look at dynamic SQL and prepare statement.

Dave

Unfortunately you can't. The dynamic SQL doesn't work with cursor variables.

SQL0206N "{sql variable}" is not valid in the context where it is used.

So me are back to the CASE expression that I mentioned earlier... you may have to separate this for different data types, of course.

Discovered a work-around for those who care....

The Execute Immediate or Prepare/Execute does not like cursor variables. So a way around this is to write your cursor out as a table or global temporary table. Then you can open that table again as a cursor (if not already defined as cursor), but when you want to refer to a cursor variable/column, refer to the table column instead. A little ugly, but it works.

-- a sample code outline (incomplete) is here
declare c_cur1 cursor with return for v_sql2;
declare global temporary table session.temp_table as
(select a.field1, a.field2, a.field3, b.field4, b.field5, etc..
from table1 a, table2 b
where a.somefield = b.somefield)
definition only on commit preserve rows;
-- the Z will only work if you use 'definition only'
-- now insert your data from same select
insert into session.temp_table values
(select a.field1, a.field2, a.field3, b.field4, b.field5, etc..
from table1 a, table2 b
where a.somefield = b.somefield);

-- later is your code
-- v_column contains desired column to return, example 'field1'
set v_sql = 'select char('||v_column||') from session.temp_table where key_field = ?';
prepare v_sql2 from v_sql;
open c_cur1 using v_key_data;
fetch c_cur1 into v_data; -- fetch first row (only will be one)
close c_cur1;
-- v_data now contains your dynamic column data
-- v_column contains desired column to return, example 'field1'
set v_sql = 'select char('||v_column||') from session.temp_table where key_field = ?';
prepare v_sql2 from v_sql;
open c_cur1 using v_key_data;
fetch c_cur1 into v_data; -- fetch first row (only will be one)
close c_cur1;
-- v_data now contains your dynamic column data