Archives of the TeradataForum

Message Posted: Fri, 19 Oct 2007 @ 14:16:28 GMT

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

Hi,

1.Yes, the GROUP BY works, only when *all the column values*, not just the UPIs (in the duplicate rows) are same.

Note the last insert in the below example.

2. I suppose, we can get the same effect by making the target table as SET one,

Please give it a try.

     show table tab1;
     show table tab1;
       *** Text of DDL statement returned.
       *** Total elapsed time was 1 second.
     --------------------------------------------------------------
     CREATE MULTISET TABLE D.tab1 ,NO FALLBACK ,
           NO BEFORE JOURNAL,
           NO AFTER JOURNAL,
           CHECKSUM = DEFAULT
           (
            col1 INTEGER,
            col2 INTEGER,
            col3 INTEGER)
     *UNIQUE* PRIMARY INDEX ( col1 ,col2 );

       BTEQ -- Enter your DBC/SQL request or BTEQ command:
     show table tab2;
     show table tab2;
       *** Text of DDL statement returned.
       *** Total elapsed time was 1 second.
     --------------------------------------------------------------
     CREATE MULTISET TABLE D.tab2 ,NO FALLBACK ,
           NO BEFORE JOURNAL,
           NO AFTER JOURNAL,
           CHECKSUM = DEFAULT
           (
            col1 INTEGER,
            col2 INTEGER,
            col3 INTEGER)
     *PRIMARY INDEX* ( col1 ,col2 );

       BTEQ -- Enter your DBC/SQL request or BTEQ command:
     sel * from tab2;
     sel * from tab2;
       *** Query completed. 3 rows found. 3 columns returned.
       *** Total elapsed time was 1 second.
             col1         col2         col3
     -----------  -----------  -----------
                1            2            1
                *1            1            1
                1            1            1* <<--- Dup

       BTEQ -- Enter your DBC/SQL request or BTEQ command:
     insert tab1
     sel * from tab2;
     insert tab1
     sel * from tab2;
       *** *Failure 2801 Duplicate unique prime key error in d.tab1*.
                      Statement# 1, Info =0
       *** Total elapsed time was 1 second.

       BTEQ -- Enter your DBC/SQL request or BTEQ command:
     insert tab1
     sel * from tab2
     *group by 1,2,3;*
     insert tab1
     sel * from tab2
     group by 1,2,3;
       *** Insert completed. *2* rows added.
       *** Total elapsed time was 1 second.

       BTEQ -- Enter your DBC/SQL request or BTEQ command:
     sel * from tab1;
     sel * from tab1;
       *** Query completed. 2 rows found. 3 columns returned.
       *** Total elapsed time was 1 second.
             col1         col2         col3
     -----------  -----------  -----------
                1            2            1
                1            1            1  <<--- Dup removed.

Thanks

Victor.S

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