You can translate the document:

Goal

This document is a quick reference for migrating Teradata SQL to Denodo VQL. The document is aimed at administrators and developers that want to efficiently migrate their existing Teradata SQL queries to VQL queries.

Content

The Knowledge Base article VDP Conformance with Standard SQL contains a reference of the Virtual DataPort conformance with the SQL 92 standard. The document is focused on query capabilities and contains information about: Data Types, SQL Predicates Support and SQL Functions Support. The Query Expressions section lists the expressions defined by the standard SQL and their equivalent in Virtual DataPort, explaining the differences with the standard when appropriate.

Following, a group of tables where the correspondence between Teradata functions and the equivalent Denodo VQL functions is presented. This list is just a reference since more functions can be included in future Denodo versions.

Functions mapping

Denodo function

Teradata function

addday(daterelatedvalue, inc)

date + CAST( inc AS INTERVAL DAY(4))

addhour(timerelatedvalue, inc)

date + CAST( inc AS INTERVAL HOUR(4))

addminute(timerelatedvalue, inc)

date + CAST( inc AS INTERVAL MINUTE(4))

addsecond(timerelatedvalue, inc)

date + CAST( inc AS INTERVAL SECOND(4))

getday(daterelatedvalue)

EXTRACT(DAY FROM date)

cast(data_type, value)

CAST(value as data_type)

getdayofyear(daterelatedvalue)

CAST(CAST(CAST(date AS DATE FORMAT 'D3') AS VARCHAR(3)) AS INTEGER)

getdaysbetween(daterelatedvalue1, daterelatedvalue2)

CAST(CAST(date2 AS DATE) -CAST(date1 AS DATE) AS INTEGER)

gethour(timerelatedvalue)

EXTRACT(HOUR FROM date)

getminute(timerelatedvalue)

EXTRACT(MINUTE FROM date)

getsecond(timerelatedvalue)

EXTRACT(SECOND FROM date)

getmillisecond(timerelatedvalue)

cast(((EXTRACT(second from

 date)*1000) MOD 1000) AS BIGINT)

getmonth(daterelatedvalue)

EXTRACT(MONTH FROM date)

getyear(daterelatedvalue)

EXTRACT(YEAR FROM date)

now()

CURRENT_TIMESTAMP

current_date

CURRENT_DATE

power(number, power)

number ** power

len(arg0)

CHARACTER_LENGTH(arg0)

mod(arg0, arg1)

arg0 MOD arg1

concat(arg0, arg1 [,argi]*)

arg0 [ || argi]{1, n}

ltrim(arg0)

trim(LEADING from arg0)

rtrim(arg0)

trim(TRAILING from arg0)

instr(arg0, arg1)

(index(arg0, arg1) -1)

lower(arg0)

lower(CAST(arg0 AS VARCHAR(4000)))

upper(arg0)

upper(arg0)

addmonth(daterelatedvalue, inc)

add_months(date, cast(inc as BIGINT))

addyear(daterelatedvalue, inc)

add_months(date, cast((inc*12) as bigint))

to_date(date_pattern, value)

CAST(value AS TIMESTAMP FORMAT date_pattern)

formatdate(date_pattern, date)

CAST(CAST(date AS TIMESTAMP FORMAT date_pattern) AS VARCHAR(50))

log(arg0)

log(arg0)

atan2(arg0, arg1)

atan2(arg1,arg0)

round(arg0)

CAST(arg0 AS DECIMAL(31,PRECISSION_PARAM))

substr(arg0,arg1)

SUBSTR(arg0,arg1)

SUBSTRING(arg0,arg1)

substr(arg0,arg1,arg2)

SUBSTR(arg0,arg1,arg2)

SUBSTRING(arg0,arg1,arg2)

position(arg0 IN arg1)

position(arg0 in arg1)

replace(arg0, arg1, arg2)

replace(arg0, arg1, arg2)

ceil(arg0)

ceil(arg0)

floor(arg0)

floor(arg0)

regexp(arg0, arg1, arg2)

regexp_replace(arg0, arg1, arg2,1,0,'MATCH_PARAM')

removeaccents(arg0)

oreplace(oreplace(oreplace(oreplace(oreplace( oreplace(oreplace(oreplace(oreplace(oreplace( oreplace(oreplace(oreplace(oreplace(oreplace( oreplace(oreplace(oreplace(oreplace(oreplace( oreplace(oreplace(oreplace(oreplace(oreplace( oreplace(oreplace(oreplace(oreplace(oreplace( oreplace(oreplace(oreplace(oreplace(oreplace( oreplace(oreplace(oreplace(oreplace(oreplace( oreplace(oreplace(oreplace(arg0,

 'Ý', 'Y'), 'Û', 'U'), 'Ô', 'O'), 'Î', 'I'), 'Ê', 'E'), 'Â', 'A'), 'Ü',

'U'), 'Ö', 'O'), 'Ï', 'I'), 'Ë', 'E'), 'Ä', 'A'), 'Ù', 'U'), 'Ò', 'O'),

'Ì', 'I'), 'È', 'E'), 'À', 'A'), 'Ú', 'U'), 'Ó', 'O'), 'Í', 'I'), 'É',

'E'), 'Á', 'A'), 'ý', 'y'), 'ÿ', 'y'), 'û', 'u'), 'ô', 'o'), 'î', 'i'),

'ê', 'e'), 'â', 'a'), 'ü', 'u'), 'ö', 'o'), 'ï', 'i'), 'ë', 'e'), 'ä',

'a'), 'ù', 'u'), 'ò', 'o'), 'ì', 'i'), 'è', 'e'), 'à', 'a'), 'ú', 'u'),

'ó', 'o'), 'í', 'i'), 'é', 'e'), 'á', 'a')

In the above list, there are different types of data and time functions:

  • Functions that are related with datetime types that includes dates (date, localdate, timestamp, timestamptz), will be referred as daterelatedvalue
  • Functions that are related with datetime types that includes times (date, time, timestamp, timestamptz), will be referred as timerelatedvalue
  • Functions that receive any datetime values, except intervals, will be referred as datetimevalue

Aggregation functions

Denodo function

Teradata function

var(arg0)

var_samp(cast(arg0 as DECIMAL(38)))

varp(arg0)

var_pop(cast(arg0 as DECIMAL(38)))

stdev(arg0)

stddev_samp(cast(arg0 as DECIMAL(38)))

stdevp(arg0)

stddev_pop(cast(arg0 as DECIMAL(38)))

Operators

Denodo function

Teradata operator

arg0 is false

arg0 = 0

arg0 is true

arg0 = 1

Disclaimer
The information provided in the Denodo Knowledge Base is intended to assist our users in advanced uses of Denodo. Please note that the results from the application of processes and configurations detailed in these documents may vary depending on your specific environment. Use them at your own discretion.
For an official guide of supported features, please refer to the User Manuals. For questions on critical systems or complex environments we recommend you to contact your Denodo Customer Success Manager.

Questions

Ask a question

You must sign in to ask a question. If you do not have an account, you can register here