Type Conversion Functions

Type conversion functions transform the type of a value.

Type functions supported by Virtual DataPort

ARRAY_TO_STRING

Description

The ARRAY_TO_STRING function converts an array field to a string that contains the elements of the array separated by a character.

This function has two signatures. With the first one, the array is surrounded by braces (“{“ and “}”) and if the array contains other arrays they will also be surrounded by braces. If the array contains registers, they will be surrounded by parentheses (“(” and “)”).

With the second signature, the user can indicate the characters that surround the array and its inner registers and arrays.

Syntax

ARRAY_TO_STRING( <separator:text>, <array value:array> ):text

ARRAY_TO_STRING( <separator:text>, <array begin delimiter:text>,
    <array end delimiter:text>, <register begin delimiter:text>,
    <register end delimiter:text>, <array value:array> ):text
  • separator. Character that separates the elements of the array.
  • array begin delimiter. Character placed before the array and its inner arrays.
  • array end delimiter. Character placed after the array and its inner arrays.
  • register begin delimiter. Character placed before the inner register fields.
  • register end delimiter. Character placed after the inner register fields.

Examples

Consider the view V with two fields: name and info. info is an array of registers whose subfields are message and register_sample. register_sample is a field of type register with two subfields: key and value.

name info
message register_sample
key value
Virtual DataPort Virtual Data Access Layer 1 one
Data Federation 2 two
ITPilot Web Integration 3 three
Web Automation 4 four
Aracne Crawling 5 five
Quering non-structured data 6 six
Scheduler Job Scheduling 7 seven

Example 1

SELECT name, ARRAY_TO_STRING(' - ', info)
FROM V
name array_to_string
Virtual DataPort {Virtual Data Access Layer,(1,one) - Data Federation,(2,two)}
ITPilot {Web Integration,(3,three) - Web Automation,(4,four)}
Aracne {Crawling,(5,five) - Quering non-structured data,(6,six)}
Scheduler {Job Scheduling,(7,seven)}

Example 2

SELECT name, ARRAY_TO_STRING(', ', ' [ ', ' ] ', ' |- ', ' -|', info)
FROM V
name array_to_string
Virtual DataPort [ Virtual Data Access Layer, |- 1, one -|, Data Federation, |- 2, two -| ]
ITPilot [ Web Integration, |- 3, three -|, Web Automation, |- 4, four -| ]
Aracne [ Crawling, |- 5, five -|, Quering non-structured data, |- 6, six -| ]
Scheduler [ Job Scheduling, |- 7, seven -| ]

CAST

Description

The CAST function converts data from one data type to another.

Syntax 1

CAST( <vdp data type:text>, <value:expression> )
  • vdp data type. Required. Data type you want the value to be converted to. This value is the name of a Virtual DataPort type.
  • value. Required. The value to convert.

The following table shows the possible type conversions. The column Output type contains the possible values of the parameter vdp data type.

Type conversions permitted with the CAST function
Input Value Type (type of the parameter value) Output Type
array array
boolean int
text, blob blob
text, int, long, float, double, boolean boolean
text, date, long date
text, int, long, float, double double
text, int, long, float, double float
text, int, long, float, double decimal
text, int, long, float, double int
text, int, long, float, double long
xml, register register
text, int, long, float, double, boolean, date, xml, blob, register, array text
text, blob, xml, register, array xml

Syntax 2

CAST( <value:expression> AS <SQL type:text> )
  • value. Required. The value to convert.
  • SQL type. Required. Name of an ANSI SQL type you want the value to be converted to.
Type conversion from ANSI SQL types and Virtual DataPort types
SQL Type Virtual DataPort Type
BIT (n) blob
BIT VARYING (n) blob
BOOL boolean
BYTEA blob
CHAR (n) text
CHARACTER (n) text
CHARACTER VARYING (n) text
DATE date
DECIMAL double
DECIMAL (n) double
DECIMAL (n, m) double
DOUBLE PRECISION double
FLOAT float
FLOAT4 float
FLOAT8 double
INT2 int
INT4 int
INT8 long
INTEGER int
NCHAR (n) text
NUMERIC double
NUMERIC (n) double
NUMERIC (n, m) double
NVARCHAR (n) text
REAL float
SMALLINT int
TEXT text
TIMESTAMP date
TIMESTAMP WITH TIME ZONE date
TIMESTAMPTZ date
TIMETZ date
VARBIT blob
VARCHAR text
VARCHAR ( MAX ) text
VARCHAR (n) text

Remarks

Remark 1

The function CAST truncates the output when converting a value to a text, when these two conditions are met:

  1. You specify a SQL type with length for the target data type. E.g. VARCHAR(20).
  2. And, this length is lower than the length of the input value.

