All Forums Database
NewAmigo 27 posts Joined 02/14
20 Feb 2014
Date as Integer

Hello,
I am creating a new column in to an existing Date dimension table
 

Alter table DB.Test
Add Date_Key INTEGER;

And updating the field with data from  sys_calendar.calendar
And updating that field with the data SQL Below

update DB.Test
set Date_Key = (select (cast(calendar_date as INTEGER)) from sys_calendar.calendar);

But its throwing some error 3669:More than one value was returned by a aubquery, any ideas why its doing?

Adeel Chaudhry 773 posts Joined 04/08
20 Feb 2014

What are the other columns in your table Test?
 
You need to have a join in an update based on same [but uniquely identifying] columns.

-- If you are stuck at something .... consider it an opportunity to think anew.

dnoeth 4628 posts Joined 11/04
20 Feb 2014

You're trying to update every row in Test with all 73414 in sys_calendar. 
There should be a DATE column in a calendar table:

update DB.Test
set Date_Key = cast(calendar_date as int);

 

Dieter

NewAmigo 27 posts Joined 02/14
21 Feb 2014

There is already a calendar date column in the table, i am trying add the same date column  as Int, which will be used as unique id and easy to join as it will be integer.

VandeBergB 182 posts Joined 09/06
21 Feb 2014

Just a question here, Why do you presume that the integer will be 'easy to join'?
As Dieter and Adeel have pointed out, your second update statement is in error.  You're telling the system to update every row with every date from the sys_calendar.calendar view.  If you've already got a calendar date column on the table (and i understand what you're trying to explain)...your update statement should look exactly like what Dieter posted.
You want to update the new column with the calendar date in the same row cast as an integer right?
Dieter's sql will do that as long as you make sure the column names are correct, since we don't have the table ddl, a template is about as good as it gets.
 

Some drink from the fountain of knowledge, others just gargle.

NewAmigo 27 posts Joined 02/14
21 Feb 2014

I am trying to achieve if the date is 28/11/2004 i need to and store as 28112004, but using the above sql kindly provided by Dieter  it is coming as 1041128. one more example, for 26/04/2010 it is coming as 1100426 instead od 26042010. Not sure what it is doing?

dnoeth 4628 posts Joined 11/04
21 Feb 2014

CAST(datecol AS INT) returns TD's internal DATE format. You want something different:

extract(year from datecol) * 10000 + 
extract(month from datecol) * 100 + 
extract(day from daycol)

or

cast(datecol as int) + 19000000

But IMHO a join of a DATE to a DATE is not more/less complicated that a join of an INT to an INT. 
You just loose all date functionality when you switch to INT :-)

Dieter

Adeel Chaudhry 773 posts Joined 04/08
22 Feb 2014

It would be better to store it as a date, but when you need to show it on a frontend, you can use CAST with FORMAT to specify the required format i.e. 'DDMMYYYY'.
 
HTH!
 

-- If you are stuck at something .... consider it an opportunity to think anew.

NewAmigo 27 posts Joined 02/14
23 Feb 2014

Thanks Adeel,
Can i please ask you for one more favor, i have gone through many articles and forums but couldn't manage to figure out the best method to impelement SCD type 2 objects in a table. In my data cube i have altogether 4 tables out of which 2 tables have got few columns of SCD type 2 (One table with 2 and other with 3 columns). Can you please suggest me the best method to deal with these obejcts in TD12, also please provide me the basic code to implement it. Please note that i am new to Databases and especially to data modelling and Tera Data. So please provide me a very descriptive code and how/where to implement it.
I have created the tables in TD SQL Assistance based on an existing OLTP table.
 
Many Thanks for your favour.
 
 

Adeel Chaudhry 773 posts Joined 04/08
24 Feb 2014

A simple way to cater for SCD Type 2 would be as follows:
 
Assume you have a table as below:
 
Table1
ID INT,
NAME VARCHAR(10),
ADDRESS VARCHAR(50)
 
And you wish to track history on the basis of 'Address can change for an ID/Name'.
 
Hence you add two more columns as below:
 
START_DATE DATE,
END_DATE DATE
 
And now whenever you insert new data, you set:
 
START_DATE as CURRENT_DATE
END_DATE as NULL [or HIGH DATE i.e. 9999-12-31]
 

-- If you are stuck at something .... consider it an opportunity to think anew.

Adeel Chaudhry 773 posts Joined 04/08
24 Feb 2014

Also, in case data is there but address value has changed, you need to UPDATE the previous row and set:
 
