Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 18 Jan 2002 @ 13:51:03 GMT

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

Subj:   Re: Create Set Table ... As
From:   Geoffrey Rommel

  If Table A is a Multiset table, the proper syntax is 'Create Multiset Table B as Table A with Data;'. I don't understand why Teradata would assume (as a default) that Table B shouldn't be the same type of table as Table A.  

This may be not a Teradata assumption but an ANSI assumption. Be that as it may, the correct syntax, as you know, is:

create [set|multiset] table tableA as
(select stuff from tableB ...)
with data;

The expression in parentheses designates a result set, not a table. (For example, DBC.AccLogTbl is a table; (select * from DBC.AccLogTbl) is a result set, not a table.)

In other words, the data you are inserting into your newly created table is simply a set of rows, not an existing table structure.

To put this yet another way: If you coded this:

select *
from DBC.TableSize
where (databasename, tablename) in
(select databasename, tablename from DBC.Tables);

would you expect the subquery to be materialized as a table, with primary index, entries in the Cylinder Index, and all the rest of it? Of course not. It is simply a set of rows. They have to be stored somewhere, of course, but that doesn't make them a table.


  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023