Oracle Function

Denodo Function

ABS(n)

ABS( <value:numeric> ):numeric

ACOS(n)

ACOS( <value:numeric> ):double

ASIN(n)

ASIN( <value:numeric> ):double

ATAN(n)

ATAN( <value:numeric> ):double

ATAN2(n,m)

ATAN2(<x:numeric>, <y:numeric>):double

CEIL(n)

CEIL( <value:decimal> ):long

CEIL( <value:int> ):int

CEIL( <value:long> ):long

COS(n)

COS( <angle:numeric> ):double

cot(n)

COT( <angle:numeric> ):double

degrees(n)

DEGREES( <angle:numeric> ):double

To perform division in Oracle SQL, use the forward slash (/) operator.

DIV( <dividend:numeric>, <divisor:numeric> ):numeric

exp(n)

EXP( <value:numeric> ):double

floor(n)

FLOOR( <value:decimal> ):long

FLOOR( <value:int> ):int

FLOOR( <value:long> ):long

LN(n)

LN( <value:numeric> ):double

LOG(m, n)

where m is the base and n is the number

LOG( <value:numeric> [, <base:numeric> ]):double

MAX(value1,value2,value3) or MAX(column name)

MAX( <value 1:numeric>, <value 2:numeric> [, <value N:numeric> ]* ):numeric

MIN(value1,value2,value3) or MIN(column name)

MIN( <value 1:numeric> [, <value N:numeric> ]* ):numeric

MOD(m, n)

where m is the dividend and n is a divisor

MOD( <dividend:decimal>, <divisor:decimal> ):decimal

MOD( <dividend:double>, <divisor:double> ):double

MOD( <dividend:float>, <divisor:float> ):double

MOD( <dividend:int>, <divisor:int> ):int

MOD( <dividend:long>, <divisor:int> ):int

MOD( <dividend:long>, <divisor:long> ):long

MULTIPLY {src_num_lit|_var|_col} TIMES dst_num_var

[ROUND=nn]

src_num_lit|_var|_col

Numeric source column, variable, or literal.

dst_num_var

Destination numeric variable.

ROUND

Rounds the result to the specified number of digits to the right of the decimal point.

Description

MULTIPLY multiplies the first field by the second and places the result into the second field.

MULT ( <value 1:numeric>, <value 2:numeric> [, <value N:numeric> ]* ):numeric

PI (  )

PI():double

POWER(m, n)

where m is base and n is exponent

POWER( <base:numeric>, <exponent:double> ):double

RADIANS(angle_in_degrees)

RADIANS( <angle:numeric> ):double

DBMS_RANDOM.NORMAL

  RETURN NUMBER;

Note: This function is deprecated with Release 11gR1 and, although currently supported, it should not be used.

RAND()

ROUND(n [, integer ])

returns n rounded to integer places to the right of the decimal point.

ROUND( <value:numeric [, n : integer ] ):numeric

SIGN(n)

SIGN( <value:numeric> ):int

SIN(n)

SIN( <angle:numeric> ):double

SQRT(n)

SQRT( <value:numeric> ):double

SUBTRACT {src_num_lit|_var|_col} FROM dst_num_var[ROUND=nn]

src_num_lit|_var|_col

src_num_lit|_var|_col

Subtracted from the contents of dst_num_var.

dst_num_var

The result after execution.

ROUND

Rounds the result to the specified number of digits to the right of the decimal point. For float variables this value can be from 0 to 15. For decimal variables, this value can be from 0 to the precision of the variable. For integer variables, this argument is not appropriate.

Description

Subtracts the first value from the second and moves the result into the second field.

SUBTRACT( <value 1:numeric>, <value 2:numeric> ):numeric

SUBTRACT( <value 1:localdate>, <value 2:localdate> ):long

SUBTRACT( <value 1:timestamp>, <value 2:timestamp> ):long

SUBTRACT( <value 1:timestamptz>, <value 2:timestamptz> ):long

SUBTRACT( <value 1:time>, <value 2:time> ):long

