Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Sat, 26 Jan 2008 @ 20:25:41 GMT


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


Subj:   Re: Sorting of a Select
 
From:   Mutsch, Joerg

Dieter Noeth wrote:

  Argh, adding a "order by 1" is too annoying?  


No, not the order by in the select, it's the resultset within the Frontend Tool


          > replace view "test without order by" as
          > select calendar_date from sys_calendar.calendar > where calendar_date between current_date - 10
          > and current_date qualify row_number()
          > over (order by calendar_date) > 0;

          > select * from "test without order by";

Actually this works almost perfect. Check this out:

If I am running the Select as the following

     select
        "THE_WEEK_V" ."WeekID"  ,
        "THE_WEEK_V"."WeekCD"  ,
        "THE_WEEK_V"."WeekDesc"
     from RD_VIEWS.THE_WEEK_V

The resultset is exactly as I need it. The Result is ordered by the WeekID

     Week   Week
     Id     CD      WeekDesc
     1      1       Week 01 2000
     2      2       Week 02 2000
     3      3       Week 03 2000
     4      4       Week 04 2000
     5      5       Week 05 2000

If I am running the select like this (this is the SQL which will be send out to the Database)

     SELECT
        "THE_WEEK_V"."WeekID"   "WeekID" ,
        "THE_WEEK_V"."WeekCD"    "WeekCD" ,
        "THE_WEEK_V"."WeekDesc"   "WeekDesc"
     FROM "RD_VIEWS"."THE_WEEK_V" "THE_WEEK_V"

The resultset is messed up again....

     Week   Week
     Id     CD      WeekDesc
      1       1     Week 01 2000
     53      53     Week 01 2001
      2       2     Week 02 2000
     54      54     Week 02 2001
      3       3     Week 03 2000
     55      55     Week 03 2001
      4       4     Week 04 2000

The View is defined as

     Replace View RD_VIEWS.THE_WEEK_V As Locking Table RD.THE_WEEK For access

     SELECT * FROM RD THE_WEEK
       qualify row_number() over (order by WeekID) > 0  With check option;

I have another Table for Month with the same columns for Month. On this table both SQL run in the same way......

  Or do it the good old MS SQL Server way: replace view "Used to work in MS SQL Server 2000" as select top 100 percent calendar_date from sys_calendar.calendar where calendar_date between current_date - 10 and current_date order by 1;  


  select * from "Used to work in MS SQL Server 2000";  


That's exactly what I tried in the first place. Teradata is giving me an error with the message that a Sort is not allowed in a subquery.


  Another solution, if the table is small (because all rows will be stored on a single AMP) returning in order of insertion:  


No option, there are hundreds of small tables so the maintenance would be to intensive.


Cheers

Joerg



     
  <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