Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2005
    Posts
    16

    Unhappy Unanswered: Dynamic Field Names in Stored Procedure

    Hello,

    I have just started writing stored procedures for a new DB2 server install, and am having problems with using variables to define field names in a SELECT INTO statement. I want to take a field name out of a "field_name_to_use" column in one table, and use it to select data out of another, something like this:

    SET v_statement = 'SELECT MIN(' CONCAT v_source_timestamp_field CONCAT ') INTO v_source_end_time FROM ' CONCAT v_source_table;
    PREPARE S1 FROM v_statement;
    EXECUTE S1;

    where v_source_timestamp_field and v_source_table have been retrieved from another table.

    However, this gives me the error:

    SQL0104N An unexpected token "V_FIRST_END_TIME" was found following "". Expected tokens may include: "". SQLSTATE=42601

    I'm sure there is probably an easy way to do this, but I'm new to this, so if anyone can help I'd be really grateful!

    Many thanks,
    Ian

  2. #2
    Join Date
    Sep 2005
    Posts
    16

    Cool Ah, that's how!

    No worries actually, I've worked it out. In case anyone else doesn't know, you can do it like this:

    DECLARE C1 CURSOR WITH RETURN FOR v_rows;

    SET v_statement = 'SELECT MIN(' CONCAT v_source_timestamp_field
    CONCAT ') FROM ' CONCAT v_source_table;

    PREPARE v_rows FROM v_statement;
    OPEN C1;
    FETCH C1 INTO v_first_end_time;

Posting Permissions

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