SQL for reporting and analysis on file geodatabases

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.

The following a list of functions that can be used with file geodatabases and examples.

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"