Hi, I'm 'newbie' to Teradata but with some experience in Oracle PL/SQL. I would like to do samthing like this: (Read a User-Table with Table names and get some Information from DBC.AccessLog-Table) CREATE PROCEDURE .... ..... FOR RowP1 AS c_c1 CURSOR FOR SELECT dbname AS db ,tablename AS tbl FROM user_tables DO SET hdb = RowP1.db; SET htbl= RowP1.tbl; FOR RowP1 AS c_c1 CURSOR FOR SELECT coun(*) AS hcnt FROM dbc.AccessLog where databasename=:hdb AND TVMName=htbl AND (AccessType='S' OR AccessType='U');
DO IF hcnt>0 THEN etc. ...
This will be quited with the Message 3844 Reference to AccLogTbl not valid unless solitary etc.
Is there a Workaround for somthing like that? Thanks a lot Stami27
One option is to join the two tables together in your first cursor:
SELECT dbname AS db ,tablename ,count(*) FROM user_tables AS tbl, dbc.AccessLog AS acl where acl.databasename = tbl.dbname AND acl.TVMName = tbl.tablename AND (AccessType='S' OR AccessType='U');
If this does not work for you because you have other SQL statments that cause the 3844 message, then I would recommend that you first copy the dbc.accesslog table into a volatile or global temporary table and issue a COMMIT (make sure you define the temp table as preserving rows on COMMIT). Then, you can run against the volatile or global temporary table without getting the 3844 message.
Thanks Barry, I have found a Solution with the Dynamic SQL using the INSERT..SELECT statement. This is my Solution:
SET hselt1='INSERT INTO sgia2708.AccLogTbl SELECT * FROM dbc.AccLogTbl where DatabaseName='; SET hselt2=' and TVMName='; SET hselt3=' and (AccessType = '; SET hselt4=' OR AccessType='; SET hselt5=' AND logondate >'|| DATE - hNumberDates||';'; SET hq='27'XC; SET hselect1 = hselt1 ||hq||hdb ||hq || hselt2 ||hq||htbl||hq || hselt3 ||hq||'S' ||hq || hselt4 ||hq||'U' ||hq||')' || hselt5; BEGIN call dbc.SysExecSQL(:hselect1); END;
The Variables hdb, htbl coms from my first FOR-Cursor and the number dates as input variable.