How to view the Teradata temp table data

Database

How to view the Teradata temp table data

Hi, I have created a global temp table in the test database and executed the Collect stats statement successfully but I am unable to Select * that table. Also am able to drop it. I executed the select following select statement successfully but fetched now rows. Can any body suggest me how to fetch the rows from the temp table using the following queries.

CREATE GLOBAL TEMPORARY TABLE test.Raj_test (product_id DECIMAL(18,0));

INSERT INTO Raj_test
SELECT item_id FROM views.product;
--57 rows are inserted

COLLECT STATS ON TEMPORARY test. Raj_test COLUMN(upc_cd);
--Executed successfully

SEL * FROM test. Raj_test;
-- Getting now rows (How to sel * from the temp table).
--Note: Though 57 rows are inserted , we are ubale to Sel *

DROP TABLE test.raj_test;
--Executed successfully

Regards
Raju.
1 REPLY
Junior Contributor

Re: How to view the Teradata temp table data

Hi Raju,
it's a nice trick for a magician :-)

if you do a SHOW TABLE you'll see a "ON COMMIT DELETE ROWS", this is the default postulated by Standard SQL.

Your session obviously runs in Teradata mode (if you explain the INSERT/SELECT you'll see a "send out an end transaction step"), which auto-commits by default.

Easiest solution is changing the definition to:
CREATE GLOBAL TEMPORARY TABLE test.Raj_test (product_id DECIMAL(18,0)) ON COMMIT PRESERVE ROWS;

Dieter