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 (deprecated), localdate, timestamp, timestamptz, time, long

date (deprecated)

text, date (deprecated), localdate, timestamp, timestamptz, long

localdate

text, date (deprecated), localdate, timestamp, timestamptz, time, long

timestamp

text, date (deprecated), localdate, timestamp, timestamptz, time, long

timestamptz

text, date (deprecated), timestamp, timestamptz, time, long

time

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 (deprecated), localdate, timestamp, timestamptz, time , 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

localdate

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

timestamp

TIMESTAMP WITH TIME ZONE

timestamptz

TIMESTAMPTZ

timestamptz

TIME

time

TIMETZ

time

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. The type of the value can be xml or text.

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 } }