While calculating measures you may want to convert seconds to TIME. Say you want to know how many HOURS,MINUTES & Seconds constitute this number. Example you may want to convert 500 seconds to hh:mi:ss format. This can be done easily by using “INTERVALs”. Let’s quickly see how to do it This means 5000 seconds means 1 hour 23 minutes and 20 seconds.Another way of writing same command: Let’s increase the value of number (i.e. seconds) This means 500000 seconds constitutes 5 Days 18 Hours 53 Minutes and 20 Seconds.You can change the INTERVAL RANGE to get output in desired format. Like I just want outputRead More →

Recently someone in my team asked me how to fetch column list along with datatype from VIEW definition via jdbc connection. I proposed couple of solutions to him however due to existing code base he was looking for solution using “HELP” command only. Now if you know there are multiple HELP command which can give insight into object details. For more details you can read this post- Teradata HELP commands. However for views you may get column names but datatype may be missing. Since the expected solution is via HELP command only, I proposed to create a volatile table from view and use HELP TABLERead More →

In most of the Teradata environment I have seen the preferred medium to call BTEQ is shell script. It means that Teradata developer will write a shell script which will invoke BTEQ utility to run SQL commands on Teradata. In this post, we will see how to write a sample BTEQ script using Shell and run it on Teradata. To start with, I would like to discuss why we shall pick Shell script to call BTEQ and why not directly the BTEQ script. BTEQ supports plain ANSI SQL with few TD extension to SQL. This means that PL/SQL constructs like CURSOR , conditional statements, logicalRead More →

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 →