All Forums General
mitsukiefi 10 posts Joined 08/12
02 Oct 2014
Question Regarding Stored Procedure

Dear All
I cannot manage to get this stored procedure to work. I still get a compile error: SPL5000:W(L8), E(3807):Object 'TNAME' does not exist.

-- DB = DWH_TTST_MSTR_MAIN
-- TNAME = Cust_Customerid_Upl_TZHKICH1
-- VTNAME = Cust_Customerid_Upl
-- 
-- Check if TNAME exists, if yes, create volatile table VTNAME, if no do nothing

REPLACE PROCEDURE BLI_CheckForUserTable(IN DB VARCHAR(32), IN TNAME VARCHAR(64), IN VTNAME VARCHAR(64))
BEGIN
DECLARE VAR1 INTEGER;

-- SELECT TableKind FROM dbc.TablesVX WHERE databasename = DATABASE AND TableName = 'Cust_Customerid_Upl_TZHKICH1; 
SELECT COUNT(*) INTO VAR1 FROM dbc.TablesVX WHERE DataBaseName = DB AND TABLENAME = '' || TNAME || '';

IF (VAR1 > 0) THEN
-- CREATE VOLATILE TABLE Cust_Customerid_Upl AS (SELECT * FROM Cust_Customerid_Upl_TZHKICH1) WITH DATA ON COMMIT PRESERVE ROWS;
CREATE VOLATILE TABLE VTNAME AS (SELECT * FROM TNAME) WITH DATA ON COMMIT PRESERVE ROWS;
END IF;

END

Any help is highly appreciated.

 

Thanks in advance,

  Christoph

Raja_KT 1246 posts Joined 07/09
03 Oct 2014

Try with TABLENAME =TNAME;
Try step by step and execute it.
 
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

mitsukiefi 10 posts Joined 08/12
03 Oct 2014

It seems this code is working (note that I removed the first input parameter):

REPLACE PROCEDURE BLI_CheckForUserTable
	(IN t_name VARCHAR(64),
 	 IN vt_name VARCHAR(64)
 	)
BEGIN
	DECLARE var1 SMALLINT;
	DECLARE SQL_TEXT VARCHAR(10000);
	
	SELECT COUNT(*) INTO :var1
	FROM dbc.TablesVX
	WHERE TABLENAME = : t_name
	;

	IF (var1 = 1) THEN
		BEGIN   
			SET SQL_TEXT = 'CREATE VOLATILE TABLE ' !! vt_name !! ' as (SELECT * FROM ' !! t_name !! ') WITH DATA ON COMMIT PRESERVE ROWS';    
            CALL DBC.SYSEXECSQL(:SQL_TEXT);          
         END;
	END IF;
	
END

 

frnewbrough 41 posts Joined 03/08
03 Oct 2014

Here is a revison that would improve it a bit and maintain your first parm:

REPLACE PROCEDURE BLI_CheckForUserTable
    (IN d_name VARCHAR(64),
     IN t_name VARCHAR(64),
     IN vt_name VARCHAR(64)
    )
BEGIN
    DECLARE var1 SMALLINT;
    DECLARE SQL_TEXT VARCHAR(10000);
     
    SELECT COUNT(*) INTO :var1
    FROM dbc.TablesVX
    WHERE 
    TRIM(TABLENAME) = : t_name
    AND
    TRIM(DatabaseName)=:d_name
        AND
    TableKind='T'
    ;
 
    IF (var1 =1) THEN
        BEGIN  
            SET SQL_TEXT = 'CREATE VOLATILE TABLE ' || vt_name || ' as (SELECT * FROM ' ||d_name||'.'|| t_name || ') WITH DATA ON COMMIT PRESERVE ROWS;';    
         CALL DBC.SYSEXECSQL(:SQL_TEXT);          
         END;
    END IF;
     
END


CALL BLI_CheckForUserTable('YourDb','YourTb','TestTable');
SELECT * FROM TestTable;

 

You must sign in to leave a comment.