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

IS_PROJECTED_FIELD¶

Description

The IS_PROJECTED_FIELD function returns true if the field passed as parameter is projected (i.e. the field is in the SELECT statement of the query). False otherwise.

Syntax

IS_PROJECTED_FIELD( <field name:literal> ):boolean

• field name. Required. Name of the field with the exact case of the field. The function returns false if this parameter is null.

Examples

Consider the following view items:

Example 1

SELECT item, IS_PROJECTED_FIELD('item')
FROM items

Example 2

SELECT sum(price) AS total, IS_PROJECTED_FIELD('price')
FROM items

In this example, IS_PROJECTED_FIELD returns true because the field price is projected, even though the query applies a function over this field.

Example 3

SELECT item, IS_PROJECTED_FIELD('price')
FROM items

In this example, IS_PROJECTED_FIELD returns false because the query does not project the field price.

Example 4

SELECT item AS field1, IS_PROJECTED_FIELD('field1')
FROM items

In this example, IS_PROJECTED_FIELD returns false because this functions checks if a field of the view is projected, not the alias of the fields.

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

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.