Insert ordered seq no based on order of particular column

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Insert ordered seq no based on order of particular column

Hi,

I am trying to  insert record into temp table with ordered seq no as one column and based on order of particular field values . I am tried using the following query :

INSERT into temp_table

sel

column1,

SUM(1) OVER(ROWS UNBOUNDED PRECEDING) + 0 AS REC_SEQ

from

base_table

order by column1,column2, column3;

 

I got syntax error like order by clause cannot be used in subquery.

 

Can anyone help to get the logic to implement the above req.

 

Thanks in advance.......

 

 

Thanks

Vinoth.B

3 REPLIES 3
Teradata Employee

Re: Insert ordered seq no based on order of particular column

Hi Vinoth,

 

Try with row_number :

insert into temp_table
select column1
     , row_number() over(order by column1, column2, column3) as seq_no
  from base_table;
Enthusiast

Re: Insert ordered seq no based on order of particular column

Hi Walder,

 

but i used the order function as you share before like

 

SyntaxEditor Code Snippet

insert into temp table
select incident_no, Incident_date, Task_Date, Task_time,
,row_number() over (ORDER BY MIN(Task_Date) OVER(PARTITION BY Primry_incident_no), Primry_incident_no, Task_Date, Task_time)
from MyTable

 

so getting error while using along with row_number as 

 

Failed 5480 : ordered analytical fucntion can not be nested 

Teradata Employee

Re: Insert ordered seq no based on order of particular column

You have to go for a two-steps stat query :

insert into temp_table
with cte_orig_task_date as
(
select incident_no, Incident_date, Task_Date, Task_time, Primry_incident_no
     , min(Task_Date) over(partition by Primry_incident_no) as orig_Task_Date
  from MyTable
)
select incident_no, Incident_date, Task_Date, Task_time
     , row_number() over(/*partition by Primry_incident_no*/ order by orig_Task_Date, Primry_incident_no, Task_Date, Task_time) as seq_no
  from cte_orig_task_date;

 I'm not sure if you need a partition by within the row_number, so I let it as a comment for now.