Sql dating

Rated 3.92/5 based on 783 customer reviews

--============================================================================= -- Code Sample 1 --============================================================================= -- Create the staging table for the type two slowly changing dimension table data create table Staging ( Source System ID int not null, Attribute1 varchar(128) not null constraint DF_tbl Staging_Attribute1 default 'N/A', Attribute2 varchar(128) not null constraint DF_tbl Staging_Attribute2 default 'N/A', Attribute3 int not null constraint DF_tbl Staging_Attribute3 default -1, Dimension Check Sum int not null constraint DF_tbl Staging_Dimension Check Sum default -1, Last Updated datetime not null constraint DF_tbl Staging_Last Updated default getdate(), Updated By varchar(50) not null constraint DF_tbl Staging_Updated By default suser_sname() ) -- Create the type two slowly changing dimension table create table Dim SCDType2Example ( Surrogate Key int not null identity(1,1) PRIMARY KEY, Source System ID int not null, Attribute1 varchar(128) not null constraint DF_tbl Dim SCDType2Example_Attribute1 default 'N/A', Attribute2 varchar(128) not null constraint DF_tbl Dim SCDType2Example_Attribute2 default 'N/A', Attribute3 int not null constraint DF_tbl Dim SCDType2Example_Attribute3 default -1, Dimension Check Sum int not null constraint DF_tbl Dim SCDType2Example_Dimension Check Sum default -1, Effective Date date not null constraint DF_tbl Dim SCDType2Example_Effective Date default getdate(), End Date date not null constraint DF_tbl Dim SCDType2Example_End Date default '12/31/9999', Current Record char(1) not null constraint DF_tbl Dim SCDType2Example_Current Record default 'Y', Last Updated datetime not null constraint DF_tbl Dim SCDType2Example_Last Updated default getdate(), Updated By varchar(50) not null constraint DF_tbl Dim SCDType2Example_Updated By default suser_sname() ) In Code Sample 2, we insert two "new" records from the source system into the staging table. --============================================================================= --Code Sample 5 --============================================================================= --Start of Day 2 - truncate the staging table truncate table Staging -- insert a new record into the staging table insert into Staging (Source System ID, Attribute1, Attribute2, Attribute3) values (3 , 'Jane Doe', 'Single' , 123) -- insert a new record into the staging table insert into Staging (Source System ID, Attribute1, Attribute2, Attribute3) values (4 , 'John Doe', 'Married' , 246) -- insert a changed record into the staging table insert into Staging (Source System ID, Attribute1, Attribute2, Attribute3) values (2 , 'Ricky L.Then, we use the BINARY_CHECKSUM function to create a checksum value for the records in the staging table. Green', 'Married' , 189) -- Update the checksum value in the staging table update Staging set Dimension Check Sum= BINARY_CHECKSUM(Source System ID, Attribute1, Attribute2, Attribute3) After executing Code Sample 5 to insert records into the staging table, execute the MERGE/INSERT statement in Code Sample 3 and the SELECT statements in Code Sample 4.These properties cause Oracle to treat values of one datatype differently from values of another. When you create a table or cluster, you must specify a datatype for each of its columns.When you create a procedure or stored function, you must specify a datatype for each of its arguments.Oftentimes I would find examples of the MERGE statement that just didn't do what I needed it to do, that is to process a Type 2 slowly changing dimension. The solution presented in this tip will walk through the steps of how to use the MERGE statement nested inside an INSERT statement to handle both new records and changed records in a Type 2 Slowly Changing Dimension table within a data warehouse.This solution will walk through the processing over three days. Records are first inserted into a staging table and then the MERGE statement will insert new records into the slowly changing dimension table. Dimension Check Sum, getdate(), '12/31/9999' ) -- the end of the merge statement --The changes output below are the records that have changed and will need --to be inserted into the slowly changing dimension.

"ANSI, DB2, and SQL/DS Datatypes" discusses the mapping of ANSI-supported datatypes to Oracle built-in datatypes.

Notice how there are new records for Source System ID 1, 3 and 4 where the Current Record column is set to 'Y' and the End Date is 12/31/9999.

The datatype of a value associates a fixed set of properties with the value.

SELECT Source System ID, Attribute1, Attribute2, Attribute3, Dimension Check Sum from Staging ) AS source ( Source System ID, Attribute1, Attribute2, Attribute3, Dimension Check Sum ) ON --We are matching on the Source System ID in the target table and the source table. --============================================================================= -- Code Sample 6 --============================================================================= --Start of Day 3 - truncate the staging table truncate table Staging -- insert a changed record into the staging table insert into Staging (Source System ID, Attribute1, Attribute2, Attribute3) values (3 , 'Jane Doe-Jones', 'Married' , 123) -- insert a changed record into the staging table insert into Staging (Source System ID, Attribute1, Attribute2, Attribute3) values (4 , 'John Doe', 'Married' , 220) -- insert a new record into the staging table insert into Staging (Source System ID, Attribute1, Attribute2, Attribute3) values (5 , 'Bill Smith', 'Married' , 198) -- insert a changed record into the staging table insert into Staging (Source System ID, Attribute1, Attribute2, Attribute3) values (1 , 'Mary Brown', 'Single' , 136) -- Update the checksum value in the staging table update Staging set Dimension Check Sum= BINARY_CHECKSUM(Source System ID, Attribute1, Attribute2, Attribute3) After executing Code Sample 6 to insert records into the staging table, again execute the MERGE/INSERT statement in Code Sample 3 and the SELECT statements in Code Sample 4.

The results of the latest SELECT statements are shown in the figure below.

Leave a Reply