Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2009
    Location
    Pennsylvania
    Posts
    22

    Unanswered: need to get value of dynamic column name

    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?

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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:
    Code:
    FOR my_loop AS
        my_cursor CURSOR FOR
        SELECT ... DO
    
        INSERT INTO mytable VALUES ( my_cursor.p_field_to_use )
    END FOR;
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jun 2009
    Location
    Pennsylvania
    Posts
    22
    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.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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:
    Code:
    SET value = CASE p_field_to_use
                    WHEN 'COL1' THEN my_cursor.col1
                    WHEN 'COL2' THEN my_cursor.col2
                    ...
                 END;
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jun 2009
    Location
    Pennsylvania
    Posts
    22
    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?

    Quote Originally Posted by stolze
    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:
    Code:
    SET value = CASE p_field_to_use
                    WHEN 'COL1' THEN my_cursor.col1
                    WHEN 'COL2' THEN my_cursor.col2
                    ...
                 END;

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by stolze
    In your case, you will have to use a CASE expression:
    I doubt that will work either. Judging by this:

    Quote Originally Posted by davebert99
    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"
    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.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Jun 2009
    Location
    Pennsylvania
    Posts
    22
    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.

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by davebert99
    The actual problem is that I am reading a cursor
    This is still a solution. The problem statement would explain why you think you need to read that cursor.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Jun 2009
    Location
    Pennsylvania
    Posts
    22
    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

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by davebert99
    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.
    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.
    ---
    "It does not work" is not a valid problem statement.

  11. #11
    Join Date
    Jun 2009
    Location
    Pennsylvania
    Posts
    22
    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.

  12. #12
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    You can do the same with dynamic SQL. Take a look at dynamic SQL and prepare statement.

    Dave

  13. #13
    Join Date
    Jun 2009
    Location
    Pennsylvania
    Posts
    22
    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.


    Quote Originally Posted by dav1mo
    You can do the same with dynamic SQL. Take a look at dynamic SQL and prepare statement.

    Dave

  14. #14
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    So me are back to the CASE expression that I mentioned earlier... you may have to separate this for different data types, of course.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  15. #15
    Join Date
    Jun 2009
    Location
    Pennsylvania
    Posts
    22
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •