Teradata has adopted a variety of analytical functions from Teradata Aster in the latest releases. While most functions are, in our opinion, very specific to web click analysis, Teradata Antiselect is quite useful for specific purposes. What is the Teradata Antiselect function? With the analytical function called Teradata Antiselect, Teradata offers a possibility to reverse

Read More

Problem-solving without a Teradata Recursive Query In our example, we want to represent a corporate hierarchy. Without recursion, this can be done, e.g., with the query shown below: SELECT — Concatenation of all Companies CASE WHEN Level >= 3 THEN TRIM(t03.Company_Id) || ‘.’ ELSE ” END || CASE WHEN Level >= 2 THEN TRIM(t02.Company_Id) ||

Read More

What is Teradata Query Rewriting? Teradata query rewriting is part of the Teradata optimization process. The optimizer replaces your query with a better version, which is assumed to be less resource-intense and, hopefully, faster. Of course, both queries have to deliver the same result set.  To achieve better performance, Teradata utilizes several optimization strategies. We will

Read More

It is time to publish another case study about Teradata SQL tuning,  which impressively demonstrates the terrific impact of query rewriting on performance. Our object of study is the query below, which initially ran about 40 minutes. As usual, I added all missing statistics and ensured that no statistic is stale: Something a SQL tuning specialist

Read More

The Idea Behind This Trick for UNION ALL What if you want to perform a UNION ALL on two different columns of the same table? Normally you would do this: SELECT CloseDate FROM Customer UNION ALL SELECT OpenDate FROM Customer ; However, this method has a disadvantage: The table Customer must be queried with two

Read More

What is the Teradata Sample Function? The SAMPLE function returns a number of rows that were randomly selected. These are the features: Ask for a sample with an absolute number of rows Ask for a sample which contains a certain percentage of the table rows  Ask for more than one sample at the same time

Read More

Teradata SUBSTR Or SUBSTRING? The Teradata SUBSTRING or SUBSTR function is one of the Teradata string functions and used to cut a substring from a string based on its position. We can use ANSI syntax (Teradata SUBSTRING) or Teradata syntax (Teradata SUBSTR). The ANSI syntax is recommended to remain compatible with other database systems. ANSI

Read More

UNION and UNION ALL The Teradata UNION statement combines the result set of at least two queries; in addition, UNION removes row duplicates. Each of the select statements must pick the same number of columns, the columns must be selected in the same order, and the datatypes of the column of all select statements must

Read More

The Teradata TO_CHAR Function The Teradata TO_CHAR function can be used to: Convert numeric expressions to character strings Convert dates, times, interval data types, or timestamps to character strings Here is an example of a numeric expression argument. It can be any of these data types: BYTEINT, SMALLINT, INTEGER, BIGINT, DECIMAL/NUMERIC, FLOAT/REAL/DOUBLE PRECISION, or NUMBER.

Read More