|
|
|
Junior Member
 
Group: Forum Members
Last Login: 8/1/2006 12:37:00 PM
Posts: 22,
Visits: 1
|
|
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.
...
END LOOP;
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
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 6/9/2008 2:55:55 PM
Posts: 185,
Visits: 2
|
|
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
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 8/1/2006 12:37:00 PM
Posts: 22,
Visits: 1
|
|
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 suggetion
greetings
Stamatios
|
|
|
|