SQL statements

You can use SQL statements to interact with the data in the database. You can query the data to read it, create custom objects, alter those objects, and delete them.

Note: You must have appropriate permissions to use CREATE, ALTER, and DROP statements. The custom object definitions are stored in the AF configuration database and are based on AF security.

• For the custom objects to be visible, Read and ReadData permissions are required.

• CREATE and ALTER statements require Write and WriteData permissions.

• DROP statement requires Delete permission.

SELECT statement

The SQL SELECT statement queries data from tables in the database.

Note: Brackets [ ] denote optional parts of the statement, braces { } with vertical bars | denote mutually exclusive parts.

<select_statement> ::=
<query>
[UNION ALL <query> [UNION ALL … ]]
[ORDER BY <expression> [ASC | DESC] [, …]]

<query> ::=
SELECT [ALL | DISTINCT] [TOP integer_value] <select_list>
[FROM <table_source> [, …]
[WHERE <condition>]
[GROUP BY <expression> [, …]]
[HAVING <condition>]

<select_list> ::=
{* |
{table_name | view_name | table_alias} .* |
<expression> [[AS] column_alias] |
column_alias = <expression>}
[, …]

<table_source> ::=
table_name [[AS] table_alias] |
view_name [[AS] table_alias] |
<select_statement> [[AS] table_alias] |
<joined_table> [[AS] table_alias] |
<table_valued_function> [[AS] table_alias] |
table_template_name < <template_argument> [, ... ] > [[AS] table_alias]

<template_argument> ::= <single_template_argument> | <tuple_template_argument>
<single_template_argument> ::= integer_value | float_value | string_value | True | False | NULL
<tuple_template_argument> ::= { <single_template_argument> [, ...] }

<joined_table> ::=
<table_source> [INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER]] JOIN <table_source> ON <condition>
<table_source> {CROSS | OUTER} APPLY <table_valued_function>

<table_valued_function> ::=
table_valued_function_name ([<expression> [, …]]) |
table_valued_function_template_name < <template_argument> [, ... ] > ([<expression> [, ...]])

<condition> ::=
{[NOT] <expression> | (<condition>)}
[{AND | OR} <condition>][, …]

<expression> ::=
integer_value | float_value | string_value |
True | False |
NULL |
? |
column_name |
[+ | -] <expression> |
<expression > {+ | - | * | / | %} <expression> |
(<expression>) |
<expression> {= | > | < | >= | <= | <> | !=} <expression> |
<expression> [NOT] IN (<expression> [, …]) |
<expression> [NOT] BETWEEN <expression> AND <expression> |
<expression> [NOT] LIKE <expression> [ESCAPE <expression>] |
<expression> IS [NOT] NULL |
CASE <expression> WHEN <expression> THEN <expression> [WHEN …] [ELSE <expression>] END |
CASE WHEN <condition> THEN <expression> [WHEN …] [ELSE <expression>] END |
CAST (<expression> AS data_type_name) |
COALESCE (<expression>, …) |
COUNT (*) |
aggregate_function_name ([ALL | DISTINCT] <expression>) |
nonaggregate_function_name ([<expression> [, …]])

CREATE CATALOG statement

The CREATE CATALOG statement creates a new catalog.

Syntax:

CREATE CATALOG catalog_name

Note: You cannot use the names Internal or System when creating a catalog, because catalogs with those names already exist.

CREATE SCHEMA statement

The CREATE SCHEMA statement creates a new schema.

Syntax:

CREATE SCHEMA catalog_name.schema_name

CREATE VIEW statement

The CREATE VIEW statement creates a new view. A view is effectively an SQL query stored in the catalog.

Syntax:

CREATE VIEW catalog_name.schema_name.view_name [(column_name, [, ...])] AS <select_statement>

Remarks: The data model objects referenced in the select_statement must be fully qualified, that is, catalog.schema.objectname.

CREATE TABLE statement

The CREATE TABLE statement creates a new function table.

Syntax:

CREATE TABLE catalog_name.schema_name.function_table_name AS catalog_name.schema_name.table-valued_function_name [(data_type_name, [, …])]

CREATE FUNCTION statement

The CREATE FUNCTION statement creates a new table-valued function overload.

Syntax:

CREATE FUNCTION catalog_name.schema_name.function_name ([@parameter_name parameter_data_type_name, [, …]]) AS
<select_statement>

Remarks: The data model objects referenced in the select_statement must be fully qualified, that is, catalog.schema.objectname. Only the following select_statement structures are supported:

• SELECT <column_names> FROM catalog_name.schema_name.table_name WHERE table_column1 = @parameter1 AND table_column2 = @parameter2 …

• SELECT <column_names> FROM catalog_name.schema_name.table-valued_function_name(@parameter1, @parameter2, …)

• SELECT <column_names> FROM catalog_name.schema_name.table-valued_function_tamplate_name<<template_arguments>>(@parameter1, @parameter2, …)

ALTER VIEW statement

The ALTER VIEW statement modifies a previously created view.

Syntax:

ALTER VIEW catalog_name.schema_name.view_name [(column_name, [, ...])] AS <select_statement>

Remarks: The data model objects referenced in the select_statement must be fully qualified, that is, catalog.schema.objectname.

ALTER FUNCTION statement

The ALTER FUNCTION statement modifies a previously created table-valued function overload.

Syntax:

ALTER FUNCTION catalog_name.schema_name.function_name ([@parameter_name parameter_data_type_name, [, …]]) AS <select_statement>

Remarks: The data model objects referenced in the select_statement must be fully qualified, that is, catalog.schema.objectname. Only the following select_statement structures are supported:

• SELECT <column_names> FROM catalog_name.schema_name.table_name WHERE table_column1 = @parameter1 AND table_column2 = @parameter2 …

• SELECT <column_names> FROM catalog_name.schema_name.table-valued_function_name(@parameter1, @parameter2, …)

• SELECT <column_names> FROM catalog_name.schema_name.table-valued_function_tamplate_name<<template_arguments>>(@parameter1, @parameter2, …)

DROP CATALOG statement

The DROP CATALOG statement deletes a catalog.

Syntax:

DROP CATALOG catalog_name

DROP SCHEMA statement

The DROP SCHEMA statement deletes a schema.

Syntax:

DROP SCHEMA catalog_name.schema_name

DROP VIEW statement

The DROP VIEW statement deletes a view and its description from the catalog.

Syntax:

DROP VIEW catalog_name.schema_name.view_name

DROP TABLE statement

The DROP TABLE statement deletes a function table.

Syntax:

DROP TABLE catalog_name.schema_name.table_name.function_name

DROP FUNCTION statement

The DROP FUNCTION statement deletes a table-valued function or table-valued function overload. If parameter data type names are specified, the specific table-valued function overload will be deleted; otherwise, the entire table-valued function will be deleted.

Syntax:

DROP FUNCTION catalog_name.schema_name.function_name [(parameter_data_type_name, [, …])]