Goal
This document is a quick reference for migrating Oracle SQL to Denodo VQL. The document is aimed at administrators and developers that want to efficiently migrate their existing Oracle queries to Denodo VQL queries.
Content
The Knowledge Base article VDP Conformance with Standard SQL contains a reference of the Virtual DataPort conformance with the SQL 92 standard. The document is focused on query capabilities and contains information about: Data Types, SQL Predicates Support and SQL Functions Support. The Query Expressions section lists the expressions defined by the standard SQL and their equivalent in Virtual DataPort, explaining the differences with the standard when appropriate.
Following, a group of tables where the correspondence between Oracle functions and the Denodo equivalents is presented. This list is just a reference since more functions can be included in future Denodo versions.
Functions mapping
Numeric Functions
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 |
Text Functions
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 |
Datetime Functions
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 |
NOTE: When working with date patterns in Denodo and Oracle it is important to remember that the syntax of the date pattern is different and will need to be adapted. For instance, in Oracle “DD” (uppercase) means 'Day in month'; to indicate the same in Denodo the pattern should be “dd” (lowercase). For more information about the date patterns in Denodo see Date and Time Pattern Strings — VQL Guide.
In the above list, there are different types of date and time functions:
- Functions that are related with datetime types that include dates (date, localdate, timestamp, timestamptz), will be referred as daterelatedvalue.
- Functions that are related with datetime types that include times (date, time, timestamp, timestamptz), will be referred as timerelatedvalue.
- Functions that receive any datetime values, except intervals, will be referred as datetimevalue.
XML Functions
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 |
Conversion Functions
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> ) |
Aggregate Functions
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 |
Analytical Functions
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 |
Other Functions
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 |
The information provided in the Denodo Knowledge Base is intended to assist our users in advanced uses of Denodo. Please note that the results from the application of processes and configurations detailed in these documents may vary depending on your specific environment. Use them at your own discretion.
For an official guide of supported features, please refer to the User Manuals. For questions on critical systems or complex environments we recommend you to contact your Denodo Customer Success Manager.