Loading Data files into Teradata table is one of the most common action required in TD datawarehouse environment. In this post, we will discuss about 6 ways of loading data file into teradata table. Also we will talk about pros/cons of each method. We will use below table for all the examples mentioned below: create multiset table usa_states ( state_id byteint, state_name varchar(100), state_abbr char(2), state_capital varchar(100), state_larg_city varchar(100), state_population int ) primary index (state_id); We will load following data from file into this table: stateid~state_name~state_abbr~state_capital~largest_city~population 1~Alabama~AL~Montgomery~Birmingham~4874747 2~Alaska~AK~Juneau~Anchorage~739795 3~Arizona~AZ~Phoenix~Phoenix~7016270 4~Arkansas~AR~Little Rock~Little Rock~3004279 5~California~CA~Sacramento~Los Angeles~39536653 6~Colorado~CO~Denver~Denver~5607154 7~Connecticut~CT~Hartford~Bridgeport~3588184 8~Delaware~DE~Dover~Wilmington~961939 9~Florida~FL~Tallahassee~Jacksonville~20984400 10~Georgia~GA~Atlanta~Atlanta~10429379 11~Hawaii~HI~Honolulu~Honolulu~1427538 12~Idaho~ID~Boise~Boise~1716943 13~Illinois~IL~Springfield~Chicago~12802023 14~Indiana~IN~Indianapolis~Indianapolis~6666818 15~Iowa~IA~Des Moines~DesRead More →

Timestamp Components and Formats

In Teradata, there are various components which make timestamp value in a column. This includes Date part: Year, Month and Day and Time part which includes hour, minutes and seconds. Let us see below all the various formats available for these components: # Type Format Value Description 1 Year YY 14 2 digits of century 2 Year YYYY 2014 complete year 3 Year Y4 2014 complete year 4 Month MM 11 2 digit month 5 Month MMM Nov short form of month 6 Month MMMM November full name of month 7 Day DD 12 2 digit date 8 Day DDD 316 day of the yearRead More →

Teradata DATATYPES , RANGE and STORAGE SPACE

Teradata supports so many datatypes for columns in table. Some of them are standard ANSI datatypes and some Teradata extension to SQL. Below is the list of few very common DATATYPES used in TERADATA with the range for acceptable values and storage space it requires. DATATYPE RANGE SPACE (BYTE) STANDARD NUMERIC DECIMAL(m,n) n= 1 to 38 m= 0 to n 1 TO 2 –> 1 3 TO 4 –> 2 5 TO 9 –> 4 10 TO 18 –> 8 19 TO 38 –> 16 ANSI DECIMAL DECIMAL(m,n) n= 1 to 38 m= 0 to n 1 TO 2 –> 1 3 TO 4 –>Read More →

How to free some space in Teradata Database

In Teradata, by default dates are stored in 24 hours format. i.e 2:00 pm in Teradata would look like 14:00. However we may come across the situation in which Timestamp values coming from SOURCE may have AM PM at the end of the value, like 2013-04-25 02:25:30 PM. Now if the column in Teradata is defined as TIMESTAMP(0) , the value stored in the table would be 2013-04-25 02:25:30 which is actually wrong. The correct value should be 2013-04-25 14:25:30. So how to handle such values? To store time properly in Teradata , we need to specify the proper format of the Timestamp column whichRead More →

Teradata SQL UNION and UNION ALL are used to retrieve data from multiple SELECT blocks and merge them into one resultset. Below is the SQL UNION syntax: SELECT EMP_NAME, EMP_CITY FROM EMPLOYEES UNION SELECT CUST_NAME, CUST_CITY FROM CUSTOMERS; The above query will retrieve all the data from first SQL SELECT query and also retrieve the data from second SQL SELECT query and merge the data and display the resultset. SQL UNION will remove the duplicate entries and will display only one row if duplicate rows are present in final resultset. Below is the SQL UNION ALL syntax: SELECT EMP_NAME, EMP_CITY FROM EMPLOYEES UNION ALL SELECTRead More →

In Teradata, there are various functions available for DATE columns. I have seen many people getting confused and committing mistakes  while handling columns of DATE datatype. So listing down few of the most basic methods of handling DATE columns. To Fetch Current Date: SELECT DATE; TERADATA SQL Assistant modifies the DATE column and display in format specified in settings.To Display DATE in actual format: SELECT CAST(DATE AS VARCHAR(20)); Display date in the user specified format SELECT CAST(DATE AS DATE FORMAT ‘Y4-MM-DD’); Add 2 Days to DATE SELECT DATE + INTERVAL ‘2’ DAY; Add 2 Months to DATE SELECT DATE + INTERVAL ‘2’ MONTH; Add 2 Years toRead More →