CREATE MULTISET VOLATILE TABLE 0 row processed with ON COMMIT PRESERVE ROWS

General
Enthusiast

CREATE MULTISET VOLATILE TABLE 0 row processed with ON COMMIT PRESERVE ROWS

so here is a newbie question: I'm trying to create volatile table from an existing table, I checked some info in the forum, so yes, I added ON COMMIT PRESERVE ROWS, but still I got 0 row processed. No record returned. Below is the code, please advice. Thanks!

CREATE MULTISET VOLATILE TABLE tmp

AS

(

SELECT TOP 100 x FROM table_01

)

with data

ON COMMIT PRESERVE ROWS;

3 REPLIES
Enthusiast

Re: CREATE MULTISET VOLATILE TABLE 0 row processed with ON COMMIT PRESERVE ROWS

Hi,

It is a teradata convention to show 0 rows processed on a volatile table creation.after this message you can check data in the create table temp.

check it

CREATE MULTISET VOLATILE TABLE tmp
AS
(
SELECT TOP 100 x FROM table_01
)
with data
ON COMMIT PRESERVE ROWS;

Select * from tmp;
Khurram
Senior Apprentice

Re: CREATE MULTISET VOLATILE TABLE 0 row processed with ON COMMIT PRESERVE ROWS

Did you submit it using SQL Assistant? 

SQLA displays an activitycount (which is ment to be used for DML statements) for DDL statements, too. It's usually meaningless and in your case it's always 0. So do a SELECT COUNT(*) FROM tmp and you'll notice it's not empty.

BTEQ will only return "table has been created" and activitycount :-)

Dieter

Enthusiast

Re: CREATE MULTISET VOLATILE TABLE 0 row processed with ON COMMIT PRESERVE ROWS

Yes, i'm using SQLA. 

Just checked, and yes, 0 row is returned in SQLA but tmp is existing with many rows. 

This forum is very helpfu! thanks!