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.

OperatorDescription

*

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

FunctionDescription

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.

FunctionDescription

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.

FunctionDescription

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:

LocationSalesForecast

Redlands

39000

55000

Palm Springs

60000

61000

Riverside

40000

40000

Result:

LocationResults

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"