Description on Building SQL Query Functions

Introduction

By constructing SQL query expressions, you can find out specific data from existed data. An expression consists of some operators and common functions. Common function includes aggregation functions, mathematical functions, character functions and data functions. For datasources in different engines, applicable functions are different. Following contents detail supportive situations.

Operators

The operators include mathematical, comparison, logical and some other operators.

Operator	Description	Example
Mathematical Operators
+	Addition	RENT + UTILITIES <= 800
-	Subtraction	POP_2002 - POP_1994
*	Multiplication	DENSITY * AREA > 5000000
/	Division	VALUE / POP = 50000
\	Quotient	VALUE / POP = 100
%	Modulus	VALUE1 % VALUE2
Mod	Modulus	VALUE1 Mod VALUE2
Comparison
=	Equal to	CODE = 100
>	Greater than	POP_2002 > 5000000
<	Less than	INDUST_GROWTH < 0
>=	Greater than or equal to	RENT + UTILITIES >= 800
<=	Less than or equal to	RENT + UTILITIES <= 800
<>	Not equal to	VALUE <> 100
!	Logical NOT, united with">", "<" as !< (not less than) and !> (not greater than)	VALUE !> 100
Logical
AND	Combines two Boolean expressions and returns TRUE if both expressions are TRUE.	CODE = 100 AND VALUE > 20000
NOT	Negates a Boolean input.	NOT IsBACHELOR
OR	Combines two Boolean expressions and returns TRUE if at least one expression is TRUE.	SALES > 20000 OR ORDERS > 20000
IN	Determines if the operand matches a certain value in the expression list.	PROVINCE In ("GUANGDONG")
Between	Determines whether an expression is within a certain range. It is often used together with AND.	SALES Between 4095 AND 20000
Like	Determines whether the given string matches a specified pattern.	COUNTRY Like "CANADA"
Others
Is NULL	Determines whether or not a given expression is NULL.	CONTINENT Is NULL
Is TRUE	Determines whether or not a given expression is TRUE.	Value < 0 Is TRUE
Is FALSE	Determines whether or not a given expression is FALSE.	Value > 0 Is FALSE
#	Matches a single character of any value.	World.SmID like '1#3'
´	Used for the input of char or date fields.	Universities.UNIVERSITY like '*University'
ˆ	Logical Exclusive XOR	World.SmID ^ World.COLOR_MAP >0
·	Used for specifying a field of a dataset	World.COUNTRY
*	Matches multiple characters. You can insert the asterisk (*) at any position in a string.	Universities.UNIVERSITY like '*University'

Aggregation Function

Aggregate functions perform a calculation on a set of values and return a single value. Except for the function COUNT, aggregate functions ignore null values. Aggregate functions are often used with the GROUP BY clause of the SELECT statement. All aggregate functions are deterministic; they return the same value any time they are called with a given set of input values. Except for Count, aggregate functions can not be applied to data in text type. The expressions of aggregate functions can be the names of fields of datasets in SuperMap.

Tab: Aggregation Function

Aggregation Function	Description	Supportive Engines					Example
Aggregation Function	Description	UDB/UDBX	OraclePlus	SQLPlus	PostgreSQL	DB2	Example
Avg	AVG (expression) Returns the average of the values in a group. Null values are ignored.	v	v	v	v	v	Avg(World.POP_1994)
Count	COUNT (expression) Returns count of items in a group.	v	v	v	v	v	Count(*)
Max	Max (expression) Returns the maximum value in a set of values.	v	v	v	v	v	Max(World.POP_1994)
Min	Min (expression) Returns the minimum value in a set of values.	v	v	v	v	v	Min(World.POP_1994)
Sum	Sum (expression) Returns the sum of a set of values.	v	v	v	v	v	Sum(World.POP_1994)
Stdev	Stdev (expression) Returns the statistical standard deviation of sampling in the given expression. For Oracle datasources, the function is STDDEV.	v	v(Stddev)	v	v(Stddev)	v	Stdev(World.POP_1994)
Stdevp	Stdevp (expression) Returns the statistical standard deviation of all values in the given expression.		v(Stddev_pop)	v	v(Stddev_pop)	v	Stdevp(World.POP_1994)
Var	Var (expression) Returns the statistical variance of sampling in the given expression. For Oracle datasources, the function is VARIANCE.	v	v(Variance)	v	v(Variance)	v
Varp	Varp (expression) Returns the statistical variance of all values in the given expression.		v(Var_pop)	v	v(Var_pop)	v	Varp(World.POP_1994)