SUBTRACT( <value 1:date>, <value 2:date> ):long

SUM(value1,value2,value n) or SUM(expr)

SUM( <value1:numeric, value2:numeric [, valueN:numeric ]* ):numeric

TAN(n)

TAN ( <angle:numeric> ):double

TRUNC(n [, m ])

n truncated to m decimal places. If m is omitted, then n is truncated to 0 places

TRUNC( <value:numeric> ):long

Oracle Function

Denodo Function

ASCII(char)

ASCII( <value:text> ):int

NCHR(n)

CHAR( <code:int> ):text

CONCAT(char1, char2)

CONCAT( <value 1:text>, <value 2:text> [, <value N:text> ]* ):text

INSTR (string , substring , position , occurrence)

INSTR( <str1:text>, <str2:text> ):int

LENGTH(char)

LEN( <value:text> ):int

SUBSTR(string, 1, number_of_characters)

LEFT( <value:text>,int )

LOWER(char)

LOWER( <value:text> ):text

LTRIM(char [, set ])

removes from the left end of char all of the characters contained in set

LTRIM( <value:text> ):text

GREATEST(expr, expr1)

Returns the greatest (maximum) value in the list of expressions.

MAX( <value 1:text>, <value 2:text> [, <value N:text> ]* ):text

LEAST(expr,expr1)

Returns the least(minimum) value in the list of expressions.

MIN( <value 1:text>, <value 2:text> [, <value N:text> ]*): text

This could be achieved by,

INSTR(string, substring, start_position, occurrence)

POSITION( <value 1:text> IN <value 2:text> ) : int

REGEXP_REPLACE(source_string, pattern, replace_string, position

, occurrence, match_parameter)

source_string is a character expression that serves as the search value

pattern is the regular expression.

replace_string can be of any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

position is a positive integer indicating the character of source_string where Oracle should begin the search.

occurrence is a nonnegative integer indicating the occurrence of the replace operation.

match_parameter is a text literal that lets you change the default matching behavior of the function.

REGEXP_SUBSTR(source_string, pattern, position

, occurrence, match_parameter)

REGEXP( <original text:text>, <regex:text>, <replacement:text> ):text

Original text. Required. Input string.

regex. Regular expression to which original text is matched.

replacement. Each match of regular expression will be replaced by this. This value is also a regular expression so you can specify capturing groups.

REGEXP_COUNT(source_string, pattern, position, match_parameter)

REGEXP_COUNT(String originalText, String regex): int

Using REPLACE with RPAD (for repeating strings)

For example,        SELECT REPLACE(RPAD('+', 10, '+'), '+', 'Hello') FROM DUAL;

   -- Output: HelloHelloHelloHelloHelloHelloHelloHelloHelloHello

REPEAT ( <value:text>, <count:int> ):text

REPLACE(char, search_string, replacement_string)

Returns a string where every occurrence of search_string in char is replaced by replacement_string.If replacement_string is not provided, it simply removes search_string from the char.

REPLACE( <value:text>, <from:text>, <to:text> ):text

SUBSTR(string, start_position, length)

string: The input string.

start_position: A negative number indicates counting from the end of the string. For example, -1 is the last character, -2 is the second-to-last character, and so on.

length: The number of characters to extract.

Extracts a specified number of characters from the end (right-hand side) of a text string using the SUBSTR function with a negative starting position.

RIGHT( <value:text>,int )

RTRIM(char, set)

Removes from the right end of char all of the characters that appear in set.

RTRIM( <value:text> ):text

UTL_MATCH.EDIT_DISTANCE (

   s1  IN  VARCHAR2,

   s2  IN  VARCHAR2)

SIMILARITY( <value 1:text>, <value 2:text> [ , <algorithm:text> ]):double

SPLIT(Dimension, "delimiter", component number)

Where you select the character delimiter to a separate string, and the component of the string to return.

SPLIT( <regexp:text>, <value:text> ):array

SUBSTR(string, position, substring_length)

