The file geodatabase allows improved analysis and reporting through the use of expressions and aliases via the SubFields (field list) method in a QueryDef. In addition ORDER BY and GROUP BY are supported through the PostFixClause. In support of GROUP BY, the expressions include aggregation functions like MIN, MAX, and SUM.
ALIAS
Gives a column another name to provide more understandable output.
SQL
SELECT NAME, POP1997 - POP1990 as PopChange
FROM counties ORDER BY NAME
ArcObjects
pQueryDef.SubFields = "NAME, POP1997 - POP1990 as PopChange"
pQueryDef.Tables = "counties"
pQueryDef.PostfixClause = "ORDER BY NAME"
CASE
Evaluates a list of conditions and returns one of several result expressions.
SQL
SELECT name,salary,
CASE
WHEN salary <= 2000 THEN 'low'
WHEN salary > 2000 AND salary <= 3000 THEN 'average'
WHEN salary > 3000 THEN 'high'
END AS salary_level
FROM employees
ORDER BY salary ASC
ArcObjects
pQueryDef.SubFields = "name,salary,
CASE
WHEN salary <= 2000 THEN 'low'
WHEN salary > 2000 AND salary <= 3000 THEN 'average'
WHEN salary > 3000 THEN 'high'
END AS salary_level"
pQueryDef.Tables = " employees"
pQueryDef.PostfixClause = " ORDER BY salary ASC "
COALESCE
Returns the first non-null field value among its arguments.
SQL
SELECT Name, COALESCE(Business_Phone, Cell_Phone, Home_Phone)
Contact_Phone
FROM Contact_Info
ArcObjects
queryDef.SubFields = "Name, COALESCE(Business_Phone, Cell_Phone,
Home_Phone) Contact_Phone"
queryDef.Tables = "Contact_Info"
Contact_Info:
Name | Business_Phone | Cell_Phone | Home_Phone |
Jeff | 531-2531 | 622-7813 | 565-9901 |
Laura | NULL | 772-5588 | 312-4088 |
Peter | NULL | NULL | 594-7477 |
Result:
Name | Contact_Phone |
Jeff | 531-2531 |
Laura | 772-5588 |
Peter | 594-7477 |
EXPRESSIONS
SQL
SELECT SIN(sunangle)
FROM sightings
ArcObjects
queryDef.SubFields = "SIN(sunangle)"
queryDef.Tables = "sightings"
SET FUNCTIONS
| Operator | Description |
AVG | Calculates the average of all values in the group. Null values are ignored. |
COUNT(*), COUNT(expression) | COUNT(*) returns the number of records in a table. Nulls are included. COUNT(expression) returns the number of values in the specified expression. Nulls are ignored. |
MAX | Finds the maximum values in the group. Null values are ignored. |
MIN | Finds the minimum value in the group. Null values are ignored. |
STDDEV, STDDEV_SAMP | Returns the sample standard deviation of the expression. |
STDDEV_POP | Returns the population standard deviation of the expression. |
SUM | Finds the values in the group. Null values are ignored. |
VAR, VAR_SAMP | Returns the sample variance of the expression. |
VAR_POP | Returns the population variance of the expression. |
ARITHMETIC OPERATIORS
You use an arithmetic operator to add, subtract, multiply, and divide numeric values.
| Operator | Description |
* | Arithmetic operator for multiplication |
/ | Arithmetic operator for division |
+ | Arithmetic operator for addition |
- | Arithmetic operator for subtraction |
FUNCTIONS
The following is the full list of functions supported by file geodatabases.
Date Functions
| Function | Description |
CURRENT_DATE | Returns the current date. |
EXTRACT (extract_field FROM extract_source) | Returns the extract_field portion of theextract_source. The extract_source argument is a date-time expression. The extract_field argument can be one of the following keywords: YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND. |
CURRENT TIME | Returns the current time. |
CURRENT_TIMESTAMP | Returns the current time and date. |
String Functions
Arguments denoted as string_exp can be the name of a column, a character-string-literal, or the result of another scalar function, where the underlying data type can be represented as a character type.
Arguments denoted as character_exp are variable-length character strings.
Arguments denoted as start or length can be a numeric-literal or the result of another scalar function, where the underlying data type can be represented as a numeric type.
These string functions are 1-based; that is, the first character in the string is character 1.
| Function | Description |
CHAR_LENGTH (string_exp) | Returns the length in characters of the string expression. |
CONCAT (string_exp1, string_exp2) | Returns a character string that is the result of concatenating string_exp2 to string_exp1. |
LOWER (string_exp) | Returns a string equal to that in string_exp, with all uppercase characters converted to lowercase. |
POSITION (character_exp IN character_exp) | Returns the position of the first character expression in the second character expression. The result is an exact numeric with an implementation-defined precision and a scale of zero. |
SUBSTRING (string_exp FROM start FOR length) | Returns a character string that is derived from string_exp, beginning at the character position specified by start for lengthcharacters. |
TRIM(BOTH | LEADING | TRAILINGtrim_characterFROM string_exp) | Returns the string_exp with the trim_character removed from the leading, trailing, or both ends of the string. |
UPPER (string_exp) | Returns a string equal to that in string_exp, with all lowercase characters converted to uppercase. |
Numeric Functions
All numeric functions return a numeric value.
Arguments denoted as numeric_exp, float_exp, or integer_exp can be the name of a column, the result of another scalar function, or a numeric-literal, where the underlying data type could be represented as a numeric type.
| Function | Description |
ABS (numeric_exp) | Returns the absolute value of numeric_exp. |
ACOS (float_exp) | Returns the arccosine of float_exp as an angle, expressed in radians. |
ASIN (float_exp) | Returns the arcsine of float_exp as an angle, expressed in radians. |
ATAN (float_exp) | Returns the arctangent of float_exp as an angle, expressed in radians. |
CEILING (numeric_exp) | Returns the smallest integer greater than or equal to numeric_exp. |
COS (float_exp) | Returns the cosine of float_exp, where float_expis an angle expressed in radians. |
FLOOR (numeric_exp) | Returns the largest integer less than or equal to numeric_exp. |
LOG (float_exp) | Returns the natural logarithm of float_exp. |
LOG10 (float_exp) | Returns the base 10 logarithm of float_exp. |
MOD (integer_exp1, integer_exp2) | Returns the remainder of integer_exp1 divided by integer_exp2. |
POWER (numeric_exp, integer_exp) | Returns the value of numeric_exp to the power of integer_exp. |
ROUND (numeric_exp, integer_exp) | Returns numeric_exp rounded to integer_exp places to the right of the decimal point. If integer_exp is negative, numeric_exp is rounded to |integer_exp| places to the left of the decimal point. |
SIGN (numeric_exp) | Returns an indicator of the sign of numeric_exp. If numeric_exp is less than zero, -1 is returned. If numeric_exp equals zero, 0 is returned. If numeric_exp is greater than zero, 1 is returned. |
SIN (float_exp) | Returns the sine of float_exp, where float_exp is an angle expressed in radians. |
TAN (float_exp) | Returns the tangent of float_exp, where float_exp is an angle expressed in radians. |
TRUNCATE (numeric_exp, integer_exp) | Returns numeric_exp truncated to integer_exp places to the right of the decimal point. If integer_exp is negative, numeric_exp is truncated to |integer_exp| places to the left of the decimal point. |
GROUP BY
Used to collect data across multiple records and group the results by one or more columns
SQL
SELECT STATE_NAME, SUM(POP1990) as TotalPopulation
FROM counties
GROUP BY STATE_NAME ORDER BY STATE_NAME
ArcObjects
pQueryDef.SubFields = "STATE_NAME, SUM(POP1990) as
TotalPopulation"
pQueryDef.Tables = "counties"
pQueryDef.PostfixClause = "GROUP BY STATE_NAME ORDER BY
STATE_NAME"
HAVING
A where clause on a GROUP BY.
SQL
SELECT department, MAX(salary) as Highest_salary
FROM employees
GROUP BY department HAVING MAX(salary) < 50000
ArcObjects
queryDef.SubFields = "department, MAX(salary) as Highest_salary"
queryDef.Tables = "employees"
pQueryDef.PostfixClause = "GROUP BY department HAVING MAX(salary)
< 50000"
JOINS
Combines the records from two or more tables.
Cross Join
SQL
SELECT Table1.name, Table1.Address, Table2.name, Table2.Salary
FROM Table1 CROSS JOIN Table2
ArcObjects
queryDef.SubFields = "Table1.name, Table1.Address, Table2.name,
Table2.Salary"
queryDef.Tables = "Table1 CROSS JOIN Table2"
Inner Join
SQL
SELECT Table1.C1, Table1.C2, Table2.C3, Table2.C4 FROM Table1
INNER JOIN Table2 ON Table1.C1 = Table2.C3
ArcObjects
queryDef.SubFields = "Table1.C1, Table1.C2, Table2.C3,
Table2.C4"
queryDef.Tables = "Table1 INNER JOIN Table2 ON Table1.C1 =
Table2.C3"
Left Outer Join
SQL
SELECT Table1.C1, Table1.C2, Table2.C3, Table2.C4 FROM Table1
LEFT OUTER JOIN Table2 ON Table1.C1 = Table2.C3
ArcObjects
queryDef.SubFields = "Table1.C1, Table1.C2, Table2.C3,
Table2.C4"
queryDef.Tables = "Table1 LEFT OUTER JOIN Table2 ON Table1.C1 =
Table2.C3"
Right Outer Join
SQL
SELECT * FROM Table1 RIGHT OUTER JOIN Table2 ON Table1.C1 =
Table2.C3
ArcObjects
queryDef.SubFields = "*"
queryDef.Tables = "Table1 RIGHT OUTER JOIN Table2 ON Table1.C1 =
Table2.C3"
NULLIF
NULLIF returns NULL if the two parameters provided are equal; otherwise, the value of the first parameter is returned.
SQL
SELECT Location, NULLIF(Sales, Forecast) as Results FROM
StoreSales
ArcObjects
queryDef.SubFields = " Location, NULLIF(Sales, Forecast) as
Results"
queryDef.Tables = "StoreSales"
StoreSales:
| Location | Sales | Forecast |
Redlands | 39000 | 55000 |
Palm Springs | 60000 | 61000 |
Riverside | 40000 | 40000 |
Result:
| Location | Results |
Redlands | 39000 |
Palm Springs | 60000 |
Riverside | NULL |
ORDER BY
Specifies sort order. Order can be ascending (ASC) or descending (DESC) and be collated. Collation types include BINARY(BIN), CASESENSITIVE(CASE), and NOCASESENSITIVE(NOCASE). Binary collation is both case and accent sensitive. Casesensitive distinguishes between uppercase and lowercase letters. Nocasesensitive does not distinguish between uppercase and lowercase letters.
SQL
SELECT STATE_NAME, POP1990
FROM counties ORDER BY STATE_NAME
ArcObjects
pQueryDef.SubFields = "STATE_NAME, POP1990"
pQueryDef.Tables = "counties"
pQueryDef.PostfixClause = "ORDER BY STATE_NAME"
SQL
SELECT STATE_NAME, POP1990
FROM counties ORDER BY STATE_NAME COLLATE BINARY ASC
ArcObjects
pQueryDef.SubFields = "STATE_NAME, POP1990"
pQueryDef.Tables = "counties"
pQueryDef.PostfixClause = "ORDER BY STATE_NAME COLLATE BINARY
ASC"