Using EXECUTE IMMEDIATE to create a volatile table

Database
Enthusiast

Using EXECUTE IMMEDIATE to create a volatile table

Hello Folks,

I am trying to pass a variable (in_project) into an SP that will be used to query a permanent table (PROJECT_VARS) and build a volatile table (TMP_PROJECT_VARS).  But the SELECT is not being interpreted as desired.  If I pass in the value  'ABC123'  in the variable in_project,  I want the select to pull the record where the  PROJECT = 'ABC123'.   Follows are two variations I have tried.  In (1), I get a runtime error, as shown below.  In (2), I tried to put in explicit quotes (and escape them), but my WHERE is being interpreted as PROJECT = ABC123 (as if ABC123 is a column name, not the value CONTAINED in the column named  PROJECT).  Any help would be appreciated.  Thanks!

REPLACE PROCEDURE LOAD_DSS_ENCS_PRC( IN in_project VARCHAR(10) )

STARTLABEL:

BEGIN

DECLARE claims_only INTEGER DEFAULT 0;

DECLARE sql_stmt VARCHAR(1000);

-- (1) this gives me error "expected something between '=' and "||".

SET sql_stmt =

'CREATE VOLATILE TABLE TMP_PROJECT_VARS AS (' ||

'SELECT * FROM PROJECT_VARS ' ||

'WHERE PROJECT = ' || in_project || ') WITH DATA';

EXECUTE IMMEDIATE sql_stmt;

-- (2) this gives me error "Column in_project not found in PROJECT_VARS"

SET sql_stmt =

'CREATE VOLATILE TABLE TMP_PROJECT_VARS AS (' ||

'SELECT * FROM MEDMINING.DM_MEDMINING_PROJECT_VARS ' ||

'WHERE PROJECT = '''' || in_project || '''') WITH DATA';

EXECUTE IMMEDIATE sql_stmt;

4 REPLIES
Senior Apprentice

Re: Using EXECUTE IMMEDIATE to create a volatile table

Too many quotes :-)

'WHERE PROJECT = ''' || in_project || ''') WITH DATA';

Dieter

Enthusiast

Re: Using EXECUTE IMMEDIATE to create a volatile table

Dieter,

Thanks alot for your very quick response.  Yes, your correction worked!  Have a nice weekend!

-Greg

Enthusiast

Re: Using EXECUTE IMMEDIATE to create a volatile table

One other question, can I see a volatile table and query it through my IDE?  Can I do a SHOW TABLE?

Thanks!

Senior Apprentice

Re: Using EXECUTE IMMEDIATE to create a volatile table

Of course you can do a "SHOW TABLE TMP_PROJECT_VARS;" as long as the session is logged on. But it's not allowed within an SP.

SHOW VOLATILE TABLE; returns the list of all VTs within your session.

Dieter