SUBSTRING( <value:text>, <start index:int> [, <end index:int> ]):text

TRIM([trim_character] FROM trim_source)

TRIM([LEADING | TRAILING | BOTH] [trim_character] FROM trim_source)

trim_source: The string to trim.

trim_character: The character(s) to remove (default is space).

LEADING: Remove characters from the beginning of the string.

TRAILING: Remove characters from the end of the string.

BOTH: Remove characters from both the beginning and end of the string. 

TRIM ( <value:text> )

UPPER(char)

UPPER( <value:text> ):text

Oracle Function

Denodo Function

addDays(startDate, numberOfDays)

ADDDAY( <value:intervaldaysecond>, <increment> ):intervaldaysecond

ADDDAY( <value:localdate>, <increment> ):localdate

ADDDAY( <value:timestamp>, <increment> ):timestamp

ADDDAY( <value:timestamptz>, <increment> ):timestamptz

SYSDATE + (number_of_hours / 24)

ADDHOUR( <value:intervaldaysecond>, <increment> ):intervaldaysecond

ADDHOUR( <value:time>, <increment> ):time

ADDHOUR( <value:timestamp>, <increment> ):timestamp

ADDHOUR( <value:timestamptz>, <increment> ):timestamptz

addTimeInMinutes(startTime, numberOfMinutes)

ADDMINUTE( <value:intervaldaysecond>, <increment> ):intervaldaysecond

ADDMINUTE( <value:time>, <increment> ):time

ADDMINUTE( <value:timestamp>, <increment> ):timestamp

ADDMINUTE( <value:timestamptz>, <increment> ):timestamptz

ADD_MONTHS(date,integer)

ADDMONTH( <value:intervalyearmonth>, <increment> ):intervalyearmonth

ADDMONTH( <value:localdate>, <increment> ):localdate

ADDMONTH( <value:timestamp>, <increment> ):timestamp

ADDMONTH( <value:timestamptz>, <increment> ):timestamptz

SYSDATE + (1/24/60/60)

or

SYSDATE + .00001157407

ADDSECOND( <value:intervaldaysecond>, <increment> ):intervaldaysecond

ADDSECOND( <value:time>, <increment> ):time

ADDSECOND( <value:timestamp>, <increment> ):timestamp

ADDSECOND( <value:timestamptz>, <increment> ):timestamptz

No equivalent function.You could make use of this formula to addweek to a date value

in_dt + (trunc(num_weeks) * 7)

where in_dt is column name or date value

ADDWEEK( <value:intervaldaysecond>, <increment> ):intervaldaysecond

ADDWEEK( <value:localdate>, <increment> ):localdate

ADDWEEK( <value:timestamp>, <increment> ):timestamp

ADDWEEK( <value:timestamptz>, <increment> ):timestamptz

No equivalent function.

You could make use of this formula to add year to a date value

ADD_MONTHS(sysdate,12)

ADDYEAR(value,increment)

No equivalent function. You could make use of this formula to convert timezone,

from_tz(cast(columnname or date value as timestamp), 'timezone format')

<timestamp:timestamp> AT TIME ZONE <targetTimezone:text> : timestamptz

<timestamptz:timestamptz> AT TIME ZONE <targetTimezone:text> : timestamp

CURRENT_DATE

CURRENT_DATE() : localdate

CURRENT_DATE : localdate

CURRENT_TIMESTAMP [ (precision) ]

CURRENT_TIMESTAMP:timestamptz

EXTRACT( { { YEAR

               | MONTH

               | DAY

               | HOUR

               | MINUTE

               | SECOND

               }

             | { TIMEZONE_HOUR

               | TIMEZONE_MINUTE

               }

             | { TIMEZONE_REGION

               | TIMEZONE_ABBR

               }

             }

FROM { datetime_value_expression

                  | interval_value_expression

                  }

           )

Example: SELECT EXTRACT(YEAR FROM DATE '1998-03-07') FROM DUAL

EXTRACT ( <part of field> FROM <value> )

No equivalent function. You could make use of the formula

