What,Why and How of Encryption in Teradata – Basics and Examples with Syntax

What is encryption ? Why we need encryption ? How do we implement encryption ? One must clearly know answer to these 3 questions before actually implementing encryption in any datawarehouse environment. Encryption is the process by which we change the actual meaningful value to some other meaningless value so that any person who reads it cannot understand it. For example if I say “VGTCFCVC” it won’t make any sense to you however if I share actual value “TERADATA” then…

Continue Reading
Teradata Recursive Query - Syntax with Example

Teradata Recursive Query Syntax with Example

Recently Krish asked us a query in which data in one row should be concatenated with data in other row. And this should continue for all subsequent rows. It looks like kind-of cumulative sum kind of operation for character columns. So we thought of doing it using rescursive queries: Let us see the example below: CREATE MULTISET VOLATILE TABLE vt_source ( dept_no INTEGER, emp_name VARCHAR(100) )ON COMMIT PRESERVE ROWS; INSERT INTO vt_source SEL 10,’A’; INSERT INTO vt_source SEL 10,’B’; INSERT…

Continue Reading
Multiple Rows into Single Row

Convert Multiple Rows into Single Row in SQL in Teradata

Recently Krish asked on our Facebook page about how to convert multiple rows into one row using sql. Also, Raghav asked via contact form how to get all column list of a table in one single column into a volatile table. So we thought of proposing a single solution to both the problems. We will use recursive query to convert multiple rows into one single row where each row value will be separated by a delimiter ( in this case,…

Continue Reading
How to create an excel report from sql in Teradata

How to create an excel report from sql in Teradata

This post is in response to a topic on LinkedIn Group. When we say Excel report, we mean real Excel report with multiple tabs and not just one file with delimiter as comma which you can open in Excel. With the data present in tables you may have to create reports and share it with business. We know how to export data from Table into file in Teradata. You can use BTEQ export or FASTEXPORT depending on volume of the…

Continue Reading
Regular Expression in SQL in Teradata

String Manipulation using REGULAR EXPRESSION in Teradata

There are so many String manipulation tasks we do in our data warehouse environment. Some of them are easy and few of them are complex. With the recent introduction of REGULAR EXPRESSION in Teradata, many complex tasks have now become a one-line code. Below are few cases in which using REGULAR EXPRESSION can be useful: 1) Check if string is made of only 1 character: There may be a case when source is sending you some data with some filler…

Continue Reading
Split One Column into Multiple Rows

Split one column into multiple rows in sql in Teradata

We recently had a discussion on our Facebook Fan Page regarding how to split one column values into multiple rows. We got many responses like using for loop in stored procedures etc. We can split values in one column into multiple rows by using Recursive queries. Let’s see an example below: First we create a table which will hold multiple emails as one value in a column. CREATE VOLATILE TABLE VT_MANY_TO_ONE ,NO FALLBACK,NO LOG, NO JOURNAL ( SEQ_NO SMALLINT, EMAIL_LIST…

Continue Reading
Teradata INTERVAL Function

Teradata INTERVAL Function

In Teradata, we can use INTERVAL function to perform several DATE – TIME operations. Let’s see below few examples using Teradata Interval Function. CREATE MULTISET VOLATILE TABLE VT_DATE_TIME ( TIMESTAMP1 TIMESTAMP(0) FORMAT ‘Y4-MM-DDBHH:MI:SS’ ) PRIMARY INDEX(TIMESTAMP1) ON COMMIT PRESERVE ROWS; INSERT INTO VT_DATE_TIME SELECT CURRENT_TIMESTAMP(0) (FORMAT ‘Y4-MM-DDBHH:MI:SS’); SELECT TIMESTAMP1 FROM VT_DATE_TIME; — 2014-01-07 04:21:17 SELECT TIMESTAMP1 – INTERVAL ’02’ YEAR FROM VT_DATE_TIME; — 2012-01-07 04:21:17 SELECT TIMESTAMP1 – INTERVAL ’02’ MONTH FROM VT_DATE_TIME; — 2013-11-07 04:21:17 SELECT TIMESTAMP1 – INTERVAL…

Continue Reading
TERADATA DATE FUNCTIONS

Teradata Date Functions – Advance

Below are few Teradata DATE Functions that can be used to get more details about any DATE. a) SEL DATE; — 2013-07-22 b) SEL TD_DAY_OF_WEEK(DATE); –2 i.e. Day of the Week. [Monday] c) SEL TD_DAY_OF_MONTH(DATE); –22 i.e. Day of the Month d) SEL TD_DAY_OF_YEAR(DATE); –203 i.e. 203 day of the year e) SEL TD_DAY_OF_CALENDAR(DATE); –41476 i.e. days since 01-Jan-1900 f) SEL TD_WEEKDAY_OF_MONTH(DATE); –4 i.e. 4th Monday of Month g) SEL TD_WEEK_OF_MONTH(DATE); — 3 i.e. 3rd FULL WEEK of the MONTH.…

Continue Reading
TERADATA-SQL-TUTORIAL

Teradata Replace Function Alternative

Many people asked me if there is any alternative method in Teradata which can replicate REPLACE functionality in Teradata. Basically, the intent is to REPLACE some character with some other character in all the column values in a table. There is a REPLACE & OREPLACE function available in Teradata however many people find that the REPLACE function is not available in Teradata environment they are working. So I have created a Stored Procedure, which can replicate the same functionality of…

Continue Reading
Compression in Teradata

How you can implement COMPRESSION in Teradata

Compression in Teradata plays a very important role in saving some space and increasing the performance of SQL Query. In Teradata, COMPRESSION can be implemented in three ways: a) Single Value or Multi Value Compression (MVC) b) Algorithmic Compression (ALC) c) Block – Level Compression (BLC) a) Single Value or Multi Value Compression: This type of compression is widely used in many Teradata Data Warehouse environment. This is easy to implement and can save good amount of space. MVC uses…

Continue Reading
How to free some space in Teradata Database

How to free some space in Teradata Database

We may come across situation in which we may be encountering errors related with non availability of Storage Space in Teradata. For example: “NO MORE ROOM IN DATABASE” or “NO MORE SPOOL SPACE IN DATABASE” So what one should do in such cases? We are talking about situations when we really have Storage Space shortage in our Teradata Environment. Adding more physical Storage Space is not the best option available all the time. So what one must do in order…

Continue Reading