END_DATE = CURRENT_DATE;
 
And INSERT new row [with updated Address] as before i.e.:

START_DATE as CURRENT_DATE
END_DATE as NULL [or HIGH DATE i.e. 9999-12-31]

-- If you are stuck at something .... consider it an opportunity to think anew.

Adeel Chaudhry 773 posts Joined 04/08
24 Feb 2014

I will share some basic SQLs sometime today.

-- If you are stuck at something .... consider it an opportunity to think anew.

Adeel Chaudhry 773 posts Joined 04/08
24 Feb 2014

HTH!
 

DROP TABLE EDW_TAB;
DROP TABLE STG_TAB;

CREATE VOLATILE TABLE EDW_TAB
(
ID INT,
NAME VARCHAR(10),
ADDRESS VARCHAR(50),
START_DATE DATE,
END_DATE DATE
) ON COMMIT PRESERVE ROWS;

CREATE VOLATILE TABLE STG_TAB
(
ID INT,
NAME VARCHAR(10),
ADDRESS VARCHAR(50)
) ON COMMIT PRESERVE ROWS;

--DAY 1
INSERT STG_TAB VALUES (1, 'abc', 'lane 1');
INSERT STG_TAB VALUES (2, 'bcd', 'lane 12');
INSERT STG_TAB VALUES (3, 'cde', 'lane 15');

INSERT EDW_TAB
SELECT
ST.ID,
ST.NAME,
ST.ADDRESS,
CURRENT_DATE,
NULL
FROM STG_TAB ST
LEFT OUTER JOIN
EDW_TAB ED
ON
ST.ID = ED.ID
AND
ED.END_DATE IS NULL
WHERE ED.ID IS NULL
;

SELECT * FROM EDW_TAB;

ID	NAME	ADDRESS	START_DATE	END_DATE
1	abc	lane 1	24-Feb-14	?
2	bcd	lane 12	24-Feb-14	?
3	cde	lane 15	24-Feb-14	?


--DAY 2
DELETE STG_TAB;

INSERT STG_TAB VALUES (2, 'bcd', 'lane 13');
INSERT STG_TAB VALUES (4, 'def', 'lane 145');

UPDATE EDW_TAB
FROM STG_TAB ST
SET END_DATE = CURRENT_DATE
WHERE ST.ID = EDW_TAB.ID
AND EDW_TAB.END_DATE IS NULL;

SELECT * FROM EDW_TAB;

ID	NAME	ADDRESS	START_DATE	END_DATE
1	abc	lane 1	24-Feb-14	?
2	bcd	lane 12	24-Feb-14	24-Feb-14
3	cde	lane 15	24-Feb-14	?

INSERT EDW_TAB
SELECT
ST.ID,
ST.NAME,
ST.ADDRESS,
CURRENT_DATE,
NULL
FROM STG_TAB ST
LEFT OUTER JOIN
EDW_TAB ED
ON
ST.ID = ED.ID
AND
ED.END_DATE IS NULL
WHERE ED.ID IS NULL
;

SELECT * FROM EDW_TAB;

ID	NAME	ADDRESS	START_DATE	END_DATE
1	abc	lane 1	24-Feb-14	?
2	bcd	lane 13	24-Feb-14	?
2	bcd	lane 12	24-Feb-14	24-Feb-14
3	cde	lane 15	24-Feb-14	?
4	def	lane 145	24-Feb-14	?

 

-- If you are stuck at something .... consider it an opportunity to think anew.

NewAmigo 27 posts Joined 02/14
24 Feb 2014

Great, Thanks a ton Adeel!!
But can you please explain how/Where do i implement this in realtime? and where do i write this SQL. does it needs to be created as a view in SQL assistant or in any other TD utilities like FastLoad/Mload etc..??

Adeel Chaudhry 773 posts Joined 04/08
24 Feb 2014

Usually this is done in the ETL phase .... which is responsible for fetching new data and populating Core/EDW tables.
 
About where to implement, you can implement it in an SP or in the SQL form to be executed via an ETL tool.
 
Are you using ETL tool btw?

-- If you are stuck at something .... consider it an opportunity to think anew.

NewAmigo 27 posts Joined 02/14
24 Feb 2014

No ETL tools are being used, manual SQL codes, so SP is my option then.

NewAmigo 27 posts Joined 02/14
24 Feb 2014

how to modify the above SQL for a SP?

Adeel Chaudhry 773 posts Joined 04/08
24 Feb 2014

