Interview Questions   Tutorials   Discussions   Programs   

Teradata - How do you Generate Sequence in Teradata?




1172
views
asked mar September 20, 2014 06:37 AM  

How do you Generate Sequence in Teradata?


           

1 Answers



 
answered By vishnoiprem   0  

CREATE MULTISET TABLE op_process_run_msg ,NO FALLBACK ,
   NO BEFORE JOURNAL,
   NO AFTER JOURNAL,
   CHECKSUM = DEFAULT,
   DEFAULT MERGEBLOCKRATIO
   (
   process_run_id BIGINT NOT NULL,
   msg_id BIGINT GENERATED ALWAYS AS IDENTITY
      (START WITH 1 
      INCREMENT BY 1 
      MINVALUE -999999999999999999 
      MAXVALUE 999999999999999999 
      NO CYCLE),
   process_name VARCHAR(30) CHARACTER SET LATIN CASESPECIFIC,
   msg_ts TIMESTAMP(6),
   msg_txt VARCHAR(2000) CHARACTER SET LATIN CASESPECIFIC)
PRIMARY INDEX nupi_op_process_run_msg ( process_run_id );


 INSERT INTO wrk_batch_processing
  (
  seq_id,
  concierge_reservation_id
  )
  SELECT 
	ROW_NUMBER() OVER (ORDER BY concierge_reservation_id ASC) AS seq_id,
	concierge_reservation_id
	FROM reservation

  

2nd Method


We have come across rownum function in oracle . This function gives number for every row/record in Oracle. In teradata there is no direct approach like in oracle. There are various approaches suggested around.

Approach 1:
Here Sum function is used  over rows preceding in the SOURCE_TABLE

select
sum(1) over( rows unbounded preceding ),
columnA,
columnB
from
SOURCE_TABLE;

Approach 2:
Here ROW_NUMBER function is used to generate row_number on columnA
select
ROW_NUMBER() over( ORDER BY columnA ),
columnA,
columnB
from
SOURCE_TABLE;


If you have  to use the  row number concept  in target table  as well, then  following  approach using "identity column" (from V2R6 onwards )  be used :

CREATE MULTISET TABLE TARGET_TABLE
  (
   columnA INTEGER GENERATED BY DEFAULT AS IDENTITY
       (START WITH 1
        INCREMENT BY 20) ,
   columnB VARCHAR(20) NOT NULL
  )
UNIQUE PRIMARY INDEX pidx (ColA);

P.S:  Identity columns does differ from sequence concept in oracle. The numbers assigned in these columns are not guaranteed to be sequenctial. The Identity column in Teradata is used to guaranteed row-uniqueness.

This  works without use of Identity approach.
create TABLE TARGET_TABLE as
(
   select
   ROW_NUMBER() over( ORDER BY columnA ) NUMBER_SEQ ,
   columnA,
   columnB,
   columnC
   from a join b on a.id=b.id
) with data ;
flag   
   add comment

Your answer

Join with account you already have

FF

Preview


Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!

Alert