Archives of the TeradataForum

Message Posted: Fri, 05 Sep 2009 @ 02:01:09 GMT

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

All of the other postings are correct not only for Teradata but for set theory as well which Relational Databases are (or should be) based upon.

I will rant a bit then offer a practical solution - please bear with me.

As such:

     insert into target_table
     select x,y,z
     from some_table
     order by z;

makes no sense - even if some RDBMS's permit the syntax.

At some point in your program you will need to run a query against the table (in my case target_table), simply relocate your order by to that select.

For example:

     insert into target_table
     select x,y,z
     from some_table      -- No order by
     ;

     Select *
     From target_table
     Order by z;      -- Order by on retrieval.

If as you indicate that "minor" change means a total rewrite (it's hard to imagine that is true, but we have to accept that it is), consider using a views database. A view's database is a recommended practice by the way.

In this model, the physical tables are in one database. A separate database has 1:1 views onto the physical tables. You can use the same names and include an order by in the views if you wish.

Consider the following:

     Create table pdata.target_table (
         X    integer,
         Y    integer,
         Z    integer
     ) primary index(x);

And

     Replace view pview.target_table
     As
     Select x,y,z
     >From pdata.target_table
     Order by z
     ;

Repeat these views for all relevant tables with or without the order by clause.

Now all you need to do is ensure that users are pointing at PVIEW rather than PData. I doubt this would involve much work because you must already have a mechanism to point users at the "correct" database.

I hope this helps

Glenn Mc

Teradata Corporation
Glenn McCall

Canberra Australia

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