Mathematical Functions

Mathematical functions perform operations on mathematic expression and return the results. The expressions of mathematical functions are often composed of fields of datasets or the expressions composed by these types of fields, such as Cos(Slope), Ceiling(Pop/Area), etc.

Mathematical Function	Description	Supportive Engines					Example
Mathematical Function	Description	UDB/UDBX	OraclePlus	SQLPlus	PostgreSQL	DB2	Example
Abs	Returns the absolute value of the expression. Applicable to fields (or expressions) in Short, Long, Single, or Double type.	v	v	v	v	v	Abs(World.SmID)
Acos	Returns the arccosine of the expression as an angle in radians ranging from -π to π. Applicable to fields or expressions in Single or Double type. The value of the expression ranges from -1 to 1. Not applicable to UDB datasources.	v	v	v	v	v	Acos(data)
Asin	Returns the arcsine of the expression as an angle in radians ranging from -π to π. Applicable to fields or expressions in Single or Double type. The value of the expression ranges from -1 to 1. Not applicable to UDB datasources.	v	v	v	v	v	Asin(data)
Atan	Returns the arctangent of the expression as an angle in radians. Function name is Atn for a SDB datasource.	v	v	v	v	v	Atan(data)
Ceiling	Returns the smallest integer greater than or equal to the expression. Applicable to fields (or expressions) in Short, Long, Single, or Double type. For Oracle datasources, the function is CEIL. Not applicable to UDB datasources.	v	v(CEIL)	v	v	v	Ceiling(data)
Cos	Returns the cosine of the expression as an angle in radians.	v	v	v	v	v	Cos(World.COLOR_MAP)
cot	Returns the cotangent of the expression as an angle in radians.	v	v	v	v	v	Cot(angle)
Degrees	Converts from radians to degrees.	v		v	v	v	Degrees(angle)
Exp	Returns a Double value containing e (the base of natural logarithms) raised to the specified power.	v	v	v	v	v	Exp(data)
Floor	Rounds the specified number down and returns the largest number that is less than or equal to the specified number.	v	v	v	v	v	Floor(23.45), Floor(-123.45)
Log	Returns the natural logarithm of the specified expression. Not applicable to UDB datasources. For Oracle datasources, the function is Log(n, m), representing the logarithm of m with base n.	v	v	v	v	v	Log(World.SmID), Log(n,m)
PI	The Pi function returns the mathematical constant called pi.	v		v	v		PI()*World.SMID as Test
Power	Returns the value of parameter 1 to the power of parameter 2. Not applicable to SDB datasources. Applicable to Oracle datasources.	v	v		v		Power(expression,2) as Area
Radians	Converts degrees to radians. Applicable to SQL datasources.	v	v	v	v	v	Radians(angle) as NewAngle
Rand	Returns a float value less than 1, but greater than or equal to zero.				v	v	Rand()
Round	Returns a number rounded to a specified number of decimal places. Parameter 1 and 2 respectively specify the number to round and the decimal places. The number nearest the specified value will be returned if you didn't specify the decimal places.	v	v	v	v	v	Round(World.SmArea,2), Round(World.SmArea)
Sign	Returns -1, 0, or +1 when the expression value is negative, zero, or positive respectively.	v	v	v	v	v	Sign(data)
Sin	Returns the sine of the expression value as an angle in radians.	v	v	v	v	v	Sin(World.COLOR_MAP)
Square	Returns the square of the expression value.			v	v	v	Square(Production)
Sqrt	Returns the square root of the expression value. For SDB datasources, the function is Sqrt.	v	v	v	v	v	Sqrt(Production)
Tan	Returns the tangent of the expression value.	v	v	v	v	v	Tan(World.COLOR_MAP)

String Functions

String functions are used to perform many types of manipulation on string data.