You dont need to modify it, It will work as-is.
 
Following is a template SP:
 

REPLACE  PROCEDURE <Database Name>.<Stored-Procedure Name>
(
)

BEGIN
DECLARE MySQLCode INTEGER;
DECLARE MyActivityCount INTEGER;
DECLARE MySQLState CHAR(5);

/*******************************************************************************************************************
Error Handler
*******************************************************************************************************************/
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN

	SET MySQLState=SQLSTATE;
	SET MySQLCode=SQLCODE;

END;

<Paste the above code here :)>

END;

 

-- If you are stuck at something .... consider it an opportunity to think anew.

NewAmigo 27 posts Joined 02/14
26 Feb 2014

Thanks for the code Adeel, but i have realised that i dont have permissions to write the stored proc, so wondering what other alternatives do i have now?
May be a macro or a series of insert update statements? can you please suggest and please provide me a basic code as you have provided above. Many thansk for your continued support.
 
Regards

NewAmigo 27 posts Joined 02/14
03 Mar 2014

Hello, can some one please help me on this.

 

dnoeth 4628 posts Joined 11/04
03 Mar 2014

If you seriously want to implement SCD you need the right to create SPs.
The code will mostly be generic, so as a basis you can simply use existing code from any RDBMS.
Or use Teradata's Temporal feature in TD13.10, which is exactly what you're looking for.

Dieter

NewAmigo 27 posts Joined 02/14
11 Mar 2014

I dont have access to the SP's and i am on TD12, so decided to write a series of Insert and update statements to tackle the type2 objects.
Source Table A
Target Table B, i want to update this table based on table A, i want to keep the history so added Effective_Date_From/To to the target table if the values changes in a column.
Written a few insert and update statements, but the target table is not updated...am i missing something here??
 
 

Insert INTO B

select

ST.Client_ID,

ST.Business,

ST.Segment,

Current_Date,

NULL

from A ST

LEFT OUTER JOIN B ED

ON ST.Client_ID=ED.ClientID_PK

AND

ED.Effective_Date_To IS NULL

where ED.ClientID_PK IS NULL;




UPDATE B

FROM A ST

SET Effective_Date_To= Current_Date

where ST.Client_ID = B.ClientID_PK

and B.Effective_Date_To IS NULL;




Insert INTO B

select

ST.Client_ID,

ST.Business,

ST.Segment,

Current_Date,

NULL

from A ST

LEFT OUTER JOIN B ED

ON ST.Client_ID=ED.ClientID_PK

AND

ED.Effective_Date_To IS NULL

where ED.ClientID_PK IS NULL;

 

krishaneesh 140 posts Joined 04/13
11 Mar 2014

what are the columns that you think are changing in the table B versus A, you need to mention them as <> conditions in your update like
Update B
from A ST
Set Efftctive_Date_tb=current_date
where st.client_id=B.clienid_pk and
(ST.business <>B.business or ST.segment<>B.segment)
and B.effective_date_to is nUll;
This implies that only if there is a change in the values the old value will be updated else not.

NewAmigo 27 posts Joined 02/14
12 Mar 2014

I am trying to change both Business and segment as these are type 2 fields in the table.
So this update statement needs to be added after the above code i have provided?

Adharssh 36 posts Joined 08/13
04 Apr 2014

Hi,
The Update Statement followed by an insert statement will work.

Update B
from A ST
Set Efftctive_Date_tb=current_date
where st.client_id=B.clienid_pk and
(ST.business <>B.business or ST.segment<>B.segment)
and B.effective_date_to is nUll;
Insert INTO B   
select  ST.Client_ID,  
 ST.Business,   
ST.Segment,   
Current_Date,   
NULL  
from 
A ST  
 LEFT OUTER JOIN B ED  
 ON ST.Client_ID=ED.ClientID_PK   
AND  ED.Effective_Date_To IS NULL  
where ED.ClientID_PK IS NULL;

Thanks & Regards,
Adharssh.

Share the Knowledge. Feel the Happiness, When you share/Teach it.

sharath_rk 7 posts Joined 03/15
03 Mar 2015

Team,
i need help in writing a stored proc for adding integer to a date column by excluding weekends & custom holidays.
For example : OE_DATE + DAYS = Calculated Field ( Date )
                       02/27/15 + 1  = 03/02/15 ( 02/28 & 3/1 are weekends )
                       02/27/15 + 1  = 03/03/15 ( 02/28, 3/1 are weekends & 3/2 is a holiday )

You must sign in to leave a comment.