COALESCE¶

Description

The COALESCE function returns the first non-null argument. COALESCE is equivalent to the expression:

CASE WHEN arg1 IS NOT NULL THEN arg1
     WHEN arg2 IS NOT NULL THEN arg2
     …
END

Syntax

COALESCE( <field name:identifier>, <field name:identifier> [, <field name:identifier> ]*)

• field name. Text or field name which can be NULL.

Examples

Consider the view called V:

Example 1

SELECT COALESCE(b, 'hello')
FROM V

Example 2

SELECT coalesce('hello', 'bye')
FROM v

Example 3

SELECT COALESCE(b, a)
FROM V

CONTEXTUALSUMMARY¶

Description

The CONTEXTUALSUMMARY function returns relevant text fragments of a text, containing the word or sentence specified.

Syntax

CONTEXTUALSUMMARY( <content:text>, <keyword:text>, [ <begin delim:text>,
    <end delim:text>, <fragment separator:text>, <fragment length:int>
    [, <max fragments number:int> [, <analyzer:text> ] ] ] )

• content: Required. Text that the most relevant fragments are to be extracted from.

• keyword: Required. Keyword used to extract the text fragments. The content of this argument can be a single word or a sentence.

• begin delim: Optional. Text to add as prefix of the keyword whenever it appears in the text. Default value is “”.

• end delim: Optional. Text to add as suffix of the keyword whenever it appears in the text. Default value is “”.

• fragment separator: Optional. Text to separate each text fragment of the result. Default value is “…”.

• fragment length: Optional. Approximate number of characters that will appear before and after the keyword occurrences inside of the text. Default value is 5.

• max fragment number: Optional. Maximum number of fragments to retrieve.

• analyzer: Optional. Analyzer used to search for keywords. By default, the Standard Analyzer (std) is used. This analyzer does not consider lemmatization or stopwords. Virtual DataPort also includes analyzers for English (en) and Spanish (es).

Examples

Example 1

SELECT CONTEXTUALSUMMARY(content, 'Denodo', '<b>', '</b>', ' … ', 5, 1)
FROM demo_arn_view;

This query will return fragments of text content where the “Denodo” word appears.

Example 2

Consider the following view text_summary_sample:

SELECT contextualsummary(text_sample, 'system')
FROM text_summary_sample

Example 3

SELECT contextualsummary(text_sample, 'service')
FROM text_summary_sample

Example 4

