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.

Example 1

SELECT name, ARRAY_TO_STRING(' - ', info)
FROM V

Example 2

SELECT name, ARRAY_TO_STRING(', ', ' [ ', ' ] ', ' |- ', ' -|', info)
FROM V

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.

Casting a boolean value to an integer returns 1 for true, 0 for false.

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.

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();

Example 2

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

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.

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();

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.

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:

SELECT REGISTER(int_sample, text_sample, register_sample) AS register_sample
FROM V;