Build SQL Query Functions
Common functions for constructing SQL queries include aggregate, math, string, and date functions. The SQL Query dialog provides function help for quickly viewing function definitions, selecting appropriate functions, and inputting them into expressions. The function help interface is shown below:
- Search Box: Allows searching for specific strings in function lists and help descriptions, with highlighted results.
- Function List: Organized by recently used, operator, aggregate, math, string, and date groups for quick navigation.
- Help Description: Displays definitions, syntax, parameters, and usage examples for selected functions.
In all subsequent tables, parenthetical notations in the "Supported Engines" column indicate function writing formats for specific database engines. For example, "√(Stddev)" denotes the function should be written as "Stddev" in OraclePlus and PostgreSQL databases.
Operator
Operators are used to construct SQL query conditions, including common mathematical operators, logical operators, and some special operators.
| Operator | Meaning | Example |
|---|---|---|
| Arithmetic Operators | ||
| + | Addition | Dataset.RENT + Dataset.UTILITIES <= 800 |
| - | Subtraction | Dataset.POP_2019 - Dataset.POP_2010 |
| * | Multiplication | Dataset.DENSITY * Dataset.AREA > 5000000 |
| / | Division | Dataset.VALUE / Dataset.POP = 50000 |
| \ | Integer Division | Dataset.VALUE \ Dataset.POP > 100 |
| % | Modulo | Dataset.VALUE1 % Dataset.VALUE2 |
| Mod | Modulo | Dataset.VALUE1 Mod Dataset.VALUE2 |
| Comparison Operators | ||
| = | Equal | Dataset.CODE = 100 |
| > | Greater Than | Dataset.POP_2002 > 5000000 |
| < | Less Than | Dataset.INDUST_GROWTH < 0 |
| >= | No Less Than | Dataset.RENT + Dataset.UTILITIES >= 800 |
| <= | No More Than | Dataset.RENT + Dataset.UTILITIES <= 800 |
| <> | Not Equal | Dataset.VALUE <> 100 |
| ! | Negation, used with ">" or "<" as !< (Not Less Than) or !> (Not Greater Than) | Dataset.VALUE !> 100 |
| Logical Operators | ||
| AND | Combines two Boolean expressions and returns TRUE when both are TRUE. | Dataset.CODE = 100 AND Dataset.VALUE > 20000 |
| NOT | Negates any other Boolean operator's value. | NOT IsBACHELOR |
| OR | Combines two conditions, returns TRUE if either expression is TRUE. | Dataset.SALES > 20000 OR Dataset.ORDERS > 20000 |
| IN | Returns TRUE if the operand matches any value in the expression list. | Dataset.PROVINCE In ("GUANGDONG","BeiJing") |
| Between | Determines if an expression falls within a specified range (usually used with AND). | Dataset.SALES Between 4095 AND 20000 |
| Like | Determines if results exactly match a specified pattern. | Dataset.COUNTRY Like "CANADA" |
| Other | ||
| Is NULL | Determines if an expression is NULL (primarily for text fields). | Dataset.CONTINENT Is NULL |
| Is TRUE | Determines if an expression is TRUE (for Boolean fields). | Dataset.Value < 0 Is TRUE |
| Is FALSE | Determines if an expression is FALSE (for Boolean fields). | Dataset.Value > 0 Is FALSE |
| # | Matches any single numeric character. | Dataset.SmID like '1#3' |
| ´ | Used for inputting character/date fields. | Dataset.COUNTRY like '*国' |
| ˆ | Exponentiation (a^b). Not supported for UDB/UDBX, Oracle, SQLPlus, or MySQL; applicable to PostGIS. | Dataset.SmID > 2^3 |
| · | Used for manual input of select fields. | Dataset.COUNTRY |
| * | Wildcard matching any number of characters. | Dataset.COUNTRY like '*国' |
Aggregate
Aggregate functions perform calculations on sets of values in database queries, summarizing multiple rows/columns into single results.
Except COUNT, all aggregates ignore NULLs by default and cannot be applied to text data types.
In SuperMap iDesktopX, aggregate parameters typically follow "Dataset.Field" format (e.g., Avg(Dataset.POP_2014)).
To use aggregates in the SQL Query dialog, directly enter expressions in result fields (e.g., "Max(SmArea)" to query maximum SmArea values).
| Aggregate | Name | Description | Supported Engines | Example | |||
| UDB/UDBX | OraclePlus | SQLPlus | PostgreSQL | ||||
| Avg | Average | Returns arithmetic mean of specified field values (ignores NULLs). | √ | √(Stddev) | √ | √ | Avg(Dataset.POP_2014) |
| Count | Count | Returns number of records per group. | √ | √ | √ | √ | Count(*) |
| Max | Maximum | Returns maximum value in specified field. | √ | √ | √ | √ | Max(Dataset.POP_2014) |
| Min | Minimum | Returns minimum value in specified field. | √ | √ | √ | √ | Min(Dataset.POP_2014) |
| Sum | Sum | Returns total sum of specified field values. | √ | √ | √ | √ | Sum(Dataset.POP_2014) |
| Stdev | Sample Standard Deviation | Estimates standard deviation from sample data (STDDEV in Oracle). | √ | √(Stddev) | √ | √(Stddev) | Stdev(Dataset.POP_2014) |
| Stdevp | Population Standard Deviation | Estimates standard deviation from entire population. | × | √(Stddev_pop) | √ | √(Stddev_pop) | Stdevp(Dataset.POP_2014) |
| Var | Sample Variance | Estimates variance from sample data (VARIANCE in Oracle). | √ | √(Variance) | √ | √(Variance) | Var(Dataset.POP_2014) |
| Varp | Population Variance | Estimates variance from entire population. | × | √(Var_pop) | √ | √(Var_pop) | Varp(Dataset.POP_2014) |
Math
Math functions process input parameters through specific mathematical operations.
Expressions in SuperMap iDesktopX typically use field names (e.g., Cos(Slope)).
| Function | Description | Supported Engines | Example | |||
| UDB/UDBX | OraclePlus | SQLPlus | PostgreSQL | |||
| Abs | Returns absolute value of specified number. | √ | √ | √ | √ | Abs(Dataset.Temperature) |
| Acos | Returns arc cosine in radians (-π to π) for float expressions between -1 and 1. | √ | √ | √ | √ | Acos(data) |
| Asin | Returns arc sine in radians (-π to π) for float expressions between -1 and 1. | √ | √ | √ | √ | Asin(data) |
| Atan | Returns arc tangent in radians. | √ | √ | √ | √ | Atan(data) |
| Ceiling | Returns smallest integer ≥ specified value (CEIL in Oracle). | √ | √(CEIL) | √ | √ | Ceiling(data) |
| Cos | Returns cosine of angle in radians. | √ | √ | √ | √ | Cos(Dataset.Radian) |
| cot | Returns cotangent of specified angle. | √ | √ | √ | √ | Cot(angle) |
| Degrees | Converts radians to degrees. | √ | × | √ | √ | Degrees(angle) |
| Exp | Returns e raised to specified power. | √ | √ | √ | √ | Exp(data) |
| Floor | Returns largest integer ≤ specified value. | √ | √ | √ | √ | Floor(23.45) |
| Log | Returns natural logarithm (Log(n,m) in Oracle). | √ | √ | √ | √ | Log(Dataset.SmID) |
| PI | Returns π constant. | √ | √ | √ | PI()*Dataset.SMID | |
| Power | Returns value raised to specified power. | √ | √ | √ | Power(expression,2) | |
| Radians | Converts degrees to radians (floored). | √ | √ | √ | √ | Radians(angle) |
| Rand | Returns random float between 0-1. | √ | Rand() | |||
| Round | Rounds number to specified decimals (default: nearest integer). | √ | √ | √ | √ | Round(Dataset.SmArea,2) |
| Sign | Returns number's sign (+1, 0, -1). | √ | √ | √ | √ | Sign(data) |
| Sin | Returns sine of angle in radians. | √ | √ | √ | √ | Sin(Dataset.COLOR_MAP) |
| Square | Returns square of expression. | × | × | √ | √ | Square(Production) |
| Sqrt | Returns square root of expression. | √ | √ | √ | √ | Sqrt(Production) |
| Tan | Returns tangent of input expression. | √ | √ | √ | √ | Tan(Dataset.COLOR_MAP) |
String
String functions manipulate character/binary strings and return processed values.
| Function | Description | Supported Engines | Example | |||||
| UDB/UDBX | OraclePlus | SQLPlus | PostgreSQL | MySQL | ||||
| ASCII | Returns ASCII code of leftmost character. | √ | √ | √ | √ | Ascii(String) | ||
| Char | Converts ASCII code to character (Chr in Oracle/PostgreSQL). | √ | √(Chr) | √ | √(Chr) | √ | Char(data) | |
| InStr | Returns starting position of substring. | × | √ | × | × | × | InStr(Dataset.CAPITAL,"京") | |
| Length | Returns string length (Len in SQLPlus). | √ | √ | √(Len) | √ | √ | Length(Dataset.CAPITAL) | |
| Lower | Converts to lowercase. | √ | √ | √ | √ | √ | Lower(Dataset.CAPITAL) | |
| Ltrim | Removes leading spaces. | √ | √ | √ | √ | √ | Ltrim(Dataset.CAPITAL) | |
| Replace | Replaces all occurrences of substring. | √ | √ | × | × | × | Replace(Dataset.CAPITAL,"尔","而") | |
| Reverse | Reverses string (Chinese may display incorrectly in Oracle). | × | √ | √ | × | √ | Reverse(Dataset.CAPITAL) | |
| Rtrim | Removes trailing spaces. | √ | √ | √ | √ | √ | Rtrim(Dataset.CAPITAL) | |
| Soundex | Returns 4-character phonetic code. | × | √ | √ | × | × | Soundex(word) | |
| Space | Generates specified number of spaces. | × | × | √ | × | × | Dataset.COUNTRY+Space(3) | |
| Substr | Extracts substring (Substring in PostgreSQL). | √ | √ | √ | √ | Substr(string, position, 1) | ||
| Unicode | Returns Unicode value of first character. | × | × | √ | × | × | Unicode(Dataset.CAPITAL) | |
| Upper | Converts to uppercase. | √ | √ | √ | √ | √ | Upper(Dataset.CAPITAL) | |
| Left | Returns leftmost n characters. | × | × | × | √ | √ | Left("Hello",2) | |
| Right | Returns rightmost n characters. | × | × | × | √ | √ | Right("Hello",2) | |
Date
Date functions retrieve temporal information (date-type fields only).
| Function | Description | Supported Engines | Example | |||
| UDB/UDBX | OraclePlus | SQLPlus | MySQL | |||
| Day | Returns day portion of date (equivalent to Datepart('d',data)). | × | × | √ | √ | Day(Dataset.GETDATE) |
| GetDate | Returns current system datetime. | × | × | √ | × | GetDate() |
| GetUtcDate | Returns current UTC datetime. | × | × | √ | × | GetUtcDate() |
| Hour | Extracts hour from datetime. | × | × | √ | √ | Hour("2023-05-20 14:25:33") |
| Minute | Extracts minute from datetime. | × | × | √ | √ | Minute("2023-05-20 14:25:33") |
| Month | Returns month portion (equivalent to DatePart("m",date)). | × | × | √ | √ | Month(Dataset.GETDATE) |
| Second | Extracts second from datetime. | × | × | √ | √ | Second("2023-05-20 14:25:33") |
| Year | Returns year portion (equivalent to DatePart("yyyy",date)). | × | × | √ | √ | Year(Dataset.GETDATE) |
| to_date | Formats string datetime values for queries. | √ | √ | × | × | Province_R.DataDate > to_date(2019-11-07 00:00:00) |
Note:For UDB/UDBX data sources, use "yy-mm-dd hh:mm:ss" format (e.g., NewLine.data > '2022-01-01 00:00:00').
Datepart Parameters
| Setting | Description |
| yyyy | Year |
| q | Quarter |
| m | Month |
| y | Day of Year |
| d | Day |
| w | Weekday |
| ww | Week |
| h | Hour |
| n | Minute |
| s | Second |
Related Topics
Constructing SQL Expression Queries