Building SQL Query
Instructions for use
SQL Query queries the data that meets specific conditions from the existing data by building a query function. The condition here is to query the Dataset Properties through operators and Common Functions. Common Functions that build query statements include Aggregate, Math, String, and Date. For Datasource of different engines, the application and usage of different functions are different, and the support of SQL Query function for different engines is also given here.
Operator
Operator is used to construct SQL Query conditions, including common mathematical operators, logical operators, and some special operators.
| Operator | Meaning | Examples |
|---|---|---|
| Arithmetic Operator | ||
| ＋ | 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 |
| \ | Take the quotient | Dataset.VALUE \ Dataset.POP > 100 |
| % | Take a mold | Dataset.VALUE1 % Dataset.VALUE2 |
| Mod | Take a mold | Dataset.VALUE1 Mod Dataset.VALUE2 |
| Comparison Operator | ||
| = | Equal | Dataset.CODE = 100 |
| > | Bigger than | Dataset.POP_2002 > 5000000 |
| < | Less than | Dataset.INDUST_GROWTH < 0 |
| >= | Is no less than | Dataset.RENT + Dataset.UTILITIES >= 800 |
| <= | Is no more than | Dataset.RENT + Dataset.UTILITIES <= 800 |
| <> | Not equal to | Dataset.VALUE <> 100 |
| ! | Not, combined with ">" and "<" to form! < (not less than),! > (not greater than) | Dataset.VALUE ! > 100 |
| Logical operator | ||
| AND | Concatenates two Boolean expressions and returns TRUE if both are TRUE | Dataset.CODE = 100 AND Dataset.VALUE > 20000 |
| NOT | Inverts the value of any other Boolean Operation character | NOT IsBACHELOR |
| OR | Combine two conditions. If one of the two Boolean expressions is TRUE, then it is TRUE. | Dataset.SALES > 20000 OR Dataset.ORDERS > 20000 |
| IN | TRUE if the operand is equal to one of the list of expressions | Dataset.PROVINCE In ("GUANGDONG","BeiJing") |
| Between | TRUE if an expression is determined to be within a certain range. Typically used with AND | Dataset.SALES Between 4095 AND 20000 |
| Like | Determines that the given string exactly matches the specified pattern | Dataset.COUNTRY Like "CANADA" |
| Other | ||
| Is NULL | Determines whether an expression is NULL, primarily a text field | Dataset.CONTINENT Is NULL |
| Is TRUE | Determines that an expression is True, primarily a Boolean field | Dataset.Value < 0 Is TRUE |
| Is FALSE | Determines that an expression, primarily a Boolean field, is False | Dataset.Value > 0 Is FALSE |
| # | Match any single numeric character. | Dataset.SmID like '1#3' |
| ´ | It is used for the input of character type field, date type field, etc. | Dataset. COUNTRY like '* Country |
| ˆ | Xor | Dataset.SmID ^ Dataset.COLOR_MAP >0 |
| · | Used when manually entering Select Fields. | Dataset.COUNTRY |
| * | Matches any number of characters. You can use an asterisk (*) anywhere in the string. | Dataset. COUNTRY like '* Country |
Aggregate
Aggregate performs a calculation on a set of values and yields a single value. Aggregate ignores null values, except for the COUNT function. Aggregate is often used with the GROUP BY clause of a SELECT statement. All Aggregates are deterministic. Any time you call them with a given set of input values, you get the same value. With the exception of the COUNT function, no other Aggregate can be used with a literal Data Type. In SuperMap, the expression in Aggregate usually uses the field name of the property table. Aggregate is only available for Tabular Dataset.
| Aggregate | Chinese Name | Meaning | Supported engines | Examples | |||
| UDB/UDBX | OraclePlus | SQLPlus | PostgreSQL | ||||
| Avg | Average (mean) | AVG (expression) Returns the arithmetic mean of a set of values contained in the Specify Field (or expression) of the query. Null values are ignored. | √ | √ | √ | √ | Avg(Dataset.POP_2014) |
| Count | Counting | COUNT (expression) Returns the number of records in each group. | √ | √ | √ | √ | Count(*) |
| Max | Maximum value | Max (expression) Returns the maximum value of a set of values contained within the Specify Field (or expression) of the query. | √ | √ | √ | √ | Max(Dataset.POP_2014) |
| Min | Minimum value | Min (expression) Returns the minimum value of a set of values contained within the Specify Field (or expression) of the query. | √ | √ | √ | √ | Min(Dataset.POP_2014) |
| Sum | Summation (total) | Sum (expression) returns the total of a set of values contained in the Specify Field (or expression) of the query. | √ | √ | √ | √ | Sum(Dataset.POP_2014) |
| Stdev | Sample Standard Deviation (standard deviation) | Stdev (expression) Returns an estimate of the standard deviation of a sample from a population based on a set of values contained in the Specify Field (or expression) of the query. For Orcale Data source, the function name is STDDEV. | √ | √(Stddev) | √ | √(Stddev) | Stdev(Dataset.POP_2014) |
| Stdevp | Overall standard deviation | Stdevp (expression) Returns an estimate of the standard deviation of a sample from the population using a set of values contained in the Specify Field (or expression) of the query. | √(Stddev_pop) | √ | √(Stddev_pop) | Stdevp(Dataset.POP_2014) | |
| Var | Sample Variance | Var (expression) Returns an estimate of the variance of a sample of a population based on a set of values contained in the Specify Field (or expression) of the query. For Orcale Data source, the function name is VARIANCE. | √ | √(Variance) | √ | √(Variance) | |
| Varp | Population variance | Varp (expression) Returns an estimate of the variance of a population sampled from a set of values contained in the Specify Field (or expression) of the query. | √(Var_pop) | √ | √(Var_pop) | Varp(Dataset.POP_2014) | |
Math
Math Operates mathematically on a numeric expression and returns the result. The expression of Math in SuperMap generally uses the field name of the attribute table or its expression, such as Cos (Slope), Ceiling (Pop/Area), etc.
| Math | Meaning | Supported engines | Examples | |||
| UDB/UDBX | OraclePlus | SQLPlus | PostgreSQL | |||
| Abs | Returns the absolute value of the specified number, of the same type as the specified Data Type. Applies to short, long, single, double, currency and other types of fields (or expressions). | √ | √ | √ | √ | Abs(Dataset.Temperature) |
| Acos | Returns an angle in radians from -π to π, the cosine of which is the given float expression; also known as arccosine. Applies to fields (or expressions) of single or double type. The expression can take a value from -1 to 1, otherwise it is The query result is null. | √ | √ | √ | √ | Acos(data) |
| Asin | Returns the angle in radians, from -π to π, of which the sine is the given float expression; also known as arcsine. Applies to fields (or expressions) of single or double type. The expression can take a value from -1 to 1, otherwise it is The query result is null. | √ | √ | √ | √ | Asin(data) |
| Atan | Returns the angle, in radians, whose tangent is the given float expression; also known as arctangent. | √ | √ | √ | √ | Atan(data) |
| Ceiling | Returns the smallest integer in the numeric expression given as Greater than or equal to. Applies to short, long, single, double, currency and other types of fields (or expressions). For Oracle Datasource, the function name is CEIL. | √ | √(CEIL) | √ | √ | Ceiling(data) |
| Cos | Returns the trigonometric cosine of the given angle (in radians) in the given expression, as a double. | √ | √ | √ | √ | Cos(Dataset.Radian) |
| cot | Returns the trigonometric cotangent of the specified angle (in radians) in the given float expression. | √ | √ | √ | √ | Cot(angle) |
| Degrees | When given an angle in radians, returns the corresponding angle in degrees. | √ | √ | √ | Degrees(angle) | |
| Exp | Returns the exponential value to the base e (approximately 2. 71828182845905) of the given float expression. | √ | √ | √ | √ | Exp(data) |
| Floor | Returns the largest integer less than or equal to the given mathematical expression. | √ | √ | √ | √ | Floor(23.45)，Floor(-123.45) |
| Log | Returns the natural logarithm of the given mathematical expression. Not intended for use with UDB Datasource; alternatively, for Oracle Datasource, the function is of the form Log (n, m), which represents the logarithm to base n, m. | √ | √ | √ | √ | Log(Dataset.SmID)，Log(n,m) |
| PI | Returns the constant value of PI, function input is empty. | √ | √ | √ | PI()*Dataset.SMID as Test | |
| Power | Returns the value of the given expression multiplied by the specified power. The first argument specifies the numeric field (or expression), and the second argument specifies the power. | √ | √ | √ | Power(expression,2) as Area | |
| Radians | Returns the radian value for a degree value entered in a numeric expression, with the radian value rounded down. | √ | √ | √ | √ | Radians(angle) as NewAngle |
| Rand | Returns a random float value between 0 and 1. The function input is empty. | √ | Rand() | |||
| Round | Returns the number of the specified numeric field (or expression), rounded to the specified number of decimal places. The first parameter specifies the numeric field (or expression), and the second parameter specifies the number of decimal places. If no decimal places are specified, rounding to the nearest whole number is the default. | √ | √ | √ | √ | Round(Dataset.SmArea,2)，Round(Dataset.SmArea) |
| Sign | Returns positive and negative information for a given expression, including positive (labeled + 1), zero (labeled 0), or negative (labeled -1) signs. | √ | √ | √ | √ | Sign(data) |
| Sin | Returns the trigonometric sine of a given angle in radians, type double. | √ | √ | √ | √ | Sin(Dataset.COLOR_MAP) |
| Square | Returns the square of the given expression. | √ | √ | Square(Production) | ||
| Sqrt | Returns the square root of the given expression. | √ | √ | √ | √ | Sqrt(Production) |
| Tan | Returns the tangent of the input expression. | √ | √ | √ | √ | Tan(Dataset.COLOR_MAP) |
String
Strings are used to perform various operations on character and binary strings, and they return values that are typically required for operations on character data.
| String | Meaning | Supported engines | Examples | |||
| UDB/UDBX | OraclePlus | SQLPlus | PostgreSQL | |||
| Ascii | Returns the ASCII code value of the leftmost character in a character expression. Not intended for UDB/UDBX Datasource use. Format: ASCII (character _ expression) character _ expression is an expression composed of Text fields. |
√ | √ | Ascii(String) | ||
| Char | Converts the value of an int expression from ASCII to a character string. For SDB Datasource, the function name is Chr. Format: Charinteger _ expression) An expression whose integer _ expression is an integer between 0 and 255. If the integer expression is not in this range, a NULL value is returned. |
√(Chr) | √ | √(Chr) | Char(data) | |
| InStr | Returns the starting position of the specified expression in a string. Format: InSt ( start _ location, expr1, expr2) start _ location is the starting character location in expr1 when searching for expr2, where expr1 is a String expression containing the characters to look for. Expr2 is a field string expression that identifies the specified sequence to search for. When the starting character position is not set, the search starts from the first character by default. |
√ | InStr (1, Dataset. CAPITAL, "Jing"), InStr (Dataset. CAPITAL, "Jing") | |||
| Length | Returns the length (number of characters) of the given string expression, counting spaces. If the field is empty, the return value is null. Format: Length (string _ expression) string _ expression is the string expression to be evaluated. Len (string _ expression) for SQL Datasource. |
√ | √ | √(Len) | √ | Lenth(Dataset.CAPITAL) |
| Lower | Returns a character expression after uppercase character data is Converts to Lowercase data. Format: Lower (character _ expression) character _ expression is a Char expression. |
√ | √ | √ | √ | Lower(Dataset.CAPITAL) as capital |
| Ltrim | Removes the leading space from the Specify Field string expression and returns the string with no leading space. Format: Ltrim (character _ expression) The character _ exprssion is a Char expression. |
√ | √ | √ | √ | Ltrim(Dataset.CAPITAL) |
| Replace | Replaces all occurrences of the second given string expression in the first string expression with the third expression. Format: Replace ( 'string _ expr1', 'string _ expr2', 'string _ expr3') string _ expr1 is the string expression to be searched; string _ expr2 is the string expression to be searched; String _ expr3 is the substituted string expression. |
√ | √ | Replace (Dataset. CAPITAL, "er", "and") | ||
| Reverse | Returns the inverse of a character expression. When querying Oracle Datasource, if the Data Type is text type, Chinese characters will not be processed. If the Data Type is character type, garbled characters will appear when querying Chinese Character:. Format: Reverse (character _ expression) a character _ expression is an expression consisting of character data. |
√ (Chinese characters will be garbled) | √ | Reverse(Dataset.CAPITAL) as NewName | ||
| Rtrim | Strips all trailing spaces from a character expression and returns a string with no trailing spaces. Format: Rtrim (character _ expression) a character _ expression is an expression consisting of character data. |
√ | √ | √ | √ | Rtrim(Dataset.CAPITAL) |
| Soundex | Returns a four-character code (SOUNDEX) to evaluate the similarity of two strings. Format: Soundex (character _ expression) character _ expression is an alphanumeric expression of character data. |
√ | √ | Soundex(word) | ||
| Space | Generates a string of the specified number of spaces. If the number specified is negative, an empty string is returned. | √ | Dataset.COUNTRY+Space(3)+Dataset.CAPITAL as World_CAPITAL | |||
| Substr | Returns a portion of a text character expression. Format: Substr (expression, start, length). Expression is a string expression that does not contain Aggregate, start is an integer that specifies the start position of the substring, and length is an integer that specifies the length of the substring (the number of characters or bytes to be returned). |
√ | √ (Text not applicable) | √ | √ (or Substring) | Substr ( string, position, 1 ) |
| Unicode | Returns the integer value of the first character of the input expression, as defined by the Unicode standard. Format: Unicode ( 'ncharacter _ expression'). |
√ | Unicode(Dataset.CAPITAL) as Unicode | |||
| Upper | Returns a character expression that converts a lowercase character Data Conversion to uppercase. Format: Upper (character _ expression) character _ expression is an expression consisting of character data. |
√ | √ | √ | √ | Upper(Dataset.CAPITAL) |
Date
Date is used to query information about a date. These functions apply to date type field values and perform arithmetic operations on those values. Date can be used anywhere an expression can be used.
| Date | Meaning | Supported engines | Examples | |||
| UDB/UDBX | OraclePlus | SQLPlus | PostgreSQL | |||
| Day | Returns an integer specifying the day part of a date. Equivalent to Datepart ('d ', data). Date field (or expression) for SQL Datasource. Format: Day (date) date is a date field (or expression). |
√ | Day(Dataset.GETDATE) as Day | |||
| GetDate | Returns the current system date and time in SQL standard format. Function input is empty. | √ | GetDate() | |||
| GetUtcDate | Returns a date value representing the current UTC time (Greenwich Mean Time). Function input is empty. The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer running SQL Server. | √ | GetUtcDate() | |||
| Month | Returns an integer specifying the month part of a date. Equivalent to Date Part ( "m", date). Date field (or expression) for SQL Datasource. Format: Month (date) date is a date field (or expression). |
√ | Month(Dataset.GETDATE) as Month | |||
| Year | Returns an integer specifying the year part of a date. Equivalent to Date (Partdate). Date field (or expression) for SQL Datasource. Format: Year (date) date is a date field (or expression). |
√ | Year(Dataset.GETDATE) as Year | |||
| to_date | When the user inputs String time and uses this time value to construct Query Conditions, certain rules need to be followed. In this case, the time value needs to be written in the brackets of to _ data () for formatting. Format: to _ date (2018-5-11 14:28:00). |
Province_R.DataDate > to_date(2019-11-07 00:00:00) | ||||
Caution: In UDB/UDBX Datasource, query by using Date in the format of "yy-mm-dd HH: mm: SS". For example, the segment value of the date field is 2022/03/21. The SQL statement should be written as: NewLine. Data & gt; '2022-01-01 00:00:00 'can be queried.
Date part Parameter Settings table
| Set up | Explain |
| yyyy | Year |
| q | Quarter |
| m | Month |
| y | On a certain day in a certain year |
| d | God |
| w | Working days |
| ww | Zhou |
| h | Time |
| n | Points |
| s | Seconds |
Related topics
SQL Query Property Query Example