All Forums Database
stami27-2406 22 posts Joined 02/06
17 Feb 2006
Stored Procedures

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 FORSELECT dbname AS db,tablename AS tblFROM user_tables DOSET 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'); DOIF hcnt>0 THEN etc. ...END LOOP;This will be quited with the Message 3844 Reference to AccLogTbl not valid unlesssolitary etc.Is there a Workaround for somthing like that?Thanks a lotStami27

Barry-1604 176 posts Joined 07/05
20 Feb 2006

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.dbnameAND acl.TVMName = tbl.tablenameAND (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

stami27-2406 22 posts Joined 02/06
20 Feb 2006

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.By the time I will try your suggetion.Thank you very much for your suggetiongreetingsStamatios

You must sign in to leave a comment.