Question Regarding Stored Procedure

General
Enthusiast

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

Tags (1)
3 REPLIES
Enthusiast

Re: Question Regarding Stored Procedure

Try with TABLENAME =TNAME;

Try step by step and execute it.

Enthusiast

Re: Question Regarding Stored Procedure

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
Teradata Employee

Re: Question Regarding Stored Procedure

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;