For example, CAST ('Denodo' AS VARCHAR(2)) returns “De” because the target type specifies a length lower than the length of the input value.

Remark 2

When casting a boolean to an integer, true is mapped to 1 and false to 0.

Examples

Example 1

SELECT CAST('blob', 'hello') AS text_to_blob_cast
    , CAST('boolean', 'true') AS text_to_boolean_cast
    , CAST('boolean', 500000) AS long_to_boolean_cast
    , CAST('boolean', 0) AS long_to_boolean_cast_Zero
    , CAST('double', 5 + 5) AS int_to_double_cast
FROM Dual();
text_to_ blob_cast text_to_ boolean_cast long_to_ boolean_cast long_to_ boolean_cast int_to_ double_cast
[BINARY DATA] - 5 bytes true true false 10.0

Example 2

Consider the view V with a column register_sample of type register. This register has a field STR of type array.

register_sample
str r1 r2
hello | world 3 3.70
SELECT CAST('xml', register_sample)
FROM V
<?xml version="1.0" encoding="UTF-8"?>
<register>
    <R1>9</R1>
    <R2>1.1</R2>
    <STR>another string</STR>
    <STR>last string here</STR>
</register>

Example 3

Consider the view V with a column array_sample of type array. The array array_sample has another array into it.

array_sample
str r1 r2
denodo platform 40 52.0
enterprise data | virtualization 60 72.0
SELECT CAST('xml', array_sample)
FROM V
<?xml version="1.0" encoding="UTF-8"?>
<array>
    <item>
        <R1>40</R1>
        <R2>52.0</R2>
        <STR>denodo</STR>
        <STR>platform</STR>
    </item>
    <item>
        <R1>60</R1>
        <R2>72.0</R2>
        <STR>enterprise</STR>
        <STR>data</STR>
        <STR>virtualization</STR>
    </item>
</array>

Example 4

SELECT
CAST('hello' AS BIT VARYING(20)) AS text_to_blob_cast
, CAST(5+5 AS VARCHAR(1)) AS int_to_text_cast
, CAST('10' AS numeric) AS text_to_int_cast
FROM Dual();
text_to_blob_ cast int_to_text_ cast text_to_int_ cast
[BINARY DATA] - 5 bytes 1 10.0

Note that the value of the second column is truncated from “10” (5+5) to “1”. The reason is that the SQL type indicated in the CAST function (VARCHAR(1)) has a maximum length of 1. If it was CAST(5+5 AS VARCHAR(2)), the value of the second column would be “10”.

CREATETYPEFROMXML

Description

The CREATETYPEFROMXML function creates a register or an array type from XML data. If the type is created correctly, it returns the name of the new type.

This function is usually used along with CAST`. The section Converting XML Data into Virtual DataPort Compound Types explains how to do it.

Note

This function is deprecated and may be removed in future versions of the Denodo Platform. Instead, create an XML data source with a route of type from variable and pass the XML document to this data source.

Syntax

CREATETYPEFROMXML( <new type name:text>, <xml value:{xml\|text}> ):text
  • new type name. Required. Name of the new type.
  • xml value. Required. Sample XML used as a template to create the new type.

Examples

Example 1

Creating a new register type:

SELECT CREATETYPEFROMXML('bookstore_xml_type',
'<bookstore>
    <book category="COOKING">
        <title lang="en">Everyday Italian</title>
        <author>Giada De Laurentiis</author>
        <year>2005</year>
        <price>30.00</price>
    </book>
    <book category="CHILDREN">
        <title lang="en">Harry Potter</title>
        <author>J K. Rowling</author>
        <year>2005</year>
        <price>29.99</price>
    </book>
</bookstore>') FROM Dual();

Example 2

Creating a new array type:

SELECT CREATETYPEFROMXML('title_type',
'<titles>
    <title lang="en">XQuery Kick Start</title>
    <title lang="en">Learning XML</title>
</titles>') FROM Dual();

REGISTER

Description

The REGISTER function creates a register with the values of the fields of a view.

Syntax

REGISTER( <field name:any type> [, <field name:any type> ]*):register
  • field name. The name of a field.

Example

Consider the view V:

int_sample text_sample register_sample
1 A Register { hello , how’re you }
1 B Register { hello, good bye }
2 C Register { another string, last string }
SELECT REGISTER(int_sample, text_sample, register_sample) AS register_sample
FROM V;
register_sample
Register { 1, A, Register { hello , how’re you } }
Register { 1, B, Register { hello, good bye } }
Register { 2, C, Register { another string, last string } }

TO_DATE

Description

See appendix SUBTRACT.