Goal

This document is a reference of the Virtual DataPort conformance with the SQL 92 ANSI standard.

The document is focused on the query capabilities defined in this standard.

This document is aimed at administrators and developers that already have a deep knowledge of the Virtual DataPort Query Language (VQL).

Data Types

The following table contains the equivalent type in Virtual DataPort of each SQL type.

In Denodo Versions less than 7.0:

SQL Type Name	Equivalent Type in Virtual DataPort	Remarks
DATE TIME TIME WITH TIME ZONE TIMESTAMP TIMESTAMP WITH TIME ZONE	date
INTERVAL	Not supported	You can use the functions ADDYEAR, ADDMONTH, ADDDAY, ADDHOUR… to perform similar operations to the ones you can do with the INTERVAL data type.
CHARACTER CHARACTER VARYING {      NATIONAL CHAR  | NATIONAL CHARACTER  | NCHAR } {    NATIONAL CHAR    VARYING  | NATIONAL CHARACTER    VARYING  | NCHAR VARYING } VARCHAR	text	The type represents character strings of any character set, including multibyte Unicode ones.
BIT BIT VARYING	blob
INTEGER NUMERIC SMALLINT	int or long	The precision of these data types is the same as the Java primitives with the same name. The minimum value of an int is -231 and the maximum value is +231-1. The minimum value of a long value is -263 and the maximum value is +263-1.
DECIMAL DOUBLE PRECISION FLOAT REAL	float or double	These data types have the same precision as the Java primitives with the same name. The range of values of float and double values is beyond the scope is this document, but is specified in the Floating-Point Types, Formats and Values section of the Java Language Specification         (http://docs.oracle.com/javase/specs/jls/se7/html/jls-4.html#jls-4.2.3)

Addition to the above, support for more data types are added in Denodo 7.0:

SQL Type Name	Equivalent Type in Virtual DataPort	Remarks
DATE	localdate	Date without a timezone
TIME	time
TIMESTAMP	timestamp
TIMESTAMP WITH TIMEZONE	timestamptz
INTERVAL	intervaldaysecond	duration in terms of days, hours, minutes and seconds
INTERVAL	intervaldaymonth	duration in terms of years and months

Type conversion from JDBC sources to Virtual DataPort

Virtual DataPort also supports the following additional types:

• boolean, which is not defined by the standard SQL.
• decimal, a type to represent decimal numbers with big precision.

The numeric types in Virtual DataPort do not have scale or precision as in the standard SQL so you should use the appropriate type depending on your data requirements.

SQL Predicates Support

Predicates are conditions that can be evaluated to return true or false.

The following sections list the predicates supported and not supported by Virtual DataPort.

Arithmetic Operators

Virtual DataPort supports all the arithmetic operators defined by the standard SQL: +, -, * and /.

Virtual DataPort also defines an alias for these operators (see the table below).

These operators can be used with values of the types int, long, float, double and money.

SQL Operator	Alias in Virtual DataPort
+	SUM (value, value [, value]* )
-	SUBTRACT (value, value [, value]* )
*	MULT (value, value [, value]* )
/	DIV (value, value [, value]* )

Aliases of arithmetic operations

A numeric expression can be prefixed with the sign “-” to reverse the sign of the operand. For example:

SELECT – numeric_field_name FROM V

If numeric_field_name is 1, (– numeric_field_name) is -1.

Predicate Operators

Specify a condition that can be evaluated to give a truth-value.

• <boolean value> OR <boolean value>
• <boolean value> AND <boolean value>
• NOT (<boolean value>)
• IS <boolean value>

The standard SQL defines the “truth value” unknown. Virtual DataPort only supports true and false.

Comparison Operators

Virtual DataPort supports the following comparison operators defined by the standard SQL:

•  = (equal)
• <> (not equal)
• < (less than)
• > (greater than)
• <= (less than or equal)
• >= (greater than or equal)

Other predicates supported by Virtual DataPort:

• <a> BETWEEN <b> AND <c>
• <a> IN <b>, <c>, <d>…
• <a> LIKE <pattern>        

The syntax <a> LIKE <pattern> ESCAPE <escape character> is not supported.

• <a> IS NULL
• <a> <operator> { ALL | ANY } <subquery>        

The operator SOME is not supported, but you can use the operator ANY instead as it has the same meaning.

• EXISTS: see section “Subqueries in the WHERE Clause” in this document.

Unsupported Predicates

The following list contains the predicates not supported by Virtual DataPort.

• UNIQUE <subquery>
• <a> MATCH [ UNIQUE ] [ PARTIAL | FULL ] <subquery>
• <a> OVERLAPS <b>

As Virtual DataPort does not support date-time intervals, it cannot support this predicate.

SQL Functions Support

The following sections list the functions defined by the standard SQL and their equivalent in Virtual DataPort. These sections explain the syntax of each function in Virtual DataPort and when appropriate, the differences with the standard.

Virtual DataPort defines more functions than the standard. For more information about them, read the sections “Functions for Conditions and Derived Attributes” and the appendix “Syntax of Condition Functions” of the Advanced VQL Guide.

Character String Operations

CHAR_LENGTH,CHARACTER_LENGTH,OCTET_LENGTH,BIT_LENGTH

These functions return the length of a value

Syntax

SQL Function	Virtual DataPort Equivalent
CHAR_LENGTH(value) CHARACTER_LENGTH(value)	LEN (value)

Remarks

If the value is NULL, all the functions return NULL.

OCTET_LENGTH and BIT_LENGTH are currently not supported by Virtual DataPort.

LOWER

Converts text to lowercase.

Syntax

LOWER( <character value> ) : <character value>

The syntax of this function conforms to the standard.

Remarks

• LOWER returns NULL when the input value is NULL.

POSITION

Returns the index of a string within another string.

Syntax

SQL Function	Virtual DataPort Equivalent
POSITION(<string value> IN <string value>)	INSTR(<string value>, <text to search:string value>)

Remarks

• For INSTR, the index of the first character is 0 and for POSITION… IN, is 1.
• If any of the parameters is NULL, the function returns NULL.
• INSTR returns -1 if it does not find <string value>. In this case, POSITION… IN returns 0.
• INSTR returns 0 if <string value> has a length of 0. In this case, POSITION… IN returns 1.

SUBSTRING

Returns a substring of a string.        

Syntax

SUBSTRING(

<character value> FROM <start position: numeric>  

[ FOR <length : numeric> ] )

The syntax of this function conforms to the standard.

Remarks

• If any of the parameters is NULL, the function returns NULL.

TRIM

Returns the input parameter without its leading and trailing space characters.

Syntax

SQL Function	Virtual DataPort Equivalent
TRIM ( <character value> )	TRIM( <text value> )
TRIM ( BOTH FROM        <character value> )	TRIM( <text value> )
TRIM ( LEADING FROM        <character value> )	LTRIM( <text value> )
TRIM ( TRAILING FROM        <character value> )	RTRIM( <text value> )
TRIM (     { BOTH | LEADING | TRAILING }           <trim character>     FROM <character value> )	TRIM([{BOTH | LEADING | TRAILING} [trimcharacter] from  | trimcharacter from] value)

Remarks

• The function returns NULL if the input is NULL.

UPPER

Converts text to uppercase.

Syntax

UPPER( <character value> )

The syntax of this function conforms to the standard.

Remarks

• UPPER returns NULL when the input value is NULL.

UNSUPPORTED TEXT VALUES FUNCTIONS

The following functions are not supported by Virtual DataPort:

• CONVERT… USING 
• TRANSLATE … USING: the Virtual DataPort functions REPLACE and REPLACEMAP provide a similar result.

DATE VALUES FUNCTIONS

SQL Function	Virtual DataPort Equivalent	Remarks
CURRENT_DATE	CURRENT_DATE	This function returns a “date” value with the fields hour, minute, second and milliseconds set to zero.
CURRENT_TIME		Although this function is not supported, a similar result can be achieved with the functions EXTRACT(…) and NOW().
CURRENT_TIMESTAMP	CURRENT_TIMESTAMP	This function returns a “date” value.
EXTRACT (… FROM <value> )	EXTRACT ( {  YEAR  | MONTH  | DAY  | HOUR  | MINUTE  | SECOND } FROM <value:date> )	Returns NULL if value is NULL.
OVERLAPS	Not supported	Virtual DataPort does not support the type “interval of dates”.

Date processing functions

TYPE CONVERSION FUNCTIONS

CAST

Syntax

CAST ( <value> AS <type> )

Remarks

The syntax of the function in Virtual DataPort is the same as in the SQL-99. The value of <type> can be the name of a Virtual DataPort type or a standard SQL type:

<type> Value	Output Type
array	array
text, blob	blob
text, int, long, float, double, boolean	boolean
text, date, long	date
text, int, long, float, double, money	double
text, enumerated	enumerated
text, int, long, float, double, money	float
text, int, long, float, double, money	int
text, int, long, float, double, money	long
text, int, long, float, double, money, date	money
xml, register	register
text, int, long, float, double, boolean, date, xml, money, link, blob, enumerated, register, array	text
text, blob, xml, register, array	xml

Type conversion performed by the function CAST (Virtual DataPort types)

<type> value SQL Type	Output type
CHAR (n)	text
CHARACTER (n)	text
CHARACTER VARYING (n)	text
VARCHAR (n)	text
NCHAR (n)	text
NVARCHAR (n)	text
BIT (n)	blob
BIT VARYING (n)	blob
INTEGER	int
SMALLINT	int
FLOAT	float
REAL	float
DOUBLE PRECISION	double
NUMERIC	double
NUMERIC (n)	double
DECIMAL	double
DECIMAL (n)	double
DATE	date
TIMESTAMP	date timestamp(in Denodo versions >= 7.0)
TIMESTAMP WITH TIME ZONE	Date timestamp(in Denodo versions >= 7.0)

Type conversions performed by the function CAST (standard SQL types)

In some scenarios, Virtual DataPort converts a value to the required type. E.g., Let us say that we have a view internet_inc with a field iinc_id of type int.

The following query works because Virtual DataPort automatically converts <string value> into the appropriate data type (int).

SELECT * FROM internet_inc WHERE iinc_id = '<string value>'

According to the standard, to round a number you have to use the CAST function. For example, CAST(0.9681 as INTEGER) returns 1 and CAST(0.9681 AS NUMERIC(9,2)) returns 0.93.

To do this in Virtual DataPort, instead of using CAST, you have to use the ROUND function. See more about this function in the appendix Round of the Advanced VQL Guide.

Aggregation functions

The following table lists the aggregation functions defined by the standard SQL. Virtual DataPort supports these functions and others. See more information about them in the appendix Aggregation functions of the Advanced VQL Guide.

SQL Function	Description
AVG	Returns the average of the non-null values of an attribute of the table
COUNT	Returns the number of non-null values of an attribute of the table
MAX	Returns the highest value of an attribute for each group of values
MIN	Returns the lowest value of an attribute for each group of values
SUM	Returns the sum of all non-null values of an attribute for each group of values

Standard SQL aggregation functions supported by Virtual DataPort

QUERY EXPRESSIONS

The following sections list the expressions defined by the standard SQL and their equivalent in Virtual DataPort. These sections explain the syntax of each expression in Virtual DataPort and when appropriate, the differences with the standard.

EXCEPT

In Virtual DataPort, the expression EXCEPT is called MINUS. This operation returns all the rows returned by the first query except the rows that are also returned by the second query.

Syntax

<query 1> MINUS <query 2>

Remarks

The modifier ALL in the operation MINUS is not supported.

GROUP BY

The GROUP BY operation groups the results of a query by the values of the fields of the view, generating a row for each group. The attributes and expressions with which the GROUP BY operation is performed are specified in the GROUP BY clause.

The queries with GROUP BY can only project the attributes specified in the GROUP BY. The other fields of the view can be used only as parameters of aggregation functions.

Syntax

SELECT…

FROM…

[ GROUP BY <group by field> [ , <group by field> ]* ]

<group by field> ::= { <field name> | <expression> }

When the GROUP BY clause is missing, the entire result of the query is considered a single group.

Remarks

The standard defines that <group by field> is a field name. In Virtual DataPort, it can be also one of the following:

•  An alias of a projected field. For example,

SELECT (field1 + 1) AS alias1, COUNT(*)  

FROM view1

GROUP BY alias1 

In this example, the GROUP BY operation groups by “field1 + 1”.

•  A position of a field in the SELECT clause of the query. For example,             

SELECT f1, f2, f3

FROM view1

GROUP BY 2

In this example, the GROUP BY operation groups by the field f2.

HAVING

The HAVING clause is used with GROUP BY to remove the results returned by the GROUP BY that do not meet the <search condition>.

Syntax

SELECT…

FROM…

GROUP BY…

HAVING <search condition>

Remarks

In Virtual DataPort, <search condition> cannot be a subquery.

INTERSECT

The intersect operation returns the common elements of the result of two or more input queries.

Syntax

<query 1> INTERSECT <query 2>

Remarks

The modifier ALL in the operation INTERSECT is not supported.

JOIN

The JOIN operation combines rows from two views.

Syntax

SELECT…

FROM

      <view 1> [ <join type> ] JOIN <view 2> ON <join condition>

    | <view 1> NATURAL JOIN <view 2>

    | <view 1> JOIN <view 2> USING ( <field> [, <field> ]* )

    | <view 1> CROSS JOIN <view2>

<join type> ::= INNER | LEFT [ OUTER ] | RIGHT [ OUTER ] | FULL [ OUTER ]

<view1> and <view2> can be either a view or a subquery.

Extended syntax

The following syntax includes the clauses to set implementation details of the join operation:

• Algorithm used to execute the join. The query engine of Virtual DataPort tries to select the most appropriate algorithm for each scenario. However, you can force it to use a different algorithm. See the Virtual DataPort Administration Guide for more details about this.

• HASH
• MERGE
• NESTED
• NESTED PARALLEL

• <order> is only relevant when the JOIN is a LEFT OUTER JOIN or RIGHT OUTER JOIN.        

If ORDERED, the “left view” is <view A>.        

If REVERSEORDER, the “left view” is <view B>.

SELECT…

FROM

      <view 1> [ <algorithm> ] [ <order> ] [ <join type> ]

      JOIN <view 2> ON <condition>

    | <view1> NESTED PARALLEL [ <order> ] [ <join type> ]

      JOIN [ <parallel number:integer> ] <view 2> ON <condition>

    | <view1> [ <algorithm> ] [ <order> ]

      NATURAL [ <join type> ] JOIN <view 2>

    | <view 1> NESTED PARALLEL [ <order> ]

      NATURAL [ <join type> ] JOIN [ <parallel number:integer> ]

      <view 2>

    | <view 1> [ <algorithm> ] [ <order> ] [ <join type> ]

      JOIN <view 2> USING ( <field> [, <field> ]* )

    | <view 1> NESTED PARALLEL [ <order> ] [ <join type> ]

      JOIN [ <parallel number:integer> ] <view 2>

      USING ( <field> [, <field>]* )

    | <view 1> CROSS JOIN <view 2>

<algorithm> ::= HASH | NESTED | MERGE

<order>  ::= ORDERED | REVERSEORDER

ORDER BY

ORDER BY sorts the results by one or more of their fields.

Syntax

SELECT…

FROM…

[ ORDER BY <field name> [ ASC | DESC ]

           [, <field name> [ ASC | DESC ] ]* ]

When the sort direction (ASC or DESC) is not present, ORDER BY sorts in ascending order.

Remarks

Virtual DataPort supports using the ORDER BY expression in a nested query. For example:

SELECT v.*, ROWNUM()

FROM

    (SELECT DISTINCT id, description, ttime, state

     FROM incidents

     ORDER BY cif

) AS v

SELECT

Selects the fields to project from the result obtained by the query.

Syntax

SELECT [ DISTINCT ] <select fields>

FROM…

<select fields> ::=

    {

      *

    | <select sublist> <as clause>

      [, <select sublist> <as clause> ]*

<select sublist> ::= <derived column> | <view name>.*

<derived column> ::= { <expression> | <field name> } [ <as clause> ]

<as clause> ::= [ AS ] <column name>

Remarks

The modifier ALL is not supported.

In Virtual DataPort, you can not place subqueries in the SELECT clause.

UNION

The UNION operation returns the union of the tuples returned by two queries.

Syntax

<query 1> UNION [ ALL ] <query2>

Remarks

The ALL modifier of the UNION operation is ignored, which means that the result of the operation may return repeated rows.

The schema of <query 1> does not have to match the schema of <query 2>. If it does not, the schema of the result is formed by the fields of <query 1> and the fields of <query 2> that are not present in <query 1>.

WITH (COMMON TABLE EXPRESSIONS)

A common table expression (CTE) is a temporary result set that is defined and used for the duration of an SQL statement.

The main advantage of a CTE is that it improves the readability and maintenance of complex queries. The query can be divided into separate “building blocks”, which can then be used to build more complex queries.

Syntax

WITH <expression name> [ ( <column name> [, <column name> ]+ ) ]

AS ( <query> )

SELECT <column list>

FROM <expression name>

If <column name> is not specified, the names of the fields of the expression are the names of the schema of <query>.

For example, the following statement finds the department with the lowest total pay.

WITH department_salary (deptno, totalpay) AS

    (SELECT deptno, SUM(salary)

     FROM EMP

     GROUP BY deptno)

SELECT deptno

FROM department_salary

WHERE totalpay = (

    SELECT max(totalpay)

    FROM department_salary

)

Remarks

Virtual DataPort supports using the WITH clause in the SELECT and the CREATE VIEW statements. It is not allowed in subqueries or in INSERT statements.

SUBQUERIES

The following sections explain how to include subqueries in the FROM and WHERE clauses of a query.

Note that, as explained in this document, Virtual DataPort does not currently support subqueries in the clauses SELECT and HAVING.

SUBQUERIES IN THE FROM CLAUSE

In a query, the elements that form the FROM clause can be either the name of a view or a query.

Example

SELECT * FROM (SELECT * FROM a)

SUBQUERIES IN THE WHERE CLAUSE

In a query, the conditions of the WHERE clause can be built using the result of a subquery.

See more about this in the section “Subqueries in the WHERE Clause of the Query” of the Advanced VQL Guide.

Example 1

SELECT * FROM incidences

WHERE taxid IN

    (SELECT taxid

     FROM flat_revenue

     WHERE revenue > 2500)

Example 2

SELECT *

FROM internet_inc AS a

WHERE EXISTS

    (SELECT b.PINC_ID

     FROM PHONE_INC AS b

     WHERE a.iinc_id = b.pinc_id)

In Example 2, the query uses the alias of the main query (internet_inc AS a) in the WHERE clause of the subquery.

QUERY EXPRESSIONS NOT DEFINED IN SQL-92

FETCH AND OFFSET

The OFFSET clause skips the first N rows of a query’s result.

OFFSET is usually used with FETCH to obtain a subset of rows of a table sorted by a primary key. It is used in scenarios where we need to paginate the contents of the query.

Syntax

SELECT…

FROM…

ORDER BY <primary key fields>

[ OFFSET <number> { ROW | ROWS } ]

[ FETCH { FIRST | NEXT } [ <number> ] { ROW | ROWS } ONLY

The modifiers FIRST and NEXT and ROW and ROWS can be used interchangeably.

Remarks

Although sorting by the primary key fields is not required, it is recommended to obtain consistent results when paginating the rows of a query.

In Virtual DataPort, FETCH and OFFSET can only be used when querying a view. Creating a derived view with FETCH and/or OFFSET is not valid.

SQL-92 does not define these clauses, but they are defined in the SQL-2011 standard. Many database management systems have provided similar clauses to these (TOP, LIMIT, ROWNUM…). However, unlike FETCH and OFFSET, these clauses are not standard.