Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2008
    Posts
    1

    Unanswered: Selecting from a Dynamic Table in SQL

    Hi all,

    I have created a procedure that selects from a table based on the value of another table concatenated with some text.

    So this code below should select all from the table FL_TIME_DIM(value)

    However I get an error that states

    SQL0104N An unexpected token "(" was found following "SELECT * FROM concat".
    Expected tokens may include: "WHERE". SQLSTATE=42601



    Code:
    CREATE PROCEDURE TEST_1 
    DYNAMIC RESULT SETS 1 
    LANGUAGE SQL  
    INHERIT SPECIAL REGISTERS  
    BEGIN  
    DECLARE V_SQL VARCHAR(64);  
    DECLARE V_TABLE VARCHAR(256);  
    DECLARE C_SQL CURSOR WITH RETURN FOR S_SQL;  
    SET V_TABLE = 'concat(''FL_TIME_DIM'',(select Value from table ))';  
    SET V_SQL = 'SELECT * FROM '||V_TAB||'FOR READ ONLY';  
    PREPARE S_SQL FROM V_SQL;  
    OPEN C_SQL; 
    END;
    I guess the issue is concatenating the table name and value together.

    If anyone could help me out it would be much appreciated.

    Thanks,
    apple

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What SQL statement do you want to execute?

    Your final value of V_SQL may be following,
    if "V_TAB" in "SET V_SQL = 'SELECT * FROM '||V_TAB||'FOR READ ONLY';" was replaced by "V_TABLE".
    V_SQL = 'SELECT * FROM concat('FL_TIME_DIM',(select Value from table ))FOR READ ONLY';

Posting Permissions

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