How to use cursors to extract data from multiple tables
P粉141035089
2023-08-16 13:12:27
<p>I have a query that returns multiple tables, something like this: </p>
<pre class="brush:php;toolbar:false;">SELECT TableName, DatabaseName '.' TableName, ColumnName
FROM DBC.Columns
WHERE ColumnName = 'id'</pre>
<p>I need to loop through these tables by looking at the information stored in them in order to get only specific tables. </p>
<p>I tried the code below, using 'LOOP' and a cursor, but it says <code>Invalid query</code> (code comes from here): </p>
<pre class="brush:php;toolbar:false;">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;</pre>
<p>How should I solve this problem? Do I need to use additional stored procedures? The DBMS is Teradata. </p>
If this is SQL Server you can look at the following SQL Cursors, I edited the cursor declaration and the code in it Although they may be different from your requirements, I think you can modify them easily
You need a stored procedure because this is the only place where cursors can be used in Teradata.