SELECT contextualsummary(text_sample, 'web', '\', '/', '--', 25)
FROM text_summary_sample

GETVAR¶

The GETVAR function obtains the value of a variable from the context of the query that uses this function. If the variable does not exist, it returns a default value.

The page Adding Variables to Selection Conditions (GETVAR and SETVAR) explains how to use this function.

Syntax

GETVAR('<name of the variable:literal>', '<type of the variable:literal>', '<default value:literal>')

GETSESSION¶

Description

The GETSESSION function provides information about the session established with a Virtual DataPort server.

Syntax

GETSESSION( <parameter : literal> )

• parameter can be one of the following (the value returned by the function depends on this parameter):

  1. user: the function returns the user name of the current user.

  2. useragent: the function returns the user agent associated to the current user’s connection.

  3. database: the function returns the name of the Virtual DataPort database that the client is connected to.

  4. i18n: the function returns the name of the i18N (internationalization) configuration of the database that the client is connected to.

  5. roles: the function returns an array with the names of the effective roles assigned to the current user. The effective roles are the roles assigned directly and indirectly to the user. For example, if “user1” has the role “A” assigned and the role “A” has the role “B” assigned, this function returns “A” and “B”.

Example

SELECT GETSESSION('user') || '@' || GETSESSION('database') as user_name
FROM Dual();

HASH¶

Description

The HASH function calculates the digest of the input value, with the algorithm MD5 and returns it encoded in base64 (not in base 16).

For the same input, this function always returns the same value.

It returns null when the input parameter is null.

Syntax

HASH( <value:text> ):text

• value: Required. The name of a field or a literal.

Example

SELECT hash('text value') as hash_value, hash(null) as null_value
FROM Dual()

MAP¶

Description

The MAP function returns the value associated with a key. The pair key-value can be obtained from a view or from a map (see section Defining a Map). When the key does not exist, the function returns NULL.

There are two possible signatures:

Syntax 1

MAP ( <key:text>, <view name:text>, <key field:text>,
    <value field:text> )

It obtains the value associated with a key. MAP searches the value of a key in the columns of a view.

• key. Required. The value to search in the view.

• view name. Required. The name of the view that contains the key and its value.

• key field. Required. The column of the view that contains the keys.

• value field. Required. The column of the view that contains the values.

Syntax 2

MAP ( <key:text>, <map name:text> [, <i18n:text> ] )

It obtains the value associated with a key from a Map.

• key. Required. The value to search in the map.

• map name. Required. The name of the map that contains the key and its value.

• i18n. Optional. Internationalization configuration of the contents.

Examples

Example 1

Consider the map food:

CREATE MAP SIMPLE food (
    'breakfast' = 'milk'
    'dinner' = 'lettuce'
    'lunch' = 'meat'
)

SELECT MAP('breakfast', 'food') AS breakfast
    , MAP('lunch', 'food') AS lunch
    , MAP('dinner', 'food') AS dinner
    , MAP('none', 'food') AS none
FROM Dual()

Example 2

Consider the view FOREIGN_SALES that contains the revenue of a company in each country, in the country’s currency.

And the CURRENCY_RATES_TO_USD map that contains the exchange rate of each currency to dollar.

CREATE MAP SIMPLE currency_rates_to_usd (
    'CAD' = '0.957121'
    'EUR' = '1.4971'
    'GBP' = '1.67'
    'JPY' = '0.011166'
    'MXN' = '0.076989'
    'USD' = '1.0'
);

SELECT month
    , country
    , CAST('float', MAP(CURRENCY, 'currency_rates_to_usd')) * revenue AS revenue_usd
FROM foreign_sales

NULLIF¶

Description

The NULLIF function compares two values or expressions and returns NULL if they are equal. Otherwise it returns the first value.

This function is equivalent to the statement:

CASE WHEN <expression> = <expression>
    THEN NULL
    ELSE <expression>
END

NULLIF performs implicit type conversion. That is, if the two parameters have different type, it will try to cast one of them in order

to make the comparison.

I.e.: if the first parameter is 1 (text) and the second is 1 (integer), it will convert the text parameter to an integer and they will be considered equal even if their type is different.

Syntax

NULLIF(<expression>, <expression>)

Examples

Consider the view internet_inc:

Example 1

SELECT NULLIF(id, 1) AS display
FROM internet_inc

Example 2

SELECT *
FROM internet_inc
WHERE NULLIF(ID, 1) <> NULL

The first row of the view does not match the condition.

Example 3

SELECT COALESCE(NULLIF(ID, '1'), summary) AS display
FROM internet_inc

PIVOTREGISTER¶

Description

The PIVOTREGISTER function transforms the provided array values to columns with the provided names.

There are two possible signatures:

Syntax 1

PIVOTREGISTER ( <values:array>, <column names:array>)

It transforms the provided array values to columns. The result columns always be of type text.

• values. Required. The values to transform to columns.

• column names. Required. The names of the new columns.

Syntax 2

PIVOTREGISTER ( <values:array>, <column names:text>)

It transforms the provided array values to columns.

• values. Required. The values to transform to columns.

• column names. Required. The names of the new columns. It is a text containing the column names and, optionally, the types separated by commas. The syntax must be: field1:type1 [,field2:type2, …]. Type should be one of the following: string, double, float, integer, long, boolean, date, time, timestamp and timestamptz.

Example

Consider the table phone_array_table:

Array Example:

We want to transform the two phone_numbers rows into two columns: home_phone_number and office_phone_number. To do this, we apply the PIVOTREGISTER function over the phone_numbers column, so it returns a record with the properties home_phone_number and office_phone_number and the values of the phone_numbers column:

CREATE OR REPLACE VIEW phone_table_aux AS
    SELECT name,
           PIVOTREGISTER(phone_numbers, { ROW ('home_phone_number'), ROW ('office_phone_number') })
    FROM phone_array_table;

or

CREATE OR REPLACE VIEW phone_table_aux AS
    SELECT name,
           pivotregister(phone_numbers, 'home_phone_number:string,office_phone_number:string')
    FROM phone_array_table;

Finally, we can created a derived view over phone_table_aux and project the fields of the PIVOTREGISTER record of the view, and we will get the pivot result:

This function can be used to transform JSONs data sources, created for example from Google Sheets or Google Analytics sources, in tables. In this case, it may be possible that the first element of the JSON was the header of the table. If we want to specify the column types, the first tuple might be null, since the header has text values.

Example:

We want to transform this JSON in a table with six columns:

• Date of type timestamptz.

• C1 of type boolean.

• C2 of type double.

• C3 of type time.

• C4 of type integer.

• C5 of type integer.

{
   "range": "'Sheet 1'!A1:E36",
   "majorDimension": "ROWS",
   "values": [
            [
         "Date",
         "c1",
         "c2",
         "c3",
         "c4",
         "c5"
      ],
      [
         "2020-10-10T01:59:59+01:00",
         "true",
         "43.58",
         "21:15:45",
         "421",
         "23"
      ],
      [
         "2020-10-11T01:59:59+01:00",
         "true",
         "43.79",
         "21:15:45"
      ],
      [
         "2020-10-12T01:59:59+01:00",
         "false",
         "44.74",
         "21:15:45"
      ],
      [
         "2020-10-13T01:59:59+01:00",
         "true",
         "45.10",
         "21:15:45"
      ],
      [
         "2020-10-14T01:59:59+01:00",
         "false",
         "46.01",
         "21:15:45",
         "486",
         "23"
      ]
   ]
}

After creating the data source using this JSON we create the base view bv_pivot_json. Then, we need to create the derived view dv_pivot_json to flatten the values array.

In order to transform the array values in columns with the wanted types, we are going to apply the PIVOTREGISTER function:

CREATE OR REPLACE view pivot_json_aux AS
   SELECT pivotregister(values, 'Date:timestampz, C1:boolean, C2:double, C3:time,
   C4:long, C5:integer')
FROM dv_pivot_json

To avoid having a null row, we can number the rows of the table. To do this, we have to add a new column, rownum, to the view in which the record subfields are projected. This new column is a call to the rownum() function:

Now, we just have to create a derived view which removes the first row. To do this, we need to add the following where condition:

rownum <> 1

Remember to remove the rownum field in the Output tab of the derived view and we will have the result view:

ROWNUM¶

Description

The ROWNUM function returns a unique number for each row of the result of a query.

This number is unique for each query even if the data were retrieved from different sources.

The ORDER BY clause is processed after ROWNUM assigns a value to each row.

For consistent results between several executions of a query, use ROWNUM() over a view with the ORDER BY clause.

Syntax

ROWNUM( [ <offset:long > ] ):long

• offset. Optional. If present, the first value returned by ROWNUM is (offset + 1), instead of 1.

Examples

Consider the view internet_inc:

Example 1

SELECT rownum(10), summary, taxid
FROM internet_inc

Example 2

SELECT ROWNUM(), summary, taxid
FROM internet_inc
ORDER BY summary

Note that the query of “Example 2” sorts the results by the field summary. As the ORDER BY clause is processed after the ROWNUM function assigns a value to each row, the first value of ROWNUM is not 1.