String Function	Description	Supportive Engines					Example
String Function	Description	UDB/UDBX	OraclePlus	SQLPlus	PostgreSQL	DB2	Example
Ascii	Returns the ASCII code value of the leftmost character of a character expression. For a SDB datasource, function name is asc. Not applicable to UDB datasources. Syntax: ASCII(character_expression)character_expression is an expression of the type text.		v	v		v	Ascii(String)
Char	Converts an int ASCII to a character. For SDB datasource, function name is Chr. Syntax: Char (integer_expression) integer_expression is an integer expression with value ranging from 0 to 255. NULL will be returned if the integer expression is not in this range.		v(Chr)	v	v(Chr)	v(Chr)	Char(data)
InStr	Returns the location of a substring in a string. Syntax: InSt([start_location],expr1,expr2) expr1 is the string to search. expr2 is the substring to search for in expr1. start_location is the position in string1 where the search will start. This argument is optional. If omitted, it defaults to 1.		v				InStr(1,World.CAPITAL,"jing"), InStr(World.CAPITAL,"jing")
Length	Returns the length of the specified string. If the specified string1 is NULL, then the function returns NULL. Syntax: Length(string_expression) string_expression is the string to return the length for. For SDB/SQL datasources, the function is Len(string_expression).	v	v	v(Len)	v	v	Lenth(World.CAPITAL)
Lower	Converts all letters in the specified string to lowercase. For SDB datasources, the function is LCase. Syntax: Lower (character_expression) character_expression is the string to convert to lowercase.	v	v	v	v	v	Lower(World.CAPITAL) as capital
Ltrim	Returns a string containing a copy of a specified string with no leading spaces. Syntax: Ltrim (character_expression) character_exprssion is the string to trim.	v	v	v	v	v	Ltrim(World.CAPITAL)
Replace	Replaces a sequence of characters in a string with another set of characters.	v	v
Reverse	Returns string converted in the reverse order. Syntax: Reverse>( character_expression) character_expression is the string to reverse.		v	v			Reverse(World.CAPITAL) as NewName
Rtrim	Returns a string containing a copy of a specified string with no trailing spaces. Syntax: Rtrim(character_expression) character_expression is any valid string expression.	v	v	v	v	v	Rtrim(World.CAPITAL)
Soundex	Returns a phonetic representation (the way it sounds) of a string. Syntax: Soundex (character_expression) character_expression the string whose phonetic value will be returned.		v	v			Soundex(word)
Space	Returns a string consisting of the specified number of spaces. If it is negative, NULL will be returned.			v			World.COUNTRY+Space(3)+World.CAPITAL as Countries_CAPITAL
Substr	Returns a part of text characters expression. Syntax: Substr(expression, start, length). expression is the expression of the string type that does not contain an aggregate function; start is an integer that specifies the starting position of the substring; length is an integer that specifies the length of the substring (the number of characters or bytes to return).	v	v(not for text)	v	v(or Substring)		Substr ( string, position, 1 )
Unicode	According to the Unicode standard, return the integer value of first character from the entered expression. Syntax: Unicode('ncharacter_expression').			v			Unicode(World.CAPITAL) as Unicode
Upper	Converts all letters in the specified string to uppercase. For SDB datasources, the function is UCase. Syntax: Upper(character_expression) character_expression is any valid string expression.	v	v	v	v	v	Upper(World.CAPITAL)

Date Functions

Date functions are used to get information on date and time. These scalar functions perform an operation on a date and time input value and return a string, numeric, or date and time value. The date functions can be used anywhere in the expression.

Date Function	Description	Supportive Engines					Example
Date Function	Description	UDB/UDBX	OraclePlus	SQLPlus	PostgreSQL	DB2	Example
Day	Returns an integer representing the day datepart of the specified date. It equals to DatePartt( "d" ,date). Applicable to date fields or expressions of SQL/SDB datasources. Syntax: DatePart( datepart ,date)) date is an expression of type date.			v		v	Day(World.GETDATE) as Day
GetDate	Gets current data and time in standard SQL Syntax.			v			GetDate()
GetUtcDate	Returns the date value representing the current UTC time (Universal Time Coordinate or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which SQL Server is running.			v			GetUtcDate()
Month	Returns an integer that represents the month part of a specified date. It equals to DatePart( "m" , date). Applicable to date fields or expressions of SQL/SDB datasources. Syntax: Month( date) date is a field or expression of date type.			v		v	Month(World.GETDATE) as Month
Year	Returns an integer that represents the year part of a specified date. It equals to DatePartdate. Applicable to date fields or expressions of SQL/SDB datasources. Syntax: Year(date) date is a date field or expression.			v		v	Year(World.GETDATE) as Year
to_date	For a string date, if you want to use it to construct a query condition, its format is fixed and you need to input it to the function to_data(). Format: to_date(2018-5-11 14:28:00).		v				Province_R.DataDate > to_date(2019-11-07 00:00:00)

Tab: datepart values