I am trying to generate a sequential no and it worked fine for first couple of inserts. But when i run this query, I get a spool error. If I remove the seq_no generator, the query runs fine. Now the COl1 does contain many dupes. Any way to fix this? INSERT INTO TABLEb SELECT DISTINCT cnt + row_number() over (order by 1), H.COL1 , H.COL2 , H.COL3 , NULL, NULL FROM TABLE H , (select coalesce(max(seq_NO), 0) as cnt from TABLEB) dt WHERE H.COL1 IS NOT NULL
The reason I am adding a sequence number is because its difficult to find column/columns with distinct values. IS there any other way to genertae the sequence no instead? I did not want to use IDentity because the seq numbers might not be in sequence.
You don't need a unique column combination, just more than the current number, which is exactly 1.
Whitin an OLAP-function "ORDER BY 1" is *not* the ordinal position within the Select list, it's the value 1. Teradata sends all the rows to a single AMP to do that ROW_NUMBER calculation and therefore runs out of spool.
So simply do something like SELECT cnt + row_number() over (order by H.Col1, H.Col2, H.Col3), H.COL1 , H.COL2 , H.COL3 ,
Just as many columns to get enough distinct values.
I tried using 5 columns but it stiill runs into spool error. Now I have a basic question. If my Select statement returns duplicates, then will these dupes be inserted in a SET table?When I say dupes, the entire record is a dupe , not just the key. When I look at the table that I inserted, i find dupes in it. How does a SET tabel allow dupes? How do I remove the dupes from the Select?
Dieter, I tried ordering by atleast 5/6 columns and it still gives me spool space error . I also tried MAX_ID + CSUM(1,1) AS SEQ_NO , (select case when max(SEQ_NO) is null then 0 else max(SEQ_NO) end MAX_ID from table1) D;
When you create a kind of sequence, there are no more duplicate rows, because the sequence makes all of them distinct. The DISTRINCT in your query is done *after* the calculation of the ROW_NUMBER.
MAX_ID + CSUM(1,1) is exactly the same as the ROW_NUMBER () OVER (ORDER BY 1). If you don't have any column combination to get a better distribution for the OLAP-function, you might try RANDOM. But you can't nest RANDOM in an OLAP-function, so you have to nest it in a Derived Table:
INSERT INTO TABLE b select cnt + row_number() over (order by r), H.COL1 , H.COL2 , H.COL3 , NULL, NULL from ( SELECT H.COL1 , H.COL2 , H.COL3 , random(1,1000000000) as r FROM TABLE H ) as H , (select coalesce(max(seq_NO), 0) as cnt from TABLEB) dt WHERE H.COL1 IS NOT NULL
If you need DISTINCT over (H.COL1 ,H.COL2 ,H.COL3) you have to nest it into another Derived Table...