Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 09 Jul 2009 @ 16:36:03 GMT


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


Subj:   VARCHAR and ORDER BY
 
From:   Price, Trevor

Hi,

We have run this simple query which inserts 2 rows into a table, and runs 3 separate selects.

We have tried this on a V2R6.1 , V2R6.2 and V12 machines.

The results are the same, the last 2 selects produce the wrong results.

We're told its due to the COLUMNLIST being over VARCHAR(1000).

All executed via SQL Assitant in Teradata mode.

     CREATE SET TABLE colstats.clean_stats_test ,FALLBACK ,
            NO BEFORE JOURNAL,
            NO AFTER JOURNAL,
            CHECKSUM = DEFAULT
            (
             DatabaseName VARCHAR(128) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
             TableName VARCHAR(128) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
             StatId INTEGER,
             StatisticType CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
             ColumnName VARCHAR(128) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
             Frequency SMALLINT NOT NULL,
             CreateTimeStamp TIMESTAMP(0) FORMAT 'yyyy-mm-ddbhh:mi:ss' NOT NULL,
             CommentString VARCHAR(300) CHARACTER SET LATIN NOT CASESPECIFIC,
             ColCounter INTEGER,
             ColumnList VARCHAR(8192) CHARACTER SET LATIN NOT CASESPECIFIC,
             CollectStats CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL)
       PRIMARY INDEX ( DatabaseName ,TableName ,ColumnName );

     insert into colstats.clean_stats_test
     values
     ('sys_reporting','dbqlobjtbl_tmp',1,'I','procid',1,current_timestamp(0),
     'Systec',1,'procid,collecttimestamp','C');
     insert into colstats.clean_stats_test
     values
     ('sys_reporting','dbqlobjtbl_tmp',1,'I','collecttimestamp',1,current_tim
     estamp(0),'Systec',2,'procid,collecttimestamp','C');

     1)   select databasename, tablename, statid, statistictype, columnname,
     frequency, createtimestamp, commentstring, colcounter, columnlist,
     collectstats
     from colstats.clean_stats_test
     order by 1,2,3, 9,10 ;

        DatabaseName   TableName   StatId   StatisticType
     ColumnName   Frequency   CreateTimeStamp   CommentString
     ColCounter   ColumnList   CollectStats
        sys_reporting   dbqlobjtbl_tmp   1   I   procid         1
     2009-07-09 16:47:59   Systec   1   procid,collecttimestamp   C
        sys_reporting   dbqlobjtbl_tmp   1   I   collecttimestamp
     1   2009-07-09 16:47:59   Systec   2   procid,collecttimestamp
     C

     CORRECT RESULT

     2)   select databasename, tablename, statid, statistictype, columnname,
     frequency, createtimestamp, commentstring, colcounter, columnlist,
     collectstats
     from colstats.clean_stats_test
     order by 1,2,3, 10,9 ;

        DatabaseName   TableName   StatId   StatisticType
     ColumnName   Frequency   CreateTimeStamp   CommentString
     ColCounter   ColumnList   CollectStats
        sys_reporting   dbqlobjtbl_tmp   1   I   collecttimestamp
     1   2009-07-09 16:47:59   Systec   2   procid,collecttimestamp
     C
        sys_reporting   dbqlobjtbl_tmp   1   I   procid
     1   2009-07-09 16:47:59   Systec   1   procid,collecttimestamp
     C

     INCORRECT RESULT

     3)  select databasename, tablename, statid, statistictype, columnname,
     frequency, createtimestamp, commentstring, colcounter, columnlist,
     collectstats
     from colstats.clean_stats_test
     order by 1,2,3, columnlist,colcounter ;

        DatabaseName   TableName   StatId   StatisticType
     ColumnName   Frequency   CreateTimeStamp   CommentString
     ColCounter   ColumnList   CollectStats
        sys_reporting   dbqlobjtbl_tmp   1   I   collecttimestamp
     1   2009-07-09 16:47:59   Systec   2   procid,collecttimestamp
     C
        sys_reporting   dbqlobjtbl_tmp   1   I   procid         1
     2009-07-09 16:47:59   Systec   1   procid,collecttimestamp   C

     INCORRECT RESULT

Anyone else experience this

Trevor Price - Certified Teradata Master V2R5

Vodafone DBA



     
  <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: 27 Dec 2016