SQL Translation Guidelines

This section 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

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.

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

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

EQ, LE, LT , NE, GE, GT

=, <=, <, !=, >= , > 

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:

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.

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.

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.

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:

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: 

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')