volatile tables use in subquery

Database

volatile tables use in subquery

I have a long report with several subqueries which repeat a lot of code. It seems I should be able to simplify and speed it up by creating temporary volatile tables. But I understand there are problems with what can be used inside subqueries - such as aliases. Can one use a volatile table inside a subquery?
2 REPLIES
Teradata Employee

Re: volatile tables use in subquery

Hi,

Volatile tables can be accessed in a subquery. For that make sure that you are using on commit preserve rows option (if you are not using Multi statement requests), so that data can be accessed after transaction.

Thanks
Ankit

Re: volatile tables use in subquery

When using this methodology, do not create the volatile table with a unique primary index.
In V2R6 at least I have known this to confuse the optimiser and produce product joins.
Sounds unlikely, but true.