Teradata SQL Test-5-Input

Teradata SQL Test – 5

Next Question is easy and the answer is easier however some of you may find it tricky. We have two tables both have just one column in it. INPUT: Tablename: TABLE1 & TABLE2 | Columnname: RDBMS1 & RDBMS2| Value: Teradata OUTPUT:   Write a generic SQL code which should give required Result. Leave your answer as comment below.

Continue Reading
Create Table in Teradata

Teradata Create Table Example with options

One of the most basic query one must know is to CREATE table in Teradata. In RDBMS, Tables are databases objects which are used to store data in form of rows and columns. Let us see a very basic example below on how to create table in Teradata and what all options are available while creating a table. Click on the Following options to see how it affects the CREATE Table in Teradata. Option 1 :SET MULTISET Option 2 :VOLATILE…

Continue Reading
Teradata Parallel Transporter - Basics, Syntax and Example

Teradata Parallel Transporter TPT – Basics , Example and Syntax

We receive so many requests for covering Teradata Utilities tutorials in our blog. The reason primarily I was not doing it because there are other blogs which already cover it. However one utility in specific I find the resources available online are not easy to understand or not very clear. I am talking about Teradata Parallel Transporter or in short TPT. This is my attempt to make it simple and easy to understand. Before we proceed with writing TPT script,…

Continue Reading
Teradata Invalid Date Error

How to find and fix Invalid Date Error in Teradata

Invalid Date is one of the most common date related error we face while loading data from Source to Target. The error is because either the source is not sending date values in format which is same as Target or the date value is not correct. Eg: If Target is expecting date in format ‘YYYY-MM-DD’ then ‘2015-02-31’ is date in format ‘YYYY-MM-DD’ however the value is not correct as February cannot have 31 as date. Other example could be ‘2015/02/25’…

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
Teradata Stored Procedures Parameters

Teradata Stored Procedures Parameters- IN , INOUT , OUT

Stored Procedures are basically blocks of SQL code which performs some functionality. However in some cases depending on situation , you may want stored procedure to perform some tasks as per your requirement. Parameters in Stored Procedure helps user to pass some information to Stored Procedure and get the corresponding result. Teradata Stored Procedure can have 3 types of parameters : IN: INPUT parameter is used to pass value to Stored Procedures. However it do not return any value. INOUT:…

Continue Reading
Teradata stored procedure activity count

Teradata stored procedure activity count

Activity_COUNT is used in Teradata Stored Procedure to keep track of the number of records impacted by any SQL Query. To check it, just use the ACTIVITY_COUNT keyword after any SQL Query which impacts rows. It is initialised to 0 at the start and value is updated after every single SQL query. Lets see an example below to see how Activity_Count works in Teradata: REPLACE PROCEDURE SP_ACTIVITYCOUNT (OUT tot_count_delete INTEGER) SQL SECURITY INVOKER BEGIN CREATE MULTISET VOLATILE TABLE vt_test1 (…

Continue Reading
Teradata Stored Procedure Access Rights

Teradata Stored Procedure Access Rights

When you write a stored procedure a very common consideration is regarding the access rights which should be granted to it. I wrote a post long back regarding SQL SECURITY option available in Teradata. Before going forward make sure to read it: SQL SECURITY OPTION FOR STORED PROCEDURE IN TERADATA . So now you are aware of four options available in Teradata for assigning proper permissions to it. Lets see a small example to check it. === Compile This Stored…

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
Table view trigger name is ambiguous

Table/view/trigger name is ambiguous – Not the obvious reason.

In some cases, we write database name explicitly in our queries however most of the time we rely on default database to select the view/table used in queries. However do you know in Teradata , you can have more than one default database for your query. Let’s look at a query below : DATABASE DB_A; SELECT a.col1 FROM table_a a INNER JOIN DB_B.table_b b ON a.col1 = b.col1 INNER JOIN table_c c ON b.col1 = c.col1; The above query will…

Continue Reading