Archives of the TeradataForum

Message Posted: Thu, 21 May 2009 @ 09:57:27 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

A data definition statement (create, drop, alter etc) must be the last statement in a transaction.

When you exec a macro all of the queries within it are run as a single transaction. Hence your create table must be at the end. Yes, I know that isn't much use if you need to load data into it in your macro.

I don't know why the data definition must be last, but it just does. If it isn't, you will get the error you are reporting.

So what can you do?

Instead of using a volatile table, try using a global temporary table. Global Temporaries essentially work the same as volatile tables except the definition is permanent (the content, like volatile tables, is still private to your session).

You could also create the volatile table before running your macro.

Another possibility is to wrap the whole process in a Stored Procedure. In this model the procedure will create the volatile table then it could exec your macro.

I hope this gives you some ideas, I'm sure the list would be interested in what you finally decided to do.

Glenn Mc

	<Prev	Next>		<<First	<Prev	Next>	Last>>