teradata SQL

Using a Common Table Expression (CTE) with an INSERT INTO statement

by Ali Raza Zaidi on February 14, 2012

My current assignment was to write ETL Process using TSQL.  So I have to get all rows which are newly added to Source Table and are not part of destination table. I wrote simple Common Table Expression to get all rows and insert into destination table.

So part of ETL Process using CTE as

 

with NewRows as

(

SELECT     sourceEmp.empid, sourceEmp.lastname, sourceEmp.firstname, sourceEmp.title, sourceEmp.titleofcourtesy, sourceEmp.birthdate, sourceEmp.hiredate, sourceEmp.address, sourceEmp.city, sourceEmp.region, sourceEmp.postalcode, sourceEmp.country, sourceEmp.phone, sourceEmp.mgrid,DestEmployee.SourceEmpId

FROM         TSQLFundamentals2008.HR.Employees sourceEmp left join

TSQLFundamentals2008DW.HR.DimEmployees DestEmployee

on

sourceEmp.empid = DestEmployee.SourceEmpId

where DestEmployee.SourceEmpId is null

)

INSERT INTO [TSQLFundamentals2008DW].[HR].[DimEmployees]

([lastname]

,[firstname]

,[title]

,[titleofcourtesy]

,[birthdate]

,[hiredate]

,[address]

,[city]

,[region]

,[postalcode]

,[country]

,[phone]

,[mgrid]

,[SourceEmpId])

(select

NewRows.lastname, NewRows.firstname, NewRows.title, NewRows.titleofcourtesy, NewRows.birthdate,

NewRows.hiredate, NewRows.address, NewRows.city, NewRows.region, NewRows.postalcode, NewRows.country, NewRows.phone, NewRows.mgrid,

NewRows.empid  from NewRows)

{ Comments on this entry are closed }

update statement with select in teradata

by Ali Raza Zaidi on September 16, 2008

hi all, i use following syntex for this


UPDATE T1 FROM (SELECT t2_1, MIN(t2_2) from T2 group by 1) as D (D1, D2)
SET Field2 = D2
WHERE Field1 = D1

 

So i solve my problem as

UPDATE XTZ.KPI FROM (SELECT CONVERTED_TARGET_QTY,NORMAL_VALUE,CRITICAL_VALUE FROM DP_UTL.PRODUCTION_KPI WHERE PACKAGE='SSPET-500') AS D(D1,D2,D3) SET
MaximumValue = D1
, NormalValue = D2
, CriticalValue = D3
WHERE KPIId=125;

 

Chears

{ Comments on this entry are closed }

How to get first date of last year in teradata Sql

by Ali Raza Zaidi on September 13, 2008

While working on teradata we have to show some reports on database, for example MTD month to date, YTD year to date, While searching Records on The bases of YTD , I have to  Find records till start of last month and till today. The I have to create first date of  last years as follow

select cast(cast( CAST( EXTRACT(YEAR FROM CURRENT_DATE)-1 AS CHAR(4))||'0101' as date format 'yyyymmdd') as date format 'yyyy-mm-dd');

{ Comments on this entry are closed }