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 →