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 you’ll immediately understand it. We may use any algorithm to convert one value to other ranging from very simple to extreme complex. In this caseRead More →

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 INTO vt_source SEL 10,’C’; INSERT INTO vt_source SEL 10,’D’; INSERT INTO vt_source SEL 20,’P’; INSERT INTO vt_source SEL 20,’Q’; INSERT INTO vt_source SEL 20,’R’; INSERTRead More →

Multiple Rows into Single Row

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, it is ~). So recursive query needs a base query and then with that base query it joins other rows on the basis of someRead More →

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 data you want in your report. So the export part is same. Lets look at below example: #!/bin/ksh bteq <<end_of_bteq .LOGON SERVER/USERNAME,PASSWORD .SET SEPARATOR ‘~’;Read More →

Regular Expression in SQL 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 values. If the complete String value consists of same character repeated throughout the string then it is a filler. Now traditionally you may do itRead More →

Split One Column into Multiple Rows

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 VARCHAR(100) )PRIMARY INDEX(SEQ_NO) ON COMMIT PRESERVE ROWS; Let’s insert a row into this table with multiple emails separated by ‘~’. INSERT INTO VT_MANY_TO_ONE VALUES(3,’ABC@ABC.COM~DEF@DEF.COM~XYZ@XYZ.COM’); NowRead More →

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 ’02-02′ YEAR TO MONTH FROM VT_DATE_TIME; –2011-11-07 04:21:17 SELECT TIMESTAMP1 – INTERVAL ’02’ DAY FROM VT_DATE_TIME; — 2014-01-05 04:21:17 SELECT TIMESTAMP1 – INTERVAL ’02’ HOURRead More →

TERADATA DATE FUNCTIONS

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. h) SEL TD_WEEK_OF_YEAR(DATE); — 29 i.e. 29th FULL WEEK of the YEAR. i) SEL TD_WEEK_OF_CALENDAR(DATE); — 5925 i.e. 5925th FULL WEEK of the YEAR sinceRead More →

TERADATA-SQL-TUTORIAL

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 REPLACE function. Below are the details: I created a Table and inserted some records in the table. For this example, I have tested REPLACING SPACERead More →

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 a dictionary to maintain value of data and its corresponding bit pattern. So while saving, Teradata replace the exact value with the bit pattern andRead More →

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 to free some existing occupied space ? Sharing below few possible methods of releasing some occupied space: Implementing Single Value or Multi-Value Compression is consideredRead More →

Handling NULL values in Teradata

NULL is anything which is unknown or unavailable in any RDBMS system and anyone who works in RDBMS environment must take extra precautions while dealing with data that may have some NULL values. There are few basics rules which one must follow in order to handle NULL values effectively. Mandatory columns may get NULL values from SOURCE or due to JOINS between tables. In such cases to avoid “NULL VALUE IN NOT NULL FIELD” error, use COALESCE function. eg: COALESCE(MANDATORY_COLUMN, ‘SOME DEFAULT VALUE’); While Joining several tables we may be having NULL values for some columns participating in JOIN CONDITION. In such cases, it isRead More →

DELETE DUPLICATE RECORDS FROM TABLE IN TERADATA

We may come across a situation where we have duplicate records in a table and we need to remove the duplicates. That is the table must hold only one instance of each row and there should not be any duplicates.In Teradata, this can be done easily by creating a new replica SET table and inserting the data into the new SET table from table which is having duplicate records. The SET table does not allow duplicate records hence all the duplicate records will be discarded and only one instance of each row will be inserted into the table.  You can use below query: CREATE TABLERead More →

PRIMARY KEY and PRIMARY INDEX in TERADATA

One must not get confused between Primary Key and Primary Index in Teradata. Primary KEY is more of a logical thing however Primary INDEX is more of physical thing. In Teradata, Primary INDEX is used for finding best access path for data retrieval and data insertion and Primary KEY is used for finding each rows uniquely just like in other RDBMS. So below are few differences between PRIMARY KEY and PRIMARY INDEX: PRIMARY KEY PRIMARY INDEX 1 PRIMARY KEY cannot be NULL PRIMARY INDEX can be NULL 2 PRIMARY KEY is not mandatory in Teradata PRIMARY INDEX is mandatory In Teradata 3 PRIMARY KEY does notRead More →