trunc(sysdate, 'mm')

FIRSTDAYOFMONTH( <value:localdate> ):localdate

FIRSTDAYOFMONTH( <value:timestamp> ):timestamp

FIRSTDAYOFMONTH( <value:timestamptz> ):timestamptz

No equivalent function. You could make use of the formula

TRUNC(sysdate, 'iw')

FIRSTDAYOFWEEK( <value:localdate> ):localdate

FIRSTDAYOFWEEK( <value:timestamp> ):timestamp

FIRSTDAYOFWEEK( <value:timestamptz> ):timestamptz

FormatDate(date, date_format_string)

FORMATDATE( <datetime pattern:text>, <datetime>, [ <i18n:text> ] ):text

No equivalent function. You could make use of the function

EXTRACT(DAY FROM DATEVALUE)

GETDAY( <value:intervaldaysecond> ):long

GETDAY( <value:localdate> ):long

GETDAY( <value:timestamp> ):long

GETDAY( <value:timestamptz> ):long

DAYOFWEEK(dateExpr)

GETDAYOFWEEK( <value:localdate> ):long

GETDAYOFWEEK( <value:timestamp> ):long

GETDAYOFWEEK( <value:timestamptz> ):long

DAYOFYEAR(dateExpr)

GETDAYOFYEAR( <value:localdate> ):long

GETDAYOFYEAR( <value:timestamp> ):long

GETDAYOFYEAR( <value:timestamptz> ):long

When you subtract one date from another in Oracle Database using (-) operator, the result is the number of days between them.

For example: to_date ( '31-MAY-2023 12:34:56', 'DD-MON-YYYY HH24:MI:SS' )

                 - date '2023-03-21'

GETDAYSBETWEEN( <value 1:localdate>, <value 2:localdate> ):long

GETDAYSBETWEEN( <value 1:timestamp>, <value 2:timestamp> ):long

GETDAYSBETWEEN( <value 1:timestamp>, <value 2:timestamptz> ):long

HOUR(timeExpr)

GETHOUR( <value:intervaldaysecond> ):long

GETHOUR( <value:time> ):long

GETHOUR( <value:timestamp> ):long

GETHOUR( <value:timestamptz> ):long

dateVar.getMilliseconds()

GETMILLISECOND( <value:intervaldaysecond> ):long

GETMILLISECOND( <value:time> ):long

GETMILLISECOND( <value:timestamp> ):long

GETMILLISECOND( <value:timestamptz> ):long

dateVar.getMinutes()

GETMINUTE( <value:intervaldaysecond> ):long

GETMINUTE( <value:time> ):long

GETMINUTE( <value:timestamp> ):long

GETMINUTE( <value:timestamptz> ):long

dateVar.getMonth()

GETMONTH( <value:intervalyearmonth> ):long

GETMONTH( <value:localdate> ):long

GETMONTH( <value:timestamp> ):long

GETMONTH( <value:timestamptz> ):long

MONTHS_BETWEEN(DATE1,DATE2)

GETMONTHSBETWEEN( <value 1:localdate>, <value 2:localdate> ):long

GETMONTHSBETWEEN( <value 1:timestamp>, <value 2:timestamp> ):long

GETMONTHSBETWEEN( <value 1:timestamptz>, <value 2:timestamptz> ):long

There is no equivalent function in Oracle.

You could cascade ADD_MONTHS and TRUNC to achieve this.

GETQUARTER( <value:localdate> ):long

GETQUARTER( <value:timestamp> ):long

GETQUARTER( <value:timestamptz> ):long

There is no equivalent function in Oracle.

You could use the EXTRACT function to achieve this.

GETSECOND( <value:intervaldaysecond> ):long

GETSECOND( <value:time> ):long

GETSECOND( <value:timestamp> ):long

GETSECOND( <value:timestamptz> ):long

getTimeInMillis()

GETTIMEINMILLIS( <value:localdate> ):long

GETTIMEINMILLIS( <value:timestamp> ):long

