-
Language:
English
-
Language:
English
Red Hat Training
A Red Hat training course is available for Red Hat JBoss Data Virtualization
3.4. Scalar Functions
3.4.1. Scalar Functions
Important
3.4.2. Numeric Functions
Table 3.1. Numeric Functions
Function
|
Definition
|
Data Type Constraint
|
---|---|---|
+ - * /
|
Standard numeric operators
|
x in {integer, long, float, double, biginteger, bigdecimal}, return type is same as x
Note
The precision and scale of non-bigdecimal arithmetic function functions results matches that of Java. The results of bigdecimal operations match Java, except for division, which uses a preferred scale of max(16, dividend.scale + divisor.precision + 1), which then has trailing zeros removed by setting the scale to max(dividend.scale, normalized scale).
|
ABS(x)
|
Absolute value of x
|
See standard numeric operators above
|
ACOS(x)
|
Arc cosine of x
|
x in {double, bigdecimal}, return type is double
|
ASIN(x)
|
Arc sine of x
|
x in {double, bigdecimal}, return type is double
|
ATAN(x)
|
Arc tangent of x
|
x in {double, bigdecimal}, return type is double
|
ATAN2(x,y)
|
Arc tangent of x and y
|
x, y in {double, bigdecimal}, return type is double
|
CEILING(x)
|
Ceiling of x
|
x in {double, float}, return type is double
|
COS(x)
|
Cosine of x
|
x in {double, bigdecimal}, return type is double
|
COT(x)
|
Cotangent of x
|
x in {double, bigdecimal}, return type is double
|
DEGREES(x)
|
Convert x degrees to radians
|
x in {double, bigdecimal}, return type is double
|
EXP(x)
|
e^x
|
x in {double, float}, return type is double
|
FLOOR(x)
|
Floor of x
|
x in {double, float}, return type is double
|
FORMATBIGDECIMAL(x, y)
|
Formats x using format y
|
x is bigdecimal, y is string, returns string
|
FORMATBIGINTEGER(x, y)
|
Formats x using format y
|
x is biginteger, y is string, returns string
|
FORMATDOUBLE(x, y)
|
Formats x using format y
|
x is double, y is string, returns string
|
FORMATFLOAT(x, y)
|
Formats x using format y
|
x is float, y is string, returns string
|
FORMATINTEGER(x, y)
|
Formats x using format y
|
x is integer, y is string, returns string
|
FORMATLONG(x, y)
|
Formats x using format y
|
x is long, y is string, returns string
|
LOG(x)
|
Natural log of x (base e)
|
x in {double, float}, return type is double
|
LOG10(x)
|
Log of x (base 10)
|
x in {double, float}, return type is double
|
MOD(x, y)
|
Modulus (remainder of x / y)
|
x in {integer, long, float, double, biginteger, bigdecimal}, return type is same as x
|
PARSEBIGDECIMAL(x, y)
|
Parses x using format y
|
x, y are strings, returns bigdecimal
|
PARSEBIGINTEGER(x, y)
|
Parses x using format y
|
x, y are strings, returns biginteger
|
PARSEDOUBLE(x, y)
|
Parses x using format y
|
x, y are strings, returns double
|
PARSEFLOAT(x, y)
|
Parses x using format y
|
x, y are strings, returns float
|
PARSEINTEGER(x, y)
|
Parses x using format y
|
x, y are strings, returns integer
|
PARSELONG(x, y)
|
Parses x using format y
|
x, y are strings, returns long
|
PI()
|
Value of Pi
|
return is double
|
POWER(x,y)
|
x to the y power
|
x in {double, bigdecimal, biginteger}, return is the same type as x
|
RADIANS(x)
|
Convert x radians to degrees
|
x in {double, bigdecimal}, return type is double
|
RAND()
|
Returns a random number, using generator established so far in the query or initializing with system clock if necessary.
|
Returns double.
|
RAND(x)
|
Returns a random number, using new generator seeded with x.
|
x is integer, returns double.
|
ROUND(x,y)
|
Round x to y places; negative values of y indicate places to the left of the decimal point
|
x in {integer, float, double, bigdecimal} y is integer, return is same type as x
|
SIGN(x)
|
1 if x > 0, 0 if x = 0, -1 if x < 0
|
x in {integer, long, float, double, biginteger, bigdecimal}, return type is integer
|
SIN(x)
|
Sine value of x
|
x in {double, bigdecimal}, return type is double
|
SQRT(x)
|
Square root of x
|
x in {long, double, bigdecimal}, return type is double
|
TAN(x)
|
Tangent of x
|
x in {double, bigdecimal}, return type is double
|
BITAND(x, y)
|
Bitwise AND of x and y
|
x, y in {integer}, return type is integer
|
BITOR(x, y)
|
Bitwise OR of x and y
|
x, y in {integer}, return type is integer
|
BITXOR(x, y)
|
Bitwise XOR of x and y
|
x, y in {integer}, return type is integer
|
BITNOT(x)
|
Bitwise NOT of x
|
x in {integer}, return type is integer
|
3.4.3. Parsing Numeric Data Types from Strings
parseDouble
- parses a string as a doubleparseFloat
- parses a string as a floatparseLong
- parses a string as a longparseInteger
- parses a string as an integer
java.text.DecimalFormat
class. See examples below.
Input String
|
Function Call to Format String
|
Output Value
|
Output Data Type
|
---|---|---|---|
'$25.30'
|
parseDouble(cost, '$#,##0.00;($#,##0.00)')
|
25.3
|
double
|
'25%'
|
parseFloat(percent, '#,##0%')
|
25
|
float
|
'2,534.1'
|
parseFloat(total, '#,##0.###;-#,##0.###')
|
2534.1
|
float
|
'1.234E3'
|
parseLong(amt, '0.###E0')
|
1234
|
long
|
'1,234,567'
|
parseInteger(total, '#,##0;-#,##0')
|
1234567
|
integer
|
Note
3.4.4. Formatting Numeric Data Types as Strings
formatDouble
- formats a double as a stringformatFloat
- formats a float as a stringformatLong
- formats a long as a stringformatInteger
- formats an integer as a string
java.text.DecimalFormat
class. See examples below.
Input Value
|
Input Data Type
|
Function Call to Format String
|
Output String
|
---|---|---|---|
25.3
|
double
|
formatDouble(cost, '$#,##0.00;($#,##0.00)')
|
'$25.30'
|
25
|
float
|
formatFloat(percent, '#,##0%')
|
'25%'
|
2534.1
|
float
|
formatFloat(total, '#,##0.###;-#,##0.###')
|
'2,534.1'
|
1234
|
long
|
formatLong(amt, '0.###E0')
|
'1.234E3'
|
1234567
|
integer
|
formatInteger(total, '#,##0;-#,##0')
|
'1,234,567'
|
Note
3.4.5. String Functions
Important
ASCII(x)
, CHR(x)
, and CHAR(x)
may produce different results or exceptions depending on where the function is evaluated (JBoss Data Virtualization vs. source). JBoss Data Virtualization uses Java default int to char and char to int conversions, which operates over UTF16 values.
Table 3.2. String Functions
Function
|
Definition
|
DataType Constraint
|
---|---|---|
x || y
|
Concatenation operator
|
x,y in {string}, return type is string
|
ASCII(x)
|
Provide ASCII value of the left most character in x. The empty string will return null.
|
return type is integer
|
CHR(x) CHAR(x)
|
Provide the character for ASCII value x
|
x in {integer}
|
CONCAT(x, y)
|
Concatenates x and y with ANSI semantics. If x and/or y is null, returns null.
|
x, y in {string}
|
CONCAT2(x, y)
|
Concatenates x and y with non-ANSI null semantics. If x and y is null, returns null. If only x or y is null, returns the other value.
|
x, y in {string}
|
ENDSWITH(x, y)
|
Checks if y ends with x. If only x or y is null, returns null.
|
x, y in {string}, returns boolean
|
INITCAP(x)
|
Make first letter of each word in string x capital and all others lowercase
|
x in {string}
|
INSERT(str1, start, length, str2)
|
Insert string2 into string1
|
str1 in {string}, start in {integer}, length in {integer}, str2 in {string}
|
LCASE(x)
|
Lowercase of x
|
x in {string}
|
LEFT(x, y)
|
Get left y characters of x
|
x in {string}, y in {integer}, return string
|
LENGTH(x)
|
Length of x
|
return type is integer
|
LOCATE(x, y)
|
Find position of x in y starting at beginning of y
|
x in {string}, y in {string}, return integer
|
LOCATE(x, y, z)
|
Find position of x in y starting at z
|
x in {string}, y in {string}, z in {integer}, return integer
|
LPAD(x, y)
|
Pad input string x with spaces on the left to the length of y
|
x in {string}, y in {integer}, return string
|
LPAD(x, y, z)
|
Pad input string x on the left to the length of y using character z
|
x in {string}, y in {string}, z in {character}, return string
|
LTRIM(x)
|
Left trim x of blank characters
|
x in {string}, return string
|
QUERYSTRING(path [, expr [AS name] ...])
|
Returns a properly encoded query string appended to the given path. Null valued expressions are omitted, and a null path is treated as ''.
Names are optional for column reference expressions.
e.g. QUERYSTRING('path', 'value' as "&x", ' & ' as y, null as z) returns 'path?%26x=value&y=%20%26%20'
|
path, expr in {string}. name is an identifier
|
REPEAT(str1,instances)
|
Repeat string1 a specified number of times
|
str1 in {string}, instances in {integer} return string
|
REPLACE(x, y, z)
|
Replace all y in x with z
|
x,y,z in {string}, return string
|
RIGHT(x, y)
|
Get right y characters of x
|
x in {string}, y in {integer}, return string
|
RPAD(input string x, pad length y)
|
Pad input string x with spaces on the right to the length of y
|
x in {string}, y in {integer}, return string
|
RPAD(x, y, z)
|
Pad input string x on the right to the length of y using character z
|
x in {string}, y in {string}, z in {character}, return string
|
RTRIM(x)
|
Right trim x of blank characters
|
x is string, return string
|
SPACE(x)
|
Repeats space x times
|
x in {integer}
|
SUBSTRING(x, y)
SUBSTRING(x FROM y)
|
Get substring from x, from position y to the end of x
|
y in {integer}
|
SUBSTRING(x, y, z)
SUBSTRING(x FROM y FOR z)
|
Get substring from x from position y with length z
|
y, z in {integer}
|
TO_CHARS(x, encoding)
|
Return a CLOB from the BLOB with the given encoding. BASE64, HEX, and the built-in Java Charset names are valid values for the encoding.
Note
For charsets, unmappable chars will be replaced with the charset default character. Binary formats, such as BASE64, will error in their conversion to bytes if an unrecognizable character is encountered.
|
x is a BLOB, encoding is a string, and returns a CLOB
|
TO_BYTES(x, encoding)
|
Return a BLOB from the CLOB with the given encoding. BASE64, HEX, and the builtin Java Charset names are valid values for the encoding.
|
x in a CLOB, encoding is a string, and returns a BLOB
|
TRANSLATE(x, y, z)
|
Translate string x by replacing each character in y with the character in z at the same position.
Note that the second arg (y) and the third arg (z) must be the same length. If they are not equal, Red Hat JBoss data Virtualization throws this exception: 'TEIID30404 Source and destination character lists must be the same length.'
|
x in {string}
|
TRIM([[LEADING|TRAILING|BOTH] [x] FROM] y)
|
Trim character x from the leading, trailing, or both ends of string y. If LEADING/TRAILING/BOTH is not specified, BOTH is used by default. If no trim character x is specified, a blank space ' ' is used for x by default.
|
x in {character}, y in {string}
|
UCASE(x)
|
Uppercase of x
|
x in {string}
|
UNESCAPE(x)
|
Unescaped version of x. Possible escape sequences are \b - backspace, \t - tab, \n - line feed, \f - form feed, \r - carriage return. \uXXXX, where X is a hex value, can be used to specify any unicode character. \XXX, where X is an octal digit, can be used to specify an octal byte value. If any other character appears after an escape character, that character will appear in the output and the escape character will be ignored.
|
x in {string}
|
3.4.6. Date/Time Functions
Table 3.3. Date and Time Functions
Function
|
Definition
|
Datatype Constraint
|
---|---|---|
CURDATE()
|
Return current date
|
returns date
|
CURTIME()
|
Return current time
|
returns time
|
NOW()
|
Return current timestamp (date and time)
|
returns timestamp
|
DAYNAME(x)
|
Return name of day in the default locale
|
x in {date, timestamp}, returns string
|
DAYOFMONTH(x)
|
Return day of month
|
x in {date, timestamp}, returns integer
|
DAYOFWEEK(x)
|
Return day of week (Sunday=1, Saturday=7)
|
x in {date, timestamp}, returns integer
|
DAYOFYEAR(x)
|
Return day number
|
x in {date, timestamp}, returns integer
|
EXTRACT(YEAR|MONTH|DAY|HOUR|MINUTE|SECOND FROM x)
|
Return the given field value from the date value x. Produces the same result as the associated YEAR, MONTH, DAYOFMONTH, HOUR, MINUTE, SECOND functions.
The SQL specification also allows for TIMEZONE_HOUR and TIMEZONE_MINUTE as extraction targets. In JBoss Data Virtualization, all date values are in the timezone of the server.
|
x in {date, time, timestamp}, returns integer
|
FORMATDATE(x, y)
|
Format date x using format y
|
x is date, y is string, returns string
|
FORMATTIME(x, y)
|
Format time x using format y
|
x is time, y is string, returns string
|
FORMATTIMESTAMP(x, y)
|
Format timestamp x using format y
|
x is timestamp, y is string, returns string
|
FROM_UNIXTIME (unix_timestamp)
|
Return the Unix timestamp (in seconds) as a Timestamp value
|
Unix timestamp (in seconds)
|
HOUR(x)
|
Return hour (in military 24-hour format)
|
x in {time, timestamp}, returns integer
|
MINUTE(x)
|
Return minute
|
x in {time, timestamp}, returns integer
|
MODIFYTIMEZONE (timestamp, startTimeZone, endTimeZone)
|
Returns a timestamp based upon the incoming timestamp adjusted for the differential between the start and end time zones. i.e. if the server is in GMT-6, then modifytimezone({ts '2006-01-10 04:00:00.0'},'GMT-7', 'GMT-8') will return the timestamp {ts '2006-01-10 05:00:00.0'} as read in GMT-6. The value has been adjusted 1 hour ahead to compensate for the difference between GMT-7 and GMT-8.
|
startTimeZone and endTimeZone are strings, returns a timestamp
|
MODIFYTIMEZONE (timestamp, endTimeZone)
|
Return a timestamp in the same manner as modifytimezone(timestamp, startTimeZone, endTimeZone), but will assume that the startTimeZone is the same as the server process.
|
Timestamp is a timestamp; endTimeZone is a string, returns a timestamp
|
MONTH(x)
|
Return month
|
x in {date, timestamp}, returns integer
|
MONTHNAME(x)
|
Return name of month in the default locale
|
x in {date, timestamp}, returns string
|
PARSEDATE(x, y)
|
Parse date from x using format y
|
x, y in {string}, returns date
|
PARSETIME(x, y)
|
Parse time from x using format y
|
x, y in {string}, returns time
|
PARSETIMESTAMP(x,y)
|
Parse timestamp from x using format y
|
x, y in {string}, returns timestamp
|
QUARTER(x)
|
Return quarter
|
x in {date, timestamp}, returns integer
|
SECOND(x)
|
Return seconds
|
x in {time, timestamp}, returns integer
|
TIMESTAMPCREATE(date, time)
|
Create a timestamp from a date and time
|
date in {date}, time in {time}, returns timestamp
|
TIMESTAMPADD(interval, count, timestamp)
|
Add a specified interval (hour, day of week, month) to the timestamp, where intervals can be:
Note
The full interval amount based upon calendar fields will be added. For example adding 1 QUARTER will move the timestamp up by three full months and not just to the start of the next calendar quarter.
|
The interval constant may be specified either as a string literal or a constant value. Interval in {string}, count in {integer}, timestamp in {date, time, timestamp}
|
TIMESTAMPDIFF(interval, startTime, endTime)
|
Calculates the date part intervals crossed between the two timestamps. interval is one of the same keywords as those used for TIMESTAMPADD.
If (endTime > startTime), a positive number will be returned. If (endTime < startTime), a negative number will be returned. The date part difference is counted regardless of how close the timestamps are. For example, '2000-01-02 00:00:00.0' is still considered 1 hour ahead of '2000-01-01 23:59:59.999999'.
Note
TIMESTAMPDIFF typically returns an integer, however JBoss Data Virtualization returns a long. You will encounter an exception if you expect a value out of the integer range from a pushed down TIMESTAMPDIFF.
Note
The implementation of TIMESTAMPDIFF in previous versions returned values based upon the number of whole canonical interval approximations (365 days in a year, 91 days in a quarter, 30 days in a month, etc.) crossed. For example the difference in months between 2013-03-24 and 2013-04-01 was 0, but based upon the date parts crossed is 1. See the System Properties section in Red Hat JBoss Data Virtualization Administration and Configuration Guide for backwards compatibility.
|
Interval in {string}; startTime, endTime in {timestamp}, returns a long.
|
WEEK(x)
|
Return week in year (1-53). see also System Properties for customization.
|
x in {date, timestamp}, returns integer
|
YEAR(x)
|
Return four-digit year
|
x in {date, timestamp}, returns integer
|
3.4.7. Parsing Date Data Types from Strings
parseDate
parseTime
parseTimestamp
java.text.SimpleDateFormat
class. See examples below.
Table 3.4. Functions to Parse Dates
String
|
Function Call To Parse String
|
---|---|
'19970101'
|
parseDate(myDateString, 'yyyyMMdd')
|
'31/1/1996'
|
parseDate(myDateString, 'dd''/''MM''/''yyyy')
|
'22:08:56 CST'
|
parseTime (myTime, 'HH:mm:ss z')
|
'03.24.2003 at 06:14:32'
|
parseTimestamp(myTimestamp, 'MM.dd.yyyy ''at'' hh:mm:ss')
|
Note
3.4.8. Specifying Time Zones
3.4.9. Type Conversion Functions
Table 3.5. Type Conversion Functions
Function
|
Definition
|
---|---|
CONVERT(x, type)
|
Convert x to type, where type is a JBoss Data Virtualization Base Type
|
CAST(x AS type)
|
Convert x to type, where type is a JBoss Data Virtualization Base Type
|
3.4.10. Choice Functions
Table 3.6. Type Conversion Functions
Function
|
Definition
|
Data Type Constraint
|
---|---|---|
COALESCE(x,y+)
|
Returns the first non-null parameter
|
x and all y's can be any compatible types
|
IFNULL(x,y)
|
If x is null, return y; else return x
|
x, y, and the return type must be the same type but can be any type
|
NVL(x,y)
|
If x is null, return y; else return x
|
x, y, and the return type must be the same type but can be any type
|
NULLIF(param1, param2)
|
Equivalent to case when (param1 = param2) then null else param1
|
param1 and param2 must be compatible comparable types
|
Note
3.4.11. Decode Functions
Table 3.7. Decode Functions
Function
|
Definition
|
Data Type Constraint
|
---|---|---|
DECODESTRING(x, y [, z])
|
Decode column x using value pairs in y (with optional delimiter, z) and return the decoded column as a set of strings.
Warning
Deprecated. Use a CASE expression instead.
|
All string
|
DECODEINTEGER(x, y [, z])
|
Decode column x using value pairs in y (with optional delimiter z) and return the decoded column as a set of integers.
Warning
Deprecated. Use a CASE expression instead.
|
All string parameters, return integer
|
- x is the input value for the decode operation. This will generally be a column name.
- y is the literal string that contains a delimited set of input values and output values.
- z is an optional parameter on these methods that allows you to specify what delimiter the string specified in y uses.
SELECT DECODEINTEGER(IS_IN_STOCK, 'false, 0, true, 1') FROM PartsSupplier.PARTS;
SELECT DECODESTRING(IS_IN_STOCK, 'false, no, true, yes, null') FROM PartsSupplier.PARTS;
SELECT DECODESTRING(IS_IN_STOCK, 'false:no:true:yes:null',':') FROM PartsSupplier.PARTS;
SELECT DECODESTRING( IS_IN_STOCK, 'null,no,"null",no,nil,no,false,no,true,yes' ) FROM PartsSupplier.PARTS;
3.4.12. Lookup Function
codeTable
, the following function will find the row where keyColumn
has the value, keyValue
, and return the associated returnColumn
value (or null if no matching key is found).
LOOKUP(codeTable, returnColumn, keyColumn, keyValue)
codeTable
must be a string literal that is the fully qualified name of the target table. returnColumn
and keyColumn
must also be string literals and match corresponding column names in codeTable
. keyValue
can be any expression that must match the datatype of the keyColumn
. The return data type matches that of returnColumn
.
ISOCountryCodes
table is used to translate country names to ISO codes:
lookup('ISOCountryCodes', 'CountryCode', 'CountryName', 'UnitedStates')
CountryName
represents a key column and CountryCode
represents the ISO code of the country. A query to this lookup table would provide a CountryName
, in this case 'UnitedStates', and expect a CountryCode
in response.
Note
Important
- The key column must contain unique values. If the column contains duplicate values, an exception will be thrown.
3.4.13. System Functions
Function
|
Definition
|
Data Type Constraint
|
---|---|---|
COMMANDPAYLOAD([key])
|
If the key parameter is provided, the command payload object is cast to a java.util.Properties object and the corresponding property value for the key is returned. If the key is not specified, the return value is the command payload toString value.
The command payload is set by the
TeiidStatement.setPayload method on the Data Virtualization JDBC API extensions on a per-query basis.
|
key in {string}, return value is string
|
ENV(key)
|
Retrieve a system environment property.
Note
The only key specific to the current session is 'sessionid'. The preferred mechanism for getting the session id is with the session_id() function.
Note
To prevent untrusted access to system properties, this function is not enabled by default. The ENV function may be enabled via the allowEnvFunction property.
|
key in {string}, return value is string
|
SESSION_ID()
|
Retrieve the string form of the current session id.
|
return value is string
|
USER()
|
Retrieve the name of the user executing the query.
|
return value is string
|
CURRENT_DATABASE()
|
Retrieve the catalog name of the database which, for the VDB, is the VDB name.
|
return value is string
|
TEIID_SESSION_GET(name)
|
Retrieve the session variable.
A null name will return a null value. Typically you will use the a get wrapped in a CAST to convert to the desired type.
|
name in {string}, return value is object
|
TEIID_SESSION_SET(name, value)
|
Set the session variable.
The previous value for the key or null will be returned. A set has no effect on the current transaction and is not affected by commit/rollback.
|
name in {string}, value in {object}, return value is object
|
3.4.14. XML Functions
TABLE Customer ( CustomerId integer PRIMARY KEY, CustomerName varchar(25), ContactName varchar(25) Address varchar(50), City varchar(25), PostalCode varchar(25), Country varchar(25), );
CustomerID CustomerName ContactName Address City PostalCode Country 87 Wartian Herkku Pirkko Koskitalo Torikatu 38 Oulu 90110 Finland 88 Wellington Importadora Paula Parente Rua do Mercado, 12 Resende 08737-363 Brazil 89 White Clover Markets Karl Jablonski 305 - 14th Ave. S. Suite 3B Seattle 98128 USA
- XMLCOMMENT
XMLCOMMENT(comment)
Returns an XML comment.comment
is a string. Return value is XML.- XMLCONCAT
XMLCONCAT(content [, content]*)
Returns XML with the concatenation of the given XML types. If a value is null, it will be ignored. If all values are null, null is returned. This is how you concatenate two or more XML fragments:SELECT XMLCONCAT( XMLELEMENT("name", CustomerName), XMLPARSE(CONTENT ' <a> b </a>' WELLFORMED) ) FROM Customer c WHERE c.CustomerID = 87; ========================================================== <name> Wartian Herkku </name> <a> b </a>
content
is XML. Return value is XML.- XMLELEMENT
XMLELEMENT([NAME] name [, <NSP>] [, <ATTR>][, content]*) ATTR:=XMLATTRIBUTES(exp [AS name] [, exp [AS name]]*) NSP:=XMLNAMESPACES((uri AS prefix | DEFAULT uri | NO DEFAULT))+
Returns an XML element with the given name and content. If the content value is of a type other than XML, it will be escaped when added to the parent element. Null content values are ignored. Whitespace in XML or the string values of the content is preserved, but no whitespace is added between content values.XMLNAMESPACES is used to provide namespace information. NO DEFAULT is equivalent to defining the default namespace to the null URI -xmlns=""
. Only one DEFAULT or NO DEFAULT namespace item may be specified. The namespace prefixesxmlns
andxml
are reserved.If an attribute name is not supplied, the expression must be a column reference, in which case the attribute name will be the column name. Null attribute values are ignored.For example, with an xml_value of <doc/>,XMLELEMENT(NAME "elem", 1, '<2/>', xml_value)
returns<elem>1<2/><doc/><elem/>
name
andprefix
are identifiers.uri
is a string literal.content
can be any type. Return value is XML. The return value is valid for use in places where a document is expected.SELECT XMLELEMENT("name", CustomerName) FROM Customer c WHERE c.CustomerID = 87; ========================================================== <name>Wartian Herkku</name> "Multiple Columns" SELECT XMLELEMENT("customer", XMLELEMENT("name", c.CustomerName), XMLELEMENT("contact", c.ContactName)) FROM Customer c WHERE c.CustomerID = 87; ========================================================== <customer><name>Wartian Herkku</name><contact>Pirkko Koskitalo</contact></customer> "Columns as Attributes" SELECT XMLELEMENT("customer", XMLELEMENT("name", c.CustomerName, XMLATTRIBUTES( "contact" as c.ContactName, "id" as c.CustomerID ) ) ) FROM Customer c WHERE c.CustomerID = 87; ========================================================== <customer><name contact="Pirkko Koskitalo" id="87">Wartian Herkku</name></customer>
- XMLFOREST
XMLFOREST(content [AS name] [, <NSP>] [, content [AS name]]*)
Returns an concatenation of XML elements for each content item. See XMLELEMENT for the definition of NSP. If a name is not supplied for a content item, the expression must be a column reference, in which case the element name will be a partially escaped version of the column name.name
is an identifier.content
can be any type. Return value is XML.You can use XMLFORREST to simplify the declaration of multiple XMLELEMENTS, XMLFOREST function allows you to process multiple columns at once:SELECT XMLELEMENT("customer", XMLFOREST( c.CustomerName AS "name", c.ContactName AS "contact" )) FROM Customer c WHERE c.CustomerID = 87; ========================================================== <customer><name>Wartian Herkku</name><contact>Pirkko Koskitalo</contact></customer> XMLAGG XMLAGG is an aggregate function, that takes a collection of XML elements and returns an aggregated XML document. XMLAGG(xml) From above example in XMLElement, each row in the Customer table table will generate row of XML if there are multiple rows matching the criteria. That will generate a valid XML, but it will not be well formed, because it lacks the root element. XMLAGG can used to correct that "Example" SELECT XMLELEMENT("customers", XMLAGG( XMLELEMENT("customer", XMLFOREST( c.CustomerName AS "name", c.ContactName AS "contact" ))) FROM Customer c ========================================================== <customers> <customer><name>Wartian Herkku</name><contact>Pirkko Koskitalo</contact></customer> <customer><name>Wellington Importadora</name><contact>Paula Parente</contact></customer> <customer><name>White Clover Markets</name><contact>Karl Jablonski</contact></customer> </customers>
- XMLPARSE
XMLPARSE((DOCUMENT|CONTENT) expr [WELLFORMED])
Returns an XML type representation of the string value expression. If DOCUMENT is specified, then the expression must have a single root element and may or may not contain an XML declaration. If WELLFORMED is specified then validation is skipped; this is especially useful for CLOB and BLOB known to already be valid.expr
in {string, clob, blob and varbinary}. Return value is XML.If DOCUMENT is specified then the expression must have a single root element and may or may not contain an XML declaration. If WELLFORMED is specified then validation is skipped; this is especially useful for CLOB and BLOB known to already be valid.SELECT XMLPARSE(CONTENT '<customer><name>Wartian Herkku</name><contact>Pirkko Koskitalo</contact></customer>' WELLFORMED);
Will return a SQLXML with contents:<customer><name>Wartian Herkku</name><contact>Pirkko Koskitalo</contact></customer>
- XMLPI
XMLPI([NAME] name [, content])
Returns an XML processing instruction.name
is an identifier.content
is a string. Return value is XML.- XMLQUERY
XMLQUERY([<NSP>] xquery [<PASSING>] [(NULL|EMPTY) ON EMPTY]] PASSING:=PASSING exp [AS name] [, exp [AS name]]*
Returns the XML result from evaluating the givenxquery
. See XMLELEMENT for the definition of NSP. Namespaces may also be directly declared in the XQuery prolog.The optional PASSING clause is used to provide the context item, which does not have a name, and named global variable values. If the XQuery uses a context item and none is provided, then an exception will be raised. Only one context item may be specified and should be an XML type. All non-context non-XML passing values will be converted to an appropriate XML type.The ON EMPTY clause is used to specify the result when the evaluated sequence is empty. EMPTY ON EMPTY, the default, returns an empty XML result. NULL ON EMPTY returns a null result.xquery
in string. Return value is XML.Note
XMLQUERY is part of the SQL/XML 2006 specification.See also XMLTABLE.- XMLEXISTS
- Returns true if a non-empty sequence would be returned by evaluating the given xquery.
XMLEXISTS([<NSP>] xquery [<PASSING>]] PASSING:=PASSING exp [AS name] [, exp [AS name]]*
Namespaces may also be directly declared in the xquery prolog.The optional PASSING clause is used to provide the context item, which does not have a name, and named global variable values. If the xquery uses a context item and none is provided, then an exception will be raised. Only one context item may be specified and should be an XML type. All non-context non-XML passing values will be converted to an appropriate XML type. Null/Unknown will be returned if the context item evaluates to null.xquery in string. Return value is boolean.XMLEXISTS is part of the SQL/XML 2006 specification. - XMLSERIALIZE
XMLSERIALIZE([(DOCUMENT|CONTENT)] xml [AS datatype] [ENCODING enc] [VERSION ver] [(INCLUDING|EXCLUDING) XMLDECLARATION])
Returns a character type representation of the XML expression.datatype
may be character (string, varchar, clob) or binary (blob, varbinary). CONTENT is the default. If DOCUMENT is specified and the XML is not a valid document or fragment, then an exception is raised.Return value matches data type. If no data type is specified, then CLOB will be assumed.The encodingenc
is specified as an identifier. A character serialization may not specify an encoding. The versionver
is specified as a string literal. If a particular XMLDECLARATION is not specified, then the result will have a declaration only if performing a non UTF-8/UTF-16 or non version 1.0 document serialization or the underlying XML has an declaration. If CONTENT is being serialized, then the declaration will be omitted if the value is not a document or element.The following example produces a BLOB of XML in UTF-16 including the appropriate byte order mark of FE FF and XML declaration:XMLSERIALIZE(DOCUMENT value AS BLOB ENCODING "UTF-16" INCLUDING XMLDECLARATION)
- XSLTRANSFORM
XSLTRANSFORM(doc, xsl)
Applies an XSL stylesheet to the given document.doc
andxsl
in {string, clob, xml}. Return value is a CLOB. If either argument is null, the result is null.- XPATHVALUE
XPATHVALUE(doc, xpath)
Applies the XPATH expression to the document and returns a string value for the first matching result. For more control over the results and XQuery, use the XMLQUERY function.Matching a non-text node will still produce a string result, which includes all descendant text nodes.doc
andxpath
in {string, clob, xml}. Return value is a string.When the input document utilizes namespaces, it is sometimes necessary to specify XPATH that ignores namespaces. For example, given the following XML,<?xml version="1.0" ?> <ns1:return xmlns:ns1="http://com.test.ws/exampleWebService">Hello<x> World</x></return>
the following function results in 'Hello World'.xpathValue(value, '/*[local-name()="return"])
3.4.15. JSON Functions
- JSONTOXML
JSONTOXML(rootElementName, json)
Returns an XML document from JSON. The appropriate UTF encoding (8, 16LE. 16BE, 32LE, 32BE) will be detected for JSON BLOBS. If another encoding is used, see the TO_CHARS function (see Section 3.4.5, “String Functions”).rootElementName
is a string,json
is in {clob, blob}. Return value is XML. The result is always a well-formed XML document.The mapping to XML uses the following rules:- The current element name is initially the
rootElementName
, and becomes the object value name as the JSON structure is traversed. - All element names must be valid XML 1.1 names. Invalid names are fully escaped according to the SQLXML specification.
- Each object or primitive value will be enclosed in an element with the current name.
- Unless an array value is the root, it will not be enclosed in an additional element.
- Null values will be represented by an empty element with the attribute
xsi:nil="true"
- Boolean and numerical value elements will have the attribute
xsi:type
set toboolean
anddecimal
respectively.
Example 3.1. Sample JSON to XML for jsonToXml('person', x)
JSON:{ "firstName" : "John" , "children" : [ "Randy", "Judy" ] }
XML:<?xml version="1.0" ?><person><firstName>John</firstName><children>Randy</children><children>Judy</children></person>
Example 3.2. Sample JSON to XML for jsonToXml('person', x) with a root array.
JSON:[{ "firstName" : "George" }, { "firstName" : "Jerry" }]
XML (Notice there is an extra "person" wrapping element to keep the XML well-formed):<?xml version="1.0" ?><person><person><firstName>George</firstName></person><person><firstName>Jerry</firstName></person></person>
JSON:Example 3.3. Sample JSON to XML for jsonToXml('root', x) with an invalid name.
{"/invalid" : "abc" }
XML:Example 3.4. Sample JSON to XML for jsonToXml('root', x) with an invalid name.
<?xml version="1.0" ?> <root> <_u002F_invalid>abc</_u002F_invalid> </root>
- JSONARRAY
JSONARRAY(value...)
Returns a JSON array.value
is any object convertable to a JSON value (see Section 3.4.17, “Conversion to JSON”). Return value is a CLOB marked as being valid JSON. Null values will be included in the result as null literals.For example:jsonArray('a"b', 1, null, false, {d'2010-11-21'})
returns["a\"b",1,null,false,"2010-11-21"]
- JSONOBJECT
JSONARRAY(value [as name] ...)
Returns a JSON object.value
is any object convertable to a JSON value (see Section 3.4.17, “Conversion to JSON”). Return value is a clob marked as being valid JSON.Null values will be included in the result as null literals.If a name is not supplied and the expression is a column reference, the column name will be used otherwise exprN will be used where N is the 1-based index of the value in the JSONARRAY expression.For example:jsonObject('a"b' as val, 1, null as "null")
returns{"val":"a\"b","expr2":1,"null":null}
- JSONPARSE
JSONPARSE(value, wellformed)
Validates and returns a JSON result.value
is blob with an appropriate JSON binary encoding (UTF-8, UTF-16, or UTF-32) or clob.wellformed
is a boolean indicating that validation should be skipped. Return value is a CLOB marked as being valid JSON.A null for either input will return null.jsonParse('"a"')
3.4.16. Spatial Functions
Important
Conversion Functions
- ST_GeomFromText
- Returns a geometry from a Clob in WKT format.
ST_GeomFromText(text [, srid])
text is a clob, srid is an optional integer. Return value is a geometry. - ST_GeomFromWKB/ST_GeomFromBinary
- Returns a geometry from a blob in WKB format.
ST_GeomFromWKB(bin [, srid])
bin is a blob, srid is an optional integer. Return value is a geometry. - ST_GeomFromGeoJSON
- Returns a geometry from a Clob in GeoJSON format.
ST_GeomFromGeoJson(text [, srid])
text is a clob, srid is an optional integer. Return value is a geometry. - ST_GeomFromGML
- Returns a geometry from a Clob in GML2 format.
ST_GeomFromGML(text [, srid])
text is a clob, srid is an optional integer. Return value is a geometry. - ST_AsText
ST_GeomAsText(geom)
geom is a geometry. Return value is clob in WKT format.- ST_AsBinary
ST_GeomAsBinary(geom)
geom is a geometry. Return value is a blob in WKB format.- ST_AsGeoJSON
ST_GeomAsGeoJSON(geom)
geom is a geometry. Return value is a clob with the GeoJSON value.- ST_AsGML
ST_GeomAsGML(geom)
geom is a geometry. Return value is a clob with the GML2 value.- ST_AsEWKT
ST_AsEWKT(geom)
geom is a geometry. Return value is a clob with the EWKT value. The EWKT value is the WKT value with the SRID prefix.- ST_AsKML
ST_AsKML(geom)
geom is a geometry. Return value is a clob with the KML value. The KML value is effectively a simplified GML value and projected into SRID 4326.
Relationship Functions
- ST_CONTAINS
- Returns true if geom1 contains geom2 contains another.
ST_CONTAINS(geom1, geom2)
geom1, geom2 are geometries. Return value is a boolean. - ST_CROSSES
- Returns true if the geometries cross.
ST_CROSSES(geom1, geom2)
geom1, geom2 are geometries. Return value is a boolean. - ST_DISJOINT
- Returns true if the geometries are disjoint.
ST_DISJOINT(geom1, geom2)
geom1, geom2 are geometries. Return value is a boolean. - ST_DISTANCE
- Returns the distance between two geometries.
ST_DISTANCE(geom1, geom2)
geom1, geom2 are geometries. Return value is a double. - ST_EQUALS
- Returns true if the two geometries are spatially equal - the points and order may differ, but neither geometry lies outside of the other.
ST_EQUALS(geom1, geom2)
geom1, geom2 are geometries. Return value is a boolean. - ST_INTERSECTS
- Returns true if the geometries intersect.
ST_INTERSECT(geom1, geom2)
geom1, geom2 are geometries. Return value is a boolean. - ST_OVERLAPS
- Returns true if the geometries overlap.
ST_OVERLAPS(geom1, geom2)
geom1, geom2 are geometries. Return value is a boolean. - ST_TOUCHES
- Returns true if the geometries touch.
ST_TOUCHES(geom1, geom2)
geom1, geom2 are geometries. Return value is a boolean.
Miscellaneous Functions
- ST_SRID
- Returns the SRID for the geometry.
ST_SRID(geom)
geom is a geometry. Return value is an integer. A 0 value rather than null will be returned for an unknown SRID on a non-null geometry. - ST_SetSRID
- Set the SRID for the given geometry.
ST_SetSRID(geom, srid)
geom is a geometry. srid is an integer. Return value is a geometry. Only the SRID metadata of the geometry is modified. - ST_TRANSFORM
- Transforms the geometry value from one coordinate system to another.
ST_TRANSFORM(geom, srid)
geom is a geometry. srid is an integer. Return value is a geometry. The srid value and the srid of the geometry value must exist in the SPATIAL_REF_SYS view.
3.4.17. Conversion to JSON
- null values are included as the null literal.
- values parsed as JSON or returned from a JSON construction function (JSONPARSE, JSONARRAY, JSONARRAY_AGG) will be directly appended into a JSON result.
- boolean values are included as true/false literals
- numeric values are included as their default string conversion - in some circumstances if not a number or +-infinity results are allowed, invalid JSON may be obtained.
- string values are included in their escaped/quoted form.
- binary values are not implicitly convertible to JSON values and require a specific prior to inclusion in JSON.
- all other values will be included as their string conversion in the appropriate escaped/quoted form.
3.4.18. Security Functions
- HASROLE
hasRole([roleType,] roleName)
Whether the current caller has the JBoss Data Virtualization data roleroleName
.roleName
must be a string, the return type is boolean.The two argument form is provided for backwards compatibility.roleType
is a string and must be 'data'.Role names are case-sensitive and only match JBoss Data Virtualization data roles (see Section 7.1, “Data Roles”). JAAS roles/groups names are not valid for this function, unless there is corresponding data role with the same name.
3.4.19. Miscellaneous Functions
- array_get
array_get(array, index)
Returns the object value at a given array index.array
is the object type,index
must be an integer, and the return type is object.One-based indexing is used. The actual array value must be ajava.sql.Array
or Java array type. An exception will be thrown if the array value is the wrong type of the index is out of bounds.- array_length
array_length(array)
Returns the length for a given array.array
is the object type, and the return type is integer.The actual array value must be ajava.sql.Array
or Java array type. An exception will be thrown if the array value is the wrong type.- uuid
uuid()
Returns a universally unique identifier.The return type is string.Generates a type 4 (pseudo randomly generated) UUID using a cryptographically strong random number generator. The format is XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX where each X is a hex digit.
3.4.20. Nondeterministic Function Handling
- Deterministic - the function will always return the same result for the given inputs. Deterministic functions are evaluated by the engine as soon as all input values are known, which may occur as soon as the rewrite phase. Some functions, such as the
lookup
function, are not truly deterministic, but is treated as such for performance. All functions not categorized below are considered deterministic. - User Deterministic - the function will return the same result for the given inputs for the same user. This includes the
hasRole
and user functions. User deterministic functions are evaluated by the engine as soon as all input values are known, which may occur as soon as the rewrite phase. If a user deterministic function is evaluated during the creation of a prepared processing plan, then the resulting plan will be cached only for the user. - Session Deterministic - the function will return the same result for the given inputs under the same user session. This category includes the
env
function. Session deterministic functions are evaluated by the engine as soon as all input values are known, which may occur as soon as the rewrite phase. If a session deterministic function is evaluated during the creation of a prepared processing plan, then the resulting plan will be cached only for the user's session. - Command Deterministic - the result of function evaluation is only deterministic within the scope of the user command. This category include the
curdate
,curtime
,now
, andcommandpayload
functions. Command deterministic functions are delayed in evaluation until processing to ensure that even prepared plans utilizing these functions will be executed with relevant values. Command deterministic function evaluation will occur prior to pushdown; however, multiple occurrences of the same command deterministic time function are not guaranteed to evaluate to the same value. - Nondeterministic - the result of function evaluation is fully nondeterministic. This category includes the
rand
function and UDFs marked as nondeterministic. Nondeterministic functions are delayed in evaluation until processing with a preference for pushdown. If the function is not pushed down, then it may be evaluated for every row in its execution context (for example, if the function is used in the select clause).