Archives of the TeradataForum

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

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

Dieter Noeth wrote:

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......

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.

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

Cheers

Joerg

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