Other Functions

Other functions supported by Virtual DataPort

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:

a b c
10.10 I am some text 21-ene-2005 0h 0m 0s
-80.10 Text is $% needed always 12-mar-2005 12h 30m 0s
20.50 Text for a living 01-feb-2006 16h 45m 0s
40.05 NULL NULL

Example 1

SELECT COALESCE(b, 'hello')
FROM V
coalesce
I am some text
Text is $% needed always
Text for a living
hello

Example 2

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

Example 3

SELECT COALESCE(b, a)
FROM V
coalesce
I am some text
Text is $% needed always
Text for a living
40.05

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:

text_sample
A web service (also webservice) is defined by the W3C as a software system designed to support interoperable machine-to-machine interaction over a network. It has an interface described in a machine-processable format (specifically Web Services Description Language WSDL). Other systems interact with the web service in a manner
prescribed by its description using SOAP messages, typically conveyed using HTTP with an XML serialization in conjunction with other web-related standards.Web services are frequently just Internet Application Programming Interfaces (API) that can be accessed over a network, such as the Internet, and executed on a remote system hosting the requested services. Other approaches with nearly the same functionality
as web services are Object Management Group’s (OMG) Common Object Request
Broker Architecture (CORBA), Microsoft’s Distributed Component Object Model
(DCOM) or Sun Microsystems’s Java/Remote Method Invocation (RMI).
SELECT contextualsummary(text_sample, 'system')
FROM text_summary_sample
contextualsummary
system
system
 
 
 

Example 3

SELECT contextualsummary(text_sample, 'service')
FROM text_summary_sample
contextualsummary
service … service
 
 
 
 

Example 4

SELECT contextualsummary(text_sample, 'web', '\', '/', '--', 25)
FROM text_summary_sample
contextualsummary
A web/ service (also– (specifically Web/ Services– with the web/ service
with other web/-related
as web/ services
 
 

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: user, database, i18n or roles The value returned by the function depends on this parameter:
    • user: the function returns the user name of the current user.
    • database: the function returns the name of the Virtual DataPort database that the client is connected to.
    • i18n: the function returns the name of the i18N (internationalization) configuration of the database that the client is connected to.
    • 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();
user_name
user1@denodo_samples_db

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()
hash_value null_value
eNV9lLZhJ1lex1ztvwnajg== NULL

IS_PROJECTED_FIELD

Description

The IS_PROJECTED_FIELD function returns true if the field passed as parameter is projected in the view. False otherwise.

Syntax

IS_PROJECTED_FIELD( <fieldName:literal> ):boolean
  • fieldName. Required. The name of the field with the exact case of the field.

Examples

Consider the following view items:

item price
A 3.45
B 9.99
C 4.99

Example 1

SELECT item, IS_PROJECTED_FIELD('item')
FROM items
item is_projected_field
A true
B true
C true

Example 2

SELECT IS_PROJECTED_FIELD('item')
FROM items
is_projected_field
false
false
false

In the second example, IS_PROJECTED_FIELD returns false because the parameter is the value of the cell ITEM of each row. While in the first example it returns true because the parameter is a literal with the name of the field.

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.

Note

In both cases, key is a case-insensitive parameter.

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()
breakfast lunch dinner none
milk meat lettuce NULL

Example 2

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

country month revenue currency
Mexico JAN 7536.00 MXN
Spain JAN 20000.00 EUR
United Kingdom JAN 26816.00 GBP
Canada FEB -25616.00 CAD
Japan FEB 100024.00 JPY

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
month country revenue_usd
JAN Mexico 580.19
JAN Spain 29942.00
JAN United Kingdom 44782.72
FEB Canada -24517.61
FEB Japan 1116.87

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:

id summary ttime taxid
1 Error in ADSL router 2005-06-29 19:19:41.0 B78596011
2 Incident in ADSL router 2005-06-29 19:19:41.0 B78596012
3 Install additional line 2005-06-29 19:19:41.0 B78596013
4 Bandwidth increase 2005-06-29 19:19:41.0 B78596014

Example 1

SELECT NULLIF(id, 1) AS display
FROM internet_inc
display
NULL
2
3
4

Example 2

SELECT *
FROM internet_inc
WHERE NULLIF(ID, 1) <> NULL
id summary ttime taxid
2 Incident in ADSL router 2005-06-29 19:19:41.0 B78596012
3 Install additional line 2005-06-29 19:19:41.0 B78596013
4 Bandwidth increase 2005-06-29 19:19:41.0 B78596014

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

Example 3

SELECT COALESCE(NULLIF(ID, '1'), summary) AS display
FROM internet_inc
display
Error in ADSL router
2
3
4

Note

NULLIF has automatically converted the second parameter to an integer to compare it with the values of the column ID which are also integers.

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:

id summary ttime taxid
1 Error in ADSL router 2005-06-29 19:19:41.0 B78596011
2 Incident in ADSL router 2005-06-29 19:19:41.0 B78596012
3 Install additional line 2005-06-29 19:19:41.0 B78596013
4 Bandwidth increase 2005-06-29 19:19:41.0 B78596014

Example 1

SELECT rownum(10), summary, taxid
FROM internet_inc
rownum summary taxid
11 Error in ADSL router B78596011
12 Incident in ADSL router B78596012
13 Install additional line B78596013
14 Bandwidth increase B78596014

Example 2

SELECT ROWNUM(), summary, taxid
FROM internet_inc
ORDER BY summary
rownum summary taxid
4 Bandwidth increase B78596014
1 Error in ADSL router B78596011
2 Incident in ADSL router B78596012
3 Install additional line B78596013

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.