I have a query, that returns multiple tables, something like that:
SELECT TableName, DatabaseName +'.'+ TableName, ColumnName
FROM DBC.Columns
WHERE ColumnName = 'id'
And I need to loop through these tables by looking to the information stored in these tables, in order to get only specific tables.
I tried something like code below, using 'LOOP' and cursor, but it says that Query is invalid
(code have been taken from here):
DECLARE cursor_Tables CURSOR FOR
SELECT DatabaseName || '.' || TableName
FROM DBC.Columns
WHERE ColumnName ='id';
OPEN cursor_Tables;
label1:
LOOP
FETCH cursor_Tables into tbName;
IF (SQLSTATE ='02000') THEN
LEAVE label1;
END IF;
CASE WHEN (
SELECT COUNT(*)
FROM prd3_db_tmd.K_PTY_NK01
WHERE id = 0 ) > 0
THEN tbName
END
END LOOP label1;
CLOSE cursor_Tables;
END;
How can I actually deal with this problem? Do I need to use procedure in addition? DBMS is Teradata
You need a Stored Procedure because this is the only place where you can use a cursor in Teradata.
REPLACE PROCEDURE testproc()
DYNAMIC RESULT SETS 1
BEGIN
DECLARE tbName VARCHAR(257);
DECLARE SqlStr VARCHAR(500);
-- temporary table to store the result set
CREATE VOLATILE TABLE _vt_(tbName VARCHAR(257)) ON COMMIT PRESERVE ROWS;
-- your existing query to return the table name
-- Better use ColumnsV instead of Columns
FOR cursor_Tables AS
SELECT DatabaseName || '.' || TABLENAME AS tbName
FROM DBC.ColumnsV
WHERE ColumnName ='id'
DO -- prepare the dynamic SQL ...
SET SqlStr =
'insert into _vt_
select ''' || cursor_tables.tbName || '''
from ' || cursor_tables.tbName || '
where id = 0
having count(*) > 0;
';
-- ... and run it
EXECUTE IMMEDIATE SqlStr;
END FOR;
BEGIN -- return the result set
DECLARE resultset CURSOR WITH RETURN ONLY FOR S1;
SET SqlStr = 'SELECT * FROM _vt_;';
PREPARE S1 FROM SqlStr;
OPEN resultset;
END;
DROP TABLE vt;
END;