Volatile table in stored procedure and V2R5

[jliang]

Hi,

I created store procedure under V2R41 with volatile table. It works very well, but after we upgrade the server to V2R512, I got problem when I compile it.

the code likes:

create volatile table test, NO LOG
( column1 char(10) not null
) unique primary index (div_line)
on commit preserve rows;

insert data here

select count(*) into :divline_cnt from test;

I got error message (3807) for select statements. the compiler think the table test does not exist.

Why it happens on V2R5? How can I fix it?

Thanks very much advance.

Jing

 

[CapsuleCorpJX]

Well first off, you're doing a unique primary index on a column that doesn't exist (div_line), it should be:

create volatile table test, NO LOG
( column1 char(10) not null
) unique primary index (column1)
on commit preserve rows;

Next Try specifying your database:

create volatile table database1.test
...

select count(*) into :divline_cnt from database1.test;

Or if that doesn't work,

Last, if all that doesn't really help, use dynamic sql to do the job instead (always specify database in dynamic sql):
Dyn Sql tut:

http://www.teradata.com/t/page/117793/