GETTIMEINMILLIS( <value:timestamptz> ):long

There is no equivalent function in Oracle. You could make use of and TO_CHAR and TO_DATE functions. For example: to_char(to_date('datevalue','YYYYMMDD'),'ww') from dual;

GETWEEK( <value:localdate> ):long

GETWEEK( <value:timestamp> ):long

GETWEEK( <value:timestamptz> ):long

getYear()

GETYEAR( <value:intervalyearmonth> ):long

GETYEAR( <value:localdate> ):long

GETYEAR( <value:timestamp> ):long

GETYEAR( <value:timestamptz> ):long

LAST_DAY(date)

LASTDAYOFMONTH( <value:localdate> ):localdate

LASTDAYOFMONTH( <value:timestamp> ):timestamp

LASTDAYOFMONTH( <value:timestamptz> ):timestamptz

There is no equivalent function in Oracle.

You could use TRUNC to achieve this.

LASTDAYOFWEEK( <value:localdate> ):localdate

LASTDAYOFWEEK( <value:timestamp> ):timestamp

LASTDAYOFWEEK( <value:timestamptz> ):timestamptz

CURRENT_DATE

LOCALTIME:date

CURRENT_TIMESTAMP(precision)

LOCALTIMESTAMP:timestamp

NEXT_DAY(date, char)

NEXTWEEKDAY( <value:localdate>, <week day:int> ):localdate

NEXTWEEKDAY( <value:timestamp>, <week day:int> ):timestamp

NEXTWEEKDAY( <value:timestamptz>, <week day:int> ):timestamptz

TO_DATE(char [, fmt [, 'nlsparam' ] ])

fmt is a datetime model format

char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype

TO_DATE( <date pattern:text>, <value:text> [, <i18n:text> ] [, <timestamp:boolean> ] ):date

TO_DATE(char [, fmt [, 'nlsparam' ] ])

fmt is a datetime model format

char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype

TO_LOCALDATE( <localdate pattern:text>, <value:text> [, <language:text> ] ):localdate

TO_CHAR (datetime)

TO_TIME( <time pattern:text>, <value:text> [, <language:text> ] ):time

TO_CHAR (datetime)

TO_TIMESTAMP( <timestamp pattern:text>, <value:text> [, <language:text> ] ):timestamp

TO_CHAR (datetime)

TO_TIMESTAMPTZ( <timestamptz pattern:text>, <value:text> [, <language:text> ] ):timestamptz

TRUNC(date)

TRUNC( <value:localdate> [, <pattern:text> ] ):localdate

TRUNC( <value:time> [, <pattern:text> ] ):time

TRUNC( <value:timestamp> [, <pattern:text> ] ):timestamp

TRUNC( <value:timestamptz> [, <pattern:text> ] ):timestamptz

Oracle Function

Denodo Function

XMLQUERY

 ( XQuery_string

   [ XML_passing_clause ]

   RETURNING CONTENT [NULL ON EMPTY]

 )

XQuery_string is a complete XQuery expression

XML_passing_clause is an expression returning an XMLType or an instance of a SQL scalar data type that is used as the context for evaluating the XQuery expression.

XMLQUERY( <XQuery expression:text>, <is XQuery file:boolean> ):xml

XMLQUERY( <XQuery expression:text>, <is XQuery file:boolean>, <xml value:xml> ):xml

XMLQUERY( <XQuery expression:text>, <is XQuery file:boolean>, <xml value:text> , <is XML file:boolean> ):xml

XQuery expression. XQuery expression used to query xml data.

is XQuery file. True, if the parameter “XQuery expression” is a path to a file containing an XQuery expression. False, if “XQuery expression” is a literal or is the name of a field that contains an expression.

xml value. The XML to manipulate.

is XML File. True, if the parameter “xml value” is a path to a file containing an XML document. If “is XML File” is false or is missing, “xml value” is a literal or the name of an XML field.

xpathw32 /f= xml file /e=starting node /x= search path

syntax of the XPATHW32 testing utility

