teradata SQL sequence generation

General

teradata SQL sequence generation

Hi Folks,

I would like to insert records into the Reference table(xref_subject_area) from stage table(detail_stg) in sequence order, when do not have records in reference table where as present in the stage table. please find below is table structures:

CREATE SET TABLE MYDB.detail_stg ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT,

DEFAULT MERGEBLOCKRATIO

(

subject_area_Nm VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

version_no varchar(10) CHARACTER SET LATIN NOT CASESPECIFIC,

mapping_nm varchar(256) CHARACTER SET LATIN NOT CASESPECIFIC

PRIMARY INDEX ( mapping_nm );

insert into MYDB.detail_stg values('ACES','V1.1','EPDS_SYS');

insert into MYDB.detail_stg values('CS90','V1.1','EPDS2_SYS');

insert into MYDB.detail_stg values('ACBS','V1.0','WGS_SYS');

insert into MYDB.detail_stg values('NASCO','V1.1','NASCO_SYS');

insert into MYDB.detail_stg values('WMS','V1.1','EPDS1_SYS');

insert into MYDB.detail_stg values('NMS','V1.1','WGS_NMS_SYS');

Ref table:

CREATE SET TABLE MYDB.xref_subject_area ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT,

DEFAULT MERGEBLOCKRATIO

(

subject_area_Id INTEGER NOT NULL,

subject_area_Nm VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

subject_area_Desc VARCHAR(2000) CHARACTER SET LATIN NOT CASESPECIFIC)

PRIMARY INDEX ( subject_area_Id );

 

insert into MYDB.xref_subject_area values(1,'ACES','ACES Desc');

insert into MYDB.xref_subject_area values(2,'CS90','CS90 Desc');

insert into MYDB.xref_subject_area values(3,'ACBS','ACBS Desc');

insert into MYDB.xref_subject_area values(4,'membership','membership Desc');

insert into MYDB.xref_subject_area values(5,'Provider','provider Desc');

Output would be in xref_subject_area table:

1,'ACES','ACES Desc'

2,'CS90','CS90 Desc'

3,'ACBS','ACBS Desc'

4,'membership','membership Desc'

5,'Provider','Provider Desc'

6,'NASCO','NASCO Desc'

7,'WMS','WMS Desc'

8,'NMS','NMS Desc'

thanks for your help.

Regards,

Murali.

3 REPLIES

Re: teradata SQL sequence generation

 INSERT INTO xref_subject_area
 SEL
  ( SEL MAX(subject_area_Id) AS mx FROM xref_subject_area) +
 ROW_NUMBER () OVER ( ORDER BY subject_area_Nm),
 subject_area_Nm, subject_area_Nm||' desc'
 FROM detail_stg
 WHERE subject_area_Nm  NOT IN ( SELECT subject_area_Nm FROM xref_subject_area)

Hi Murali,

This will be what you can use. Your post is not clear on what columns are basis of sequence ordering so I have ordered them by subject name.

Thanks

Re: teradata SQL sequence generation

Hi Harpreet,

Thank for your querry. This is what, I expected.I had modified mentioned querry with one more select statement to distinct duplicate records in stage. now it is working perfect.

INSERT INTO xref_subject_area

SEL ( SEL MAX(subject_area_Id) AS mx

FROM xref_subject_area) + ROW_NUMBER () OVER ( ORDER BY dstg.subject_area_nm),

dstg.subject_area_nm,

dstg.subject_area_nm ||' desc'

FROM (sel subject_area_nm from detail_stg group by 1 )dstg

WHERE dstg.subject_area_nm NOT IN (

SELECT subject_area_Nm

FROM xref_subject_area)

Thanks

Murali.

N/A

Re: teradata SQL sequence generation

TRY THIS:

INSERT INTO xref_subject_area

SEL TB3.MAX_ID+CSUM(1,TB1.subject_area_nm) AS subject_area_ID,

TB1.subject_area_nm,TB1.subject_area_nm||' DESC'

FROM

detail_stg TB1

LEFT OUTER JOIN

xref_subject_area TB2

ON TB1.subject_area_nm = TB2.subject_area_nm

INNER JOIN

(SEL MAX(subject_area_ID) AS MAX_ID FROM xref_subject_area) TB3

ON 1=1

WHERE TB2.subject_area_nm IS NULL

;