Selecting from a Dynamic Table in SQL

[appletastic]

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
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "DECLARE" was found following "RE V_SQL
VARCHAR(64)". Expected tokens may include: "<psm_semicolon>". LINE
NUMBER=1. SQLSTATE=42601

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;

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

Thanks,
apple

[appletastic]

Alright for the above code I added semicolons to the end of each line and that seemd to have solved that problem but arose another, now I get

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

I guess the issue is concatenating the table name and value together.

Any Ideas??

Thanks,

apple

[sakumar9]

I am not sure if you are using CONCAT propoerly.

[sakumar9]

# SET V_TABLE = 'concat(''FL_TIME_DIM'',(select Value from table ))'

You cannot have any SQL like SELECT * FROM contact('a', 'b').

Instead, you can have these values concated before the SELECT itself and try.

[appletastic]

# SET V_TABLE = 'concat(''FL_TIME_DIM'',(select Value from table ))'

You cannot have any SQL like SELECT * FROM contact('a', 'b').

Instead, you can have these values concated before the SELECT itself and try.

Thanks sakumar9,

Makes sense to Concat first, but I'm not exactely sure on how to do that. Any ideas, would be very helpful.

apple