The /e parameter specifies the node where the search of the XPath starts. You can omit this parameter if you want the search to start from the beginning. A pair of double quotes is required to enclose the search mask

For example, Text string = Car 1 is Toyota.

This example searches the node Car with the attribute Name=“Car1”.

XPATH( <xml value:xml>, <XPath expression:text> [, <xml header:boolean> ]):xml

XMLTRANSFORM(XMLType_instance, XMLType_instance)

XSLT( <XML value:xml>, <XSL value:xml> ):xml

XSLT( <XML value:{xml\|text}>, <xslValue:{xml\|text}>, [, <is path to XML:boolean> ] [, <is path to XSLT:boolean> ]:xml

Oracle Function

Denodo Function

LISTAGG(value, delimiter) WITHIN GROUP (ORDER BY [sort_column])

value: This is the column or expression containing the values you want to include in the string.

delimiter: This is the string you want to use as a separator between the values.

ORDER BY [sort_column] (Optional): This is used to specify the order in which the values should be included in the string.

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.

CAST ( Expression AS Datatype)

CAST( <vdp data type:text>, <value:expression> )

Oracle Function

Denodo Function

AVG(expr)

AVG( <expression> ) : double

COUNT(expr)

COUNT( <count parameter> )

FIRST(columnname)

FIRST ( <field name:identifier> )

GROUP_CONCAT(columnname)

GROUP_CONCAT( [ DISTINCT | ALL ] <field name:identifier>)

GROUP_CONCAT( [ <row separator:text> [, <field separator:text> ] ],

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

GROUP_CONCAT( <ignore null:boolean> , <row separator:text>, <field separator:text>,

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

LAST(columnname)

LAST ( <field name:identifier> )

create a function that returns an array (or collection) of values from a specified field using VARRAY or TABLE collections.

LIST ( <field name:identifier> )

MAX(columnname)

MAX ( <expression> )

MEDIAN( expression )

MEDIAN ( <expression> )

MIN(columnname)

MIN ( <expression> )

STDDEV(columnname)

STDEV( <expression> ) : decimal

STDDEV_POP(columnname)

STDEVP( <expression> ) : decimal

SUM(expr)

SUM ( <expression> )

VARIANCE(columnname)

VAR( <expression> ) : decimal

VAR_POP(columnname)

VARP( <expression> ) : double

Oracle Function

Denodo Function

AVG(column_to_average) OVER (PARTITION BY partition_column ORDER BY order_column ROWS BETWEEN PRECEDING AND FOLLOWING)

AVG ( <expression> ) OVER (

        [ <window partition clause> ]

        [ <window order by clause> ]

        [ <window_frame_clause> ]

) : number

COUNT({ expr }) [ OVER (analytic_clause) ]

COUNT (

        {

           <expression>

        }

) OVER (

        [ <window partition clause> ]

        <window order by clause>

) : number

CUME_DIST() OVER (ORDER BY columnname)

CUME_DIST() OVER (

        [ <window partition clause> ]

        <window order by clause>

) : number

DENSE_RANK( ) OVER([ query_partition_clause ] order_by_clause)

DENSE_RANK( ) OVER (

        [ <window partition clause> ]

        <window order by clause>

 ) : numeric

FIRST_VALUE(expression) OVER (PARTITION BY column1, column2 ORDER BY column3)

FIRST_VALUE( <expression> ) OVER (

        [ <window partition clause> ]

        [ <window order by clause> ]

        [ <window_frame_clause> ]

) : <type of the input expression>

LAG(value_expr, offset, default_value) OVER (ORDER BY sort_column_1 [ASC|DESC], ...)

LAG (

          <expression>

        [ , <offset:number>

        [, <default value:type of the input expression> ] ]

) OVER (

        [ <window partition clause> ]

        <window order by clause>

): <type of the input expression>

LAST_VALUE(expr) OVER ( [ PARTITION BY partition_list ] ORDER BY order_list BETWEEN start_point AND end_point )

LAST_VALUE( <expression> ) OVER (

        [ <window partition clause> ]

        [ <window order by clause> ]

        [ <window_frame_clause> ]

) : <type of the input expression>

LEAD(value_expr, offset, default_value) OVER (ORDER BY sort_clause)

LEAD(

          <expression>

        [ , <offset:number>

        [, <default value:type of the input expression> ] ]

) OVER (

        [ <window partition clause> ]

        <window order by clause>

): <type of the input expression>

LISTAGG(columnname, ', ') WITHIN GROUP (ORDER BY columnname) OVER (PARTITION BY columnname)

LISTAGG (

          <measure expression>

        , <delimiter expression:text>

) WITHIN GROUP ( <window order by clause> )

  OVER ( [ <window partition clause> ] ) : text

MAX(expr) [OVER (analytic_clause)]

MAX ( <expression> ) OVER (

        [ <window partition clause> ]

        [ <window order by clause> ]

        [ <window_frame_clause> ]

) : <type of the input expression>

MIN(expr) [OVER (analytic_clause)]

MIN ( <expression> ) OVER (

        [ <window partition clause> ]

        [ <window order by clause> ]

        [ <window_frame_clause> ]

) : <type of the input expression>

NTILE(expr) OVER (query_partition_clause order_by_clause)

NTILE ( <value> OVER (

        [ <window partition clause> ]

        <window order by clause>

): <number>

PERCENTILE_CONT(percentile_value) WITHIN GROUP (ORDER BY sort_expression).

PERCENTILE_CONT ( <percentile:number> )

        WITHIN GROUP ( <window order by clause> )

        OVER ( [ <window partition clause> ] )

PERCENTILE_DISC(percentile_value) WITHIN GROUP (ORDER BY sort_expression).

PERCENTILE_DISC ( <percentile:number> )

        WITHIN GROUP ( <window order by clause> )

        OVER ( [ <window partition clause> ] )

PERCENT_RANK() WITHIN GROUP (ORDER BY sort_expression).

PERCENT_RANK ( ) OVER (

        [ <window partition clause> ]

        <window order by clause>

) : number

RANK( )

   OVER ([ query_partition_clause ] order_by_clause)

RANK( ) OVER (

        [ <window partition clause> ]

        <window order by clause>

) : numeric

ROW_NUMBER() OVER (

        [PARTITION BY column_list]

        ORDER BY column_list

)

ROW_NUMBER ( ) OVER (

         [ <window partition clause> ] <window order by clause>

) : numeric

SUM(expr) OVER (

        [PARTITION BY column_list]

        ORDER BY column_list

)

SUM ( <expression> ) OVER (

        [ <window partition clause> ]

        [ <window order by clause> ]

        [ <window_frame_clause> ]

) : number

STDDEV(expr) OVER (ORDER BY column_list)

STDEV ( <expression> ) OVER (

        [ <window partition clause> ]

        [ <window order by clause> ]

) : number

Oracle Function

Denodo Function

COALESCE(expression1, expression2, ..., expressionN)

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

CASE

            WHEN INSTR(documents.text, 'specific_word', 1, 1) > 0 THEN

                SUBSTR(documents.text, INSTR(documents.text, 'specific_word', 1, 1) - 10, 20)

            ELSE

                documents.text

        END AS relevant_fragment

CONTEXTUALSUMMARY( <content:text>, <keyword:text>, [ <begin delim:text>,

        <end delim:text>, <fragment separator:text>, <fragment length:int>

        [, <max fragments number:int> [, <analyzer:text> ] ] ] )

SYS_CONTEXT('namespace', 'parameter')

GETSESSION( <parameter : literal> )

ORA_HASH (expr, max_bucket, seed_value)

expr: argument determines the data for which you want Oracle Database to compute a hash value.

max_bucket: argument determines the maximum bucket value returned by the hash function.

seed_value: argument enables Oracle to produce many different results for the same set of data.

HASH( <value:text> ):text

NULLIF(expr1, expr2)

NULLIF(<expression>, <expression>)

ROWNUM

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