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.

SQL Translation

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

select * from  MYTABLE  

or

select * from mytable.

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
SELECT TOP 10  * FROM my_table;
SELECT * FROM my_table LIMIT 10;
 
SELECT OrderQuantity * UnitPrice AS turnover
FROM
AdventureWorksDW.FactInternetSales
WHERE turnover > 1000
SELECT OrderQuantity * UnitPrice AS turnover
FROM
AdventureWorksDW.FactInternetSales
WHERE local.turnover > 1000

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.

SELECT NORMALIZE [ON OVERLAPS [OR MEETS]] Id, DatePeriod
FROM Table_1

SELECT Id, DateStart, DateEnd
FROM (
SELECT Id, MIN(MinDateStart) OVER(PARTITION BY Id, MaxDateEnd) DateStart,
MAX(MaxDateEnd) OVER(PARTITION BY Id, MinDateStart) DateEnd
FROM (
SELECT L.Id,
MIN(R.DateStart) OVER(PARTITION BY L.Id, L.DateEnd) MinDateStart,
MAX(R.DateEnd) OVER(PARTITON BY L.ID, L.DateEnd) MaxDateEnd
FROM Table_1 L LEFT JOIN Table_1 R
ON L.ID = R.ID 
AND L.DateStart <= R.DateEnd
AND L.DateEnd >= R.DateStart
)
)
GROUP BY Id, DateStart, DateEnd

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 ">".

EQ, LE, LT , NE, GE, GT
=, <=, <, !=, >= , > 

Teradata supports specific comparison operators; use the ANSI Operators instead.

x LIKE ANY('%a','%b','%c')
x LIKE '%a' OR x LIKE '%b' OR x LIKE '%c'
 
x LIKE ALL('%a','%b','%c')
x LIKE '%a' AND x LIKE '%b' AND x LIKE '%c'
 
OREPLACE
REGEXP_REPLACE / REPLACE
 
STRTOK
REGEXP_SUBSTR
 
INDEX
INSTR
 
LAST_DAY(x)
ADD_DAYS(ADD_MONTHS( x ,1),-1)
 
td_day_of_month(x)
EXTRACT(DAY from x)
 

UPDATE Statement

Teradata supports an extension to the ANSI SQL Standard, with the following join Syntax:

Teradata Exasol Comment
UPDATE U
FROM staff AS U, staff_updates AS V
SET U.salary=V.salary, U.currency=V.currency
WHERE U.name=V.name;
UPDATE staff AS U
SET U.salary=V.salary, U.currency=V.currency
FROM staff AS U, staff_updates AS V
WHERE U.name=V.name;
 

DELETE Statement

For deleting all records, Teradata supports a specific ALL syntax.

Teradata Exasol
DELETE table1 ALL;
DELETE FROM table1 WHERE TRUE;

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 VIEW my_view 
AS SELECT ...
;
CREATE VIEW my_view AS 
SELECT ...
;
REPLACE VIEW my_view 
AS SELECT ...
;
CREATE OR REPLACE VIEW my_view AS 
SELECT ...
;

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
REPLACE FUNCTION ExasolMigrationTest.calc (func VARCHAR(1), A INTEGER, B INTEGER)  
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN CASE  
  WHEN func = 'A'  
  THEN A + B
  WHEN func = 'S' 
  THEN A - B
  WHEN func = 'M'               
  THEN A * B
  ELSE A / B
 END;    

CREATE OR REPLACE FUNCTION calc (func VARCHAR(1), A INTEGER, B INTEGER
RETURN INTEGER 
IS
BEGIN      
RETURN CASE 
   WHEN func = 'A'               
   THEN A + B
   WHEN func = 'S' 
   THEN A - B  
   WHEN func = 'M' 
   THEN A * B
   ELSE A / B             
  END;   
 END
   
/

CREATE MACRO Statement

Teradata Macros can be transformed into Exasol Scripting (LUA), refer to the example below:

Teradata Exasol
CREATE MACRO new_employee (
 num   INTEGER
 name     VARCHAR(12), 
 dept     INTEGER   ,
 pos VARCHAR(12) ,
 dob      DATE   
)  AS
(INSERT INTO employee (empno,name,deptno,jobtitle,dob)
 VALUES (:num, :name, :dept, :pos, :dob);
 --   The following select verifies the insert      
 SELECT * 
 FROM employee 
 WHERE empno = :num; );
--/
CREATE SCRIPT new_employee (num, name, dept, pos, dob) RETURNS TABLE AS

query([[INSERT INTO employee (empno,name,deptno,jobtitle,dob) VALUES (:num, :name, :dept, :pos, :dob); ]],
{num=num, name=name, dept=dept, pos=pos, dob=dob} 
)

--   The following select verifies the insert        
return query( [[SELECT * FROM employee WHERE empno = :num;]], {num=num} )

/
EXECUTE NEW_EMPLOYEE(1, 'Paul Miller', 2, 'Manager','2020-10-20');
EXECUTE SCRIPT NEW_EMPLOYEE(1, 'Paul Miller', 2, 'Manager','2020-10-20');

CREATE PROCEDURE Statement

Stored procedures in Teradata can be transformed into Exasol. Below is an example: 

Teradata Exasol
CREATE PROCEDURE new_sales_table
(my_table VARCHAR(30), my_database VARCHAR(30))
BEGIN
DECLARE sales_columns 
VARCHAR(128
DEFAULT '(item INTEGER, price DECIMAL(8,2), sold INTEGER)' ;
DECLARE sqlstr VARCHAR(500);
SET sqlstr = 'CREATE TABLE ' || my_database ||  '.' || my_table || sales_columns ;
EXECUTE IMMEDIATE sqlstr;h2.
END;
--/
CREATE SCRIPT new_employee (num, name, dept, pos, dob) RETURNS TABLE AS

query([[INSERT INTO employee (empno,name,deptno,jobtitle,dob) VALUES (:num, :name, :dept, :pos, :dob); ]],
{num=num, name=name, dept=dept, pos=pos, dob=dob} 
)

--   The following select verifies the insert        
return query( [[SELECT * FROM employee WHERE empno = :num;]], {num=num} )

/
CALL new_sales_table ('Sales_stage', 'DWH')
EXECUTE SCRIPT new_sales_table ('Sales_stage','DWH')