Few years ago, I shared a post to “Convert Multiple Rows into Single Row” using recursive queries. Though it solves the problem and gives the correct desired result, the solution was not straightforward. Ideally combining multiple rows into single value should not be lengthy solution. But at that time, it seems like the only solution. With the newer version of Teradata, we have couple of functions available which can help in combining rows into a single line. In this post, we will discuss about 2 such UDF which can give single value for multiple rows. First, XMLAGG: With Teradata supporting various operations on XML datatypeRead More →

ROWS BETWEEN is used in Teradata to determine group for calculating aggregation. Whenever you use window functions or OLAP functions you specify the rows which shall be considered for calculating measures. If you don’t specify anything then by default all the rows in the partition participate in calculating measures. Some of the common window functions used are MIN, MAX, SUM, AVG, COUNT. As we specify range in usual BETWEEN clause similarly we specify range in ROWS BETWEEN in OLAP functions. RANGE must always be from start to end i.e. Start must be before End. Some common specifications are: UNBOUNDED PRECEDING: All rows before current rowRead More →

In SCD type 2, we have to store all the historical information about any Dimension. So unlike SCD-1, we cannot simply overwrite the updated data we are getting from Source. There are multiple ways in which one can implement SCD-2 in Teradata and we will discuss about couple of most popular approach in this post. Approach 1: Create 2 technical DATE/TIMESTAMP columns. If your job is intra-day I will suggest keeping timestamp else you can keep DATE for daily jobs. Let’s call it INSERT_DT & UPDATE_DT.Firstly, you have to run UPDATE on existing TARGET table with records from SOURCE. This is required to identify andRead More →

So often we come across situation to load data file into Teradata table. Also very frequently we extract data to flat file from Teradata table as well. Occasionally we may have to load data from one Teradata table to another in different servers. The first thing which comes to a developer mind is Teradata PT. However traditionally , developer have been writing lengthy TPT scripts for these activities. To make life simple TPT Easy loader is available for developer which internally calls TPT and creates TPT script dynamically and runs it. To check if you have tdload client installed on your machine , just typeRead More →

Very often when working with BTEQ script you will face requirement to use dynamic variables or parameters. This is not possible in plain SQL without using PL/SQL construct in Teradata. However if you are calling BTEQ code in shell script you can easily use shell variables in BTEQ SQL queries. Let’s quickly see an example to understand this. I have below BTEQ script: Filename: abc.bteq .logon 192.168.100.100/dbc,dbc select InfoKey,InfoData from dbc.dbcinfo; .quit Command to run: bteq < abc.bteq Now you can see I have hardcoded SQL query in the BTEQ. What if I want to populate column name , database name or tablename as theRead More →

In SCD-1, we overwrite the existing information in the table with the most recent info received from Source. This is very common SCD type used in the data warehouse and is good only for situations when you don’t want to keep historical data for that entity. Let’s quickly jump to technical example to understand it: Create Target Table and load it with sample data: SyntaxEditor Code Snippet drop table customer; create table customer ( c_mobile bigint, c_name varchar(30), c_dob date, c_email varchar(100), c_insert_dt date, c_update_dt date ) primary index(c_mobile); insert into customer values (111111111,’Jon Groff’,’1980-03-10′,,date,date); insert into customer values (222222222,’Kenneth Jarrett’,’1986-08-24′,,date,date); insert into customer valuesRead More →