Selecting from a Dynamic Table in SQL

appletastic · #1 (**permalink**) 10-06-08, 10:26

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

tonkuma · #2 (**permalink**) 10-07-08, 11:08

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';

Internet Services

Web Development

Digital Marketing

Consumer Tech