SQL Translation Guidelines
This article provides you with an overview of what you need to take care of when you migrate your SQL statements, Views, Functions, and Stored Procedures from Teradata to Exasol, whether you are using scripts or ETL tools to load data into your data warehouse.
General Differences
Object Names / Identifiers
Object Names / Identifiers in Teradata are case insensitive. So if you have a table called Mytable in Teradata, you can reference it in a SQL statement as either
or
In Exasol, regular identifiers created without quotation marks and stored in UPPERCASE internally are insensitive, like in Teradata. Delimited identifiers in Exasol, on the other hand, are case sensitive. We highly recommend using regular identifiers for Exasol to keep the same behavior as in Teradata.
Case Sensitivity and Character Data
Depending on your Teradata installation and session settings, the sessions in Teradata either run in ANSI or Teradata mode.
If you run in ANSI mode, character data is handled as case sensitive like in Exasol. For example, ‘New York’ and ‘NEW YORK’ are two different values.
If you run in Teradata mode, character data handling is case insensitive. For example, string comparisons are handled regardless of the case, and ‘New York’ and ‘NEW YORK’ would be equal in comparison. Take a closer look at character data comparisons in your SQL Statements and adapt your statements or data accordingly (e.g., use UPPER in the comparison to keep the same semantic as Teradata ).
NULL vs. Empty String
In Teradata, the NULL value is not equal to an empty string. Exasol, on the other hand, does not distinguish between NULL and an empty string ('').
SELECT Statement
In general, most Teradata SELECT
Statements are compatible with Exasol. For example, Exasol also supports the Teradata specific QUALIFY clause.
The table below describes some of the most important differences.
Teradata | Exasol | Comment |
---|---|---|
|
|
|
|
|
In Teradata, a column alias can be used in the WHERE, GROUP BY, and HAVING, clause. In Exasol, you need to add the local keyword. |
|
|
In Teradata, the NORMALIZE keyword aggregates the overlapping or meeting periods. When using NORMALIZE ON OVERLAPS without MEETS, you need to change "<=" or ">=" in the join conditions for the dates to "<" or ">". |
|
|
Teradata supports specific comparison operators; use the ANSI Operators instead. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
UPDATE Statement
Teradata supports an extension to the ANSI SQL Standard, with the following join Syntax:
Teradata | Exasol | Comment |
---|---|---|
|
|
DELETE Statement
For deleting all records, Teradata supports a specific ALL syntax.
Teradata | Exasol |
---|---|
|
|
CREATE VIEW Statement
The main syntax for creating a view is similar to Exasol; refer to the table below. Specific Teradata keywords like LOCKING
are not supported in Exasol.
Teradata | Exasol |
---|---|
|
|
|
|
CREATE FUNCTION Statement
Scalar SQL Functions can also be migrated to Exasol using a PL/SQL Style user defined function in Exasol, refer to the example below.
The example shows that the logic itself can be easily migrated with no modifications. Only Teradata specific keywords have to be deleted, and the declaration of the function body is slightly different in Exasol.
Teradata | Exasol |
---|---|
|
|
CREATE MACRO Statement
Teradata Macros can be transformed into Exasol Scripting (LUA), refer to the example below:
Teradata | Exasol |
---|---|
|
|
|
|
CREATE PROCEDURE Statement
Stored procedures in Teradata can be transformed into Exasol. Below is an example:
Teradata | Exasol |
---|---|
|
|
|
|