All Forums Database
KevinKWada 3 posts Joined 06/10
08 Jun 2010
Problem creating NOPI table

I'm a Teradata newbie. I want to create tables with NO PRIMARY INDEX. I am on version 12.0. My DDL looks similar to this.

CREATE MULTISET TABLE UP_KWADA.TEST_INSERT, FALLBACK,
NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT
(
LINE_NUMBER DECIMAL(18,0),
TRANSACTION_SET_ID DECIMAL(18,0),
FILE_ID VARCHAR(20)
) NO PRIMARY INDEX ;

I get the error message
3706: Syntax error: Expected something between ')' and the 'NO' keyword.

My need for a no primary index table is due to the fact that my inserts into my "work" tables are eliminating rows that should not be (dup key probably).

Thanks for your advice.

Jim Chapman 449 posts Joined 09/04
08 Jun 2010

The No-PI table is a feature that was introduced in version 13.0. Anyway, as far as I know, there is no automatic elimination of rows on insert into a multiset table unless you are using FastLoad. That problem can be avoided by using MultiLoad instead of FastLoad.

KevinKWada 3 posts Joined 06/10
09 Jun 2010

Thanks Jim for responding. The fact that No-PI is a version 13.0 is one issue. The reason that I dropped rows was that whole records were being duplicated, not just the primary index.
Thanks again.

Jim Chapman 449 posts Joined 09/04
09 Jun 2010

Again, the removal of duplicate rows is a FastLoad problem. Try using a different insert method, such as MultiLoad or regular SQL.

You must sign in to leave a comment.