Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 12 Jan 2007 @ 12:50:15 GMT


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


Subj:   Re: ORDER BY in INSERT SELECT to GENERATE Sequence
 
From:   Dieter Noeth

Anomy.Anom wrote:

  When we try to do the below INSERT.. SELECT  


          > Insert into temp2 (value1, value2, value3)
          > As
          > Select (value1, valu2, value3) from temp1 order by value1

Did you work with MS SQL Server before?


  We get the following error  


  "3706: ORDER BY IS NOT ALLOWED IN SUB QUERIES"  


  I understand ORDER BY can't exist in the sub query.  


  But our intention is to generate a sequence number (in temp2) based on the 'value1' column in some order.  


You mean, there's an Identity column in temp2?

Even if Order By was allowed this would not result in a sequential number. It's much easier to create the sequence during Select using an OLAP-function:

     Select
        row_number() over (order by value1)
        value1, valu2, value3
     from temp1

Dieter



     
  <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