|
Query Builder Function Name (English Localized Name)
|
Description
|
Usage
|
Windchill Function Name (DatastoreFunction constant)
|
Oracle Function or Operator Name
|
SQLServer Function or Operator Name
|
Example
|
|
Absolute
|
A mathematical function that returns the absolute (positive) value of the specified numeric expression.
|
Takes a single numeric argument expression.
|
ABS
|
ABS
|
ABS
|
Absolute(-15) returns 15
|
|
Add
|
A mathematical function that returns the sum of all specified numeric expressions.
|
Takes two or more numeric argument expressions.
|
ADD
|
"+"
|
"+"
|
Add(5, 3, 10) returns 18
|
|
Average
|
An aggregate function that returns the average value of the numeric expression for all rows in the results set.
|
Takes numeric expression as its single argument.
|
AVERAGE
|
AVG
|
AVG
|
|
|
Bit-wise And
|
A mathematical function that performs the bit-wise AND of two numeric expressions.
|
Takes two numeric expressions as parameters.
|
BITAND
|
BITAND
|
"&"
|
Bit And(5, 4) returns 4
|
|
Ceiling
|
A mathematical function that returns the smallest integer greater than or equal to the specified numeric expression.
|
Takes a single numeric argument expression.
|
CEIL
|
CEIL
|
CEILING
|
Ceiling(15.7) returns 16
|
|
Coalesce
|
This function returns the first non-null argument expression (in order)
|
Takes one or more argument expressions
|
COALESCE
|
COALESCE
|
COALESCE
|
Coalesce(null, ‘abc’) returns ‘abc’
|
|
Concatenate
|
A string function that returns the concatenation of all specified string expressions.
|
Takes one or more string argument expressions.
|
CONCAT
|
"||"
|
"+"
|
Concatenate('Wind', 'chill') returns 'Windchill'
|
|
Convert
|
A function that converts expressions of one data type to another data type.
|
Takes a data type as the first parameter, a general expression as the second parameter, and a numeric style specification as an optional third parameter.
|
CONVERT
|
Not Supported
|
CONVERT
|
Convert(varchar(1), 7) returns '7'
|
|
Count
|
An aggregate function that returns the number of all rows in the result set.
|
Takes a general expression as its single argument.
|
COUNT
|
COUNT
|
COUNT
|
|
|
Date Difference
|
A date function that returns the number of date and time boundaries crossed between two specified dates.
|
Takes a date type as the first parameter, a start date expression as the second parameter, and an end date expression as the third parameter.
|
DATEDIFF
|
Not Supported
|
DATEDIFF
|
Date Difference(week, Convert(datetime, '1/4/2006'), Convert(datetime, '1/11/2006')) returns 1
|
|
Decode
|
A general function that provides an equivalent behavior to a programmatic "case" statement.
|
Takes a general expression target as the first parameter, any number of search, value general expressions, and an optional default value general expression as the last parameter.
|
DECODE
|
DECODE
|
Not Supported
|
Decode (2, 1, 'Chicago', 2, 'New York', 3, 'New Jersey', 'Non domestic') returns 'New York'
|
|
Divide
|
A mathematical function that divides the first numeric expressions with the second numeric expression.
|
Takes two numeric expressions as parameters.
|
DIVIDE
|
"/"
|
"/"
|
Divide(12, 3) returns 4
|
|
Floor
|
A mathematical function that returns largest integer equal to or less than the specified numeric expression.
|
Takes a single numeric argument expression.
|
FLOOR
|
FLOOR
|
FLOOR
|
Floor(15.7) returns 15
|
|
Get Day
|
This conversion function returns the day portion of the specified time stamp expression.
|
Takes a single time stamp argument expression.
|
GET_DAY
|
TO_CHAR
|
DATEPART
|
Get Day(To Date('1/4/2006 10:20:30')) returns 4
|
|
Get Hours
|
This conversion function returns the hours portion of the specified time stamp expression.
|
Takes a single time stamp argument expression.
|
GET_HOURS
|
TO_CHAR
|
DATEPART
|
Get Day(To Date('1/4/2006 10:20:30')) returns 10
|
|
Get Minutes
|
This conversion function returns the minutes portion of the specified time stamp expression.
|
Takes a single time stamp argument expression.
|
GET_MINUTES
|
TO_CHAR
|
DATEPART
|
Get Day(To Date('1/4/2006 10:20:30')) returns 20
|
|
Get Month
|
This conversion function returns the month portion of the specified time stamp expression.
|
Takes a single time stamp argument expression.
|
GET_MONTH
|
TO_CHAR
|
DATEPART
|
Get Day(To Date('1/4/2006 10:20:30')) returns 1
|
|
Get Seconds
|
This conversion function returns the seconds portion of the specified time stamp expression.
|
Takes a single time stamp argument expression.
|
GET_SECONDS
|
TO_CHAR
|
DATEPART
|
Get Day(To Date('1/4/2006 10:20:30')) returns 30
|
|
Get Year
|
This conversion function returns the year portion of the specified time stamp expression.
|
Takes a single time stamp argument expression.
|
GET_YEAR
|
TO_CHAR
|
DATEPART
|
Get Day(To Date('1/4/2006 10:20:30')) returns 2006
|
|
Hash
|
This function computes a hash value number.
|
Takes a single argument expression.
|
HASH
|
ORA_HASH
|
CHECKSUM
|
Hash(‘abc’) returns 123456
|
|
In String
|
This string function returns the starting position of the search string expression in a target string expression.
|
Takes a search string expression as the first parameter, a target string expression as the second parameter, and a starting position numeric expression as the third parameter.
|
IN_STRING
|
INSTR
|
CHARINDEX
|
In String('CORPORATE FLOOR','OR', 3) returns 5
|
|
Left Pad (with spaces)
|
This function returns a string expression, left-padded to the specified length with spaces.
|
Takes a first argument string expression and a second argument number expression.
|
LPAD
|
LPAD
|
Not Supported
|
Left Pad(‘abc’, 7) returns ‘ abc’
|
|
Length
|
This string function returns the length of the target string expression.
|
Takes a single string expression.
|
LENGTH
|
LENGTH
|
LEN
|
Length('Windchill') returns 9
|
|
Log
|
This mathematical function returns the logarithm of the base numeric expression of the specified numeric expression.
|
Takes a base numeric expression as the first parameter and a numeric expression as the second parameter.
|
LOG
|
LOG
|
Not Supported
|
Log(10, 100) returns 2
|
|
Lower
|
This string function returns the lower case value of the target string expression.
|
Takes a single string expression.
|
LOWER
|
LOWER
|
LOWER
|
Lower('Windchill') returns 'windchill'
|
|
Left Trim
|
This string function returns the target string expression after it removes leading blanks.
|
Takes a single string expression.
|
LTRIM
|
LTRIM
|
LTRIM
|
Left Trim(' Windchill') returns 'Windchill'
|
|
Maximum
|
An aggregate function that returns the maximum value of the numeric expression for all rows in the results set.
|
Takes numeric expression as its single argument.
|
MAXIMUM
|
MAX
|
MAX
|
|
|
Minimum
|
An aggregate function that returns the minimum value of the numeric expression for all rows in the results set.
|
Takes numeric expression as its single argument.
|
MINIMUM
|
MIN
|
MIN
|
|
|
Modulus
|
A mathematical function that returns the remainder of the second numeric expression divided by the first numeric expression.
|
Takes two numeric expressions as its arguments.
|
MOD
|
MOD
|
"%"
|
Modulus(11,4) returns 3
|
|
Multiply
|
A mathematical function that returns the product of all specified numeric expressions.
|
Takes two or more numeric argument expressions.
|
MULTIPLY
|
"*"
|
"*"
|
Multiply(3, 2) returns 6
|
|
NLS Sort
|
This string function returns a string of bytes used for locale specific sorting.
|
Takes a single string expression.
|
NLSSORT
|
NLSSORT
|
Not Supported
|
|
|
Null If
|
This function returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.
|
Takes two argument expressions.
|
NULL_IF
|
NULLIF
|
NULLIF
|
Null If(‘xyz’,’xyz’) returns null Null If (‘abc’,’xyz’) returns ‘abc’
|
|
Null Value
|
This function checks the target expression and returns the default expression if it is null. Otherwise, it returns the target expression.
|
Takes a target expression as the first parameter and a default expression of the same type as the second parameter.
|
NULL_VALUE
|
NVL
|
ISNULL
|
Null Value(NULL, 0) returns 0
|
|
Prefix
|
This string function returns the first 200 characters of the string argument expression or the string argument expression if its length is 200 or less characters.
|
Takes a single string argument expression.
|
PREFIX
|
Windchill Database Function: WTPrefixPK.Prefix
|
Windchill Database Function: WtPK_prefix
|
Prefix(‘abc’) returns ‘abc’
|
|
Prefix (400 characters)
|
This string function returns the first 400 characters of the string argument expression or the string argument expression if its length is 400 or less characters.
|
Takes a single string argument expression.
|
PREFIX
|
Windchill Database Function: WTPrefixPK.Prefix400
|
Windchill Database Function: WtPK_prefix400
|
Prefix(‘abc’) returns ‘abc’
|
|
Reverse
|
This string function returns the reverse order of the string argument expression.
|
Takes a single string argument expression.
|
REVERSE
|
Windchill Database Function: WTReversePK.reverseChars
|
REVERSE
|
Reverse(‘abc’) returns ‘cba’
|
|
Round
|
A mathematical function that returns the numeric expression, rounded to the specified length or precision.
|
Takes a numeric expression as the first parameter and a precision numeric expression as the second parameter.
|
ROUND
|
ROUND
|
ROUND
|
Round(15.193,1) returns 15.2
|
|
Right Trim
|
This string function returns the target string expression after it removes trailing blanks.
|
Takes a single string expression.
|
RTRIM
|
RTRIM
|
RTRIM
|
Right Trim('Windchill ') returns 'Windchill'
|
|
Sign
|
A mathematical function that returns the sign numeric expression (-1, 0, or 1) of the specified numeric expression.
|
Takes a single numeric expression.
|
SIGN
|
SIGN
|
SIGN
|
Sign(-5) returns -1
|
|
Soundex
|
A string function that returns the phonetic representation string expression of the specified string expression.
|
Takes a single string expression.
|
SOUNDEX
|
SOUNDEX
|
SOUNDEX
|
|
|
Standard Deviation
|
An aggregate function that returns the sample standard deviation of the numeric expression for all rows in the results set.
|
Takes a single numeric expression.
|
STDDEV
|
STDDEV
|
Not Supported
|
|
|
Subtract
|
A mathematical function that subtracts the second numeric expression from the first numeric expression.
|
Takes two numeric expressions as parameters.
|
SUBTRACT
|
"-"
|
"-"
|
Subtract(10, 4) returns 6
|
|
Sub String
|
This string function returns the portion of string expression specified by the start and length numeric expressions.
|
Takes a string expression as the first parameter, a start numeric expression as the second parameter, and a length numeric expression as the third parameter.
|
SUB_STRING
|
SUBSTR
|
SUBSTRING
|
Sub String('Windchill', 3, 2) returns 'nd'
|
|
Sum
|
An aggregate function that returns the summation of the numeric expression for all rows in the results set.
|
Takes a single numeric expression.
|
SUM
|
SUM
|
SUM
|
|
|
System Date
|
This time stamp function returns the current date and time set for the operating system on which the database resides.
|
Takes no arguments.
|
SYSDATE
|
SYSDATE
|
GETDATE
|
|
|
Time Difference(days)
|
This time stamp function returns the difference in days between the first time stamp expression and the second time stamp expression.
|
Takes two time stamp expressions as parameters.
|
TIME_DIFFERENCE_IN_DAY
|
"-"
|
"/"
|
Time Difference(days)(To Date('1/4/2006 10:00:00'), To Date('1/2/2006 10:00:00')) returns 2
|
|
Time Difference(hours)
|
This time stamp function returns the difference in days between the first time stamp expression and the second time stamp expression.
|
Takes two time stamp expressions as parameters.
|
TIME_DIFFERENCE_IN_HOUR
|
"-"
|
"/"
|
Time Difference(days)(To Date('1/4/2006 10:00:00'), To Date('1/2/2006 10:00:00')) returns 48
|
|
To Character
|
This conversion function returns the string expression for the specified expression.
|
Takes a single expression.
|
TO_CHAR
|
TO_CHAR
|
CONVERT
|
To Character(12) returns '12'
|
|
To Date
|
This conversion function returns the time stamp expression for the specified expression.
|
Takes a single expression.
|
TO_DATE
|
TO_DATE
|
CONVERT
|
|
|
To Number
|
This conversion function returns the numeric expression for the specified expression.
|
Takes a single expression.
|
TO_NUMBER
|
TO_NUMBER
|
CONVERT
|
To Number('12') returns 12
|
|
Truncate
|
A mathematical function that returns the numeric expression, truncated to the specified number of decimal places.
|
Takes a numeric expression as the first parameter and a precision numeric expression as the second parameter.
|
TRUNCATE
|
TRUNC
|
Not Supported
|
Truncate(15.79,1) returns 15.7
|
|
Upper
|
This string function returns the upper case value of the target string expression.
|
Takes a single string expression.
|
UPPER
|
UPPER
|
UPPER
|
Upper('Windchill') returns 'WINDCHILL'
|
|
XML Exists
|
This function returns a Boolean true value if the traversal of the XML first argument expression using the second argument expression as an xpath query results in any nodes.
|
Takes an XMLType first argument expression and a string second argument expression.
|
XML_EXISTS
|
EXISTSNODE
|
EXIST
|
|
|
XML Value
|
This function returns a string node value after the traversal of the XML first argument expression using the second argument expression as an xpath query.
|
Takes an XMLType first argument expression and a string second argument expression.
|
XML_VALUE
|
XMLQUERY
|
VALUE
|