MetaMatrix uses runtime types when executing queries. These are different than semantic types defined in type fields in the MetaMatrix Designer. The runtime type is specified in the runtime type property in the Designer. The ODBC type is also used in API type mappings on top of the JDBC type from which they are derived. Data types can be converted as described in the Data Type Conversions page.

MetaMatrix runtime datatypes consist of the following:

SQL commands contain references to tables and columns. These references are in the form of identifiers, which uniquely identify the tables and columns in the context of the command. All queries in MetaMatrix are processed in the context of a virtual database, or VDB. Because MetaMatrix federates information across multiple sources, tables and columns must be scoped in some manner to avoid conflicts. This scoping is provided by models, which contain the information for each data source or set of views.

Fully-qualified table and column names are of the following form, where the separate 'parts' of the identifier are delimited by periods.

• TABLE: <model_name>.<table_spec>
• COLUMN: <model_name>.<table_spec>.<column_name>

Because different data sources organize tables in different ways, some prepending catalog or schema or user information, MetaMatrix allows the 'table_spec' to be a dot-delimited construct.

Some examples of valid fully-qualified table identifiers are:

• MyModel.MySchema.Portfolios
• MyModel.Inventory
• MyModel.MyCatalog.dbo.Authors

Some examples of valid fully-qualified column identifiers are:

• MyModel.MySchema.Portfolios.portfolioID
• MyModel.Inventory.totalPallets
• MyModel.MyCatalog.dbo.Authors.lastName

Fully-qualified identifiers can always be used in SQL commands. Partially- or unqualified forms can also be used, as long as the resulting names are unambiguous in the context of the command. Different forms of qualification can be mixed in the same query.

Identifiers, literals, and functions can be combined into expressions. Expressions can be used almost anywhere in a query -- SELECT, FROM (if specifying join criteria, WHERE, GROUP BY, HAVING. You cannot use expressions in an ORDER BY clause.

MetaMatrix supports the following types of expressions:

• Column identifiers
• Literals (including null)
• Scalar functions
• Aggregate functions
• Case and searched case
• Scalar subqueries
• Parameter references

Column identifiers are used to specify the output columns in SELECT statements, the columns and their values for INSERT and UPDATE statements, and criteria used in WHERE and FROM clauses. They are also used in GROUP BY, HAVING, and ORDER BY clauses.

The syntax for column identifiers was defined in the Identifiers section above.

Aliases

Literal values represent fixed values. These can any of the 'standard' data types: string, integer, double, boolean, date, time, timestamp. Some examples of simple literal values are:

• ‘abc’
• ‘isn’’t true’ - use an extra single tick to escape a tick in a string with single ticks
• 5
• -37.75e01
• 100.0
• true
• false
• '\27'

Integer values will be assigned an integral data type big enough to hold the value (integer, long, or biginteger). Floating point values will always be parsed as a double.

MetaMatrix provides a syntax for specifying some non-string literal values using a special literal syntax:

• {b'true'} - boolean
• {d'2006-10-12'} - date (YMD)
• {t'10:59:30'} - time, second precision (HMS)
• {ts'2006-12-24 11:59:59.999'} - timestamp, millisecond precision

The keyword 'null' is used to represent an absent or unknown value and is inherently untyped. In many cases, a null literal value will be assigned an implied type based on context. For example, in the function '5 + null', the null value will be assigned the type 'integer' to match the type of the value '5'. A null literal used in the SELECT clause of a query with no implied context will be assigned to type 'string'.

MetaMatrix supports the use of scalar functions -- functions that return a single scalar value -- in SQL statements. MetaMatrix provides an extensive set of built-in functions, covering all the functions commonly used in JDBC and SQL. In addition, MetaMatrix provides the capability for users to define their own custom functions, and subsequently use those in queries.

Aggregate functions take sets of values from a group produced by an explicit or implicit GROUP BY and return a single scalar value computed from the group. MetaMatrix supports the following aggregate functions:

• COUNT(*) – count the number of values (including nulls and duplicates) in a group
• COUNT(expression) – count the number of values (excluding nulls) in a group
• SUM(expression) – sum of the values (excluding nulls) in a group
• AVG(expression) – average of the values (excluding nulls) in a group
• MIN(expression) – minimum value in a group (excluding null)
• MAX(expression) – maximum value in a group (excluding null)

Additionally, some aggregate functions may contain a keyword 'DISTINCT' before the expression, indicating that duplicate expression values should be ignored. DISTINCT is not allowed in COUNT(*) and is not meaningful in MIN or MAX (result would be unchanged), so it can be used in COUNT, SUM, and AVG.

Aggregate functions may only be used in the HAVING or SELECT clauses and may not be nested within another aggregate function.

Aggregate functions may be nested inside other functions.

For more information on aggregates, see the sections on GROUP BY or HAVING.

MetaMatrix supports two forms of the CASE expression which allows conditional logic in a scalar expression. The two forms are:

• CASE <expr> ( WHEN <expr> THEN <expr>)+ [ELSE expr] END
• CASE ( WHEN <criteria> THEN <expr>)+ [ELSE expr] END

Each form allows for an output based on conditional logic. The first form starts with an initial expression and evaluates WHEN expressions until the values match, and outputs the THEN expression. If no WHEN is matched, the ELSE expression is output. If no WHEN is matched and no ELSE is specified, a null literal value is output.

The second form (the searched case expression) searches the WHEN clauses, which specify an arbitrary criteria to evaluate. If any criteria evaluates to true, the THEN expression is evaluated and output. If no WHEN is true, the ELSE is evaluated or NULL is output if none exists.

Subqueries can be used to produce a single scalar value in the WHERE or HAVING clauses only. A scalar subquery must have a single column in the SELECT clause and should return either 0 or 1 row. If no rows are returned, null will be returned as the scalar subquery value.

For other types of subqueries, see the Subqueries section below.

Parameters are specified using a '?' symbol. Parameters may only be used with PreparedStatement or CallableStatements in JDBC. Each parameter is linked to a value specified by 1-based index in the JDBC API.

Criteria are of two basic forms:

• Predicates that evaluate to true or false
• Logical criteria that combine predicates (AND, OR, NOT)

MetaMatrix supports the following predicates:

• expression (=|<>|!=|<|>|<=|>=) expression
• expression (=|<>|!=|<|>|<=|>=) (ANY|ALL|SOME) expression
• expression [NOT] IS NULL
• expression [NOT] LIKE expression [ESCAPE char]
• expression [NOT] IN (expression, ...)
• expression [NOT] IN (subquery)
• EXISTS(subquery)
• expression BETWEEN minExpression AND maxExpression

All predicates and criteria can be used in the FROM clause (for join criteria), WHERE clause, HAVING clause, or CASE expressions.

Some examples of valid criteria are:

• (balance > 2500.0)
• 100*(50 - x)/(25 - y) > z
• concat(areaCode,concat('-',phone)) LIKE '314'

There are 4 basic commands for manipulating data in SQL, corresponding to the CRUD create, read, update, and delete operations: INSERT, SELECT, UPDATE, and DELETE. In addition, procedures can be executed using the EXECUTE command.

The SELECT command is used to retrieve records from one or more tables.

A SELECT command has a number of clauses:

• SELECT ...
• [FROM ...]
• [WHERE ...]
• [GROUP BY ...]
• [HAVING ...]
• [ORDER BY ...]
• [LIMIT [offset,] limit]
• [OPTION ...]

The order of the clauses in a query is as follows:

All of these clauses other than OPTION are defined by the SQL specification. The specification also specifies the order that these clauses will be logically processed. Below is the processing order where each stage passes a set of rows to the following stage. Note that this processing model is logical and does not represent the way any actual database engine performs the processing, although it is a useful model for understanding questions about SQL.

• FROM stage - gathers all rows from all tables involved in the query and logically joins them with a Cartesian product, producing a single large table with all columns from all tables. Joins and join criteria are then applied to filter rows that do not match the join structure.
• WHERE stage - applies a criteria to every output row from the FROM stage, further reducing the number of rows
• GROUP BY stage - groups sets of rows with matching values in the group by columns.
• HAVING stage - applies criteria to each group of rows. Criteria can only be applied to columns that will have constant values within a group (those in the grouping columns or aggregate functions applied across the group).
• SELECT stage - specifies the column expressions that should be returned from the query. Expressions are evaluated, including aggregate functions based on the groups of rows, which will no longer exist after this point. The output columns are named using either column aliases or an implicit name determined by the engine. If SELECT DISTINCT is specified, duplicate removal will be performed on the rows being returned from the SELECT stage.
• ORDER BY stage - sorts the rows returned from the SELECT stage as desired. Supports sorting on multiple columns in specified order, ascending or descending. The output columns will be identical to those columns returned from the SELECT stage and will have the same name.
• LIMIT stage - returns only the specified rows (with skip and limit values).

This model can be used to understand many questions about SQL. For example, columns aliased in the SELECT clause can only be referenced by alias in the ORDER BY clause. Without knowledge of the processing model, this can be somewhat confusing. Seen in light of the model, it is clear that the ORDER BY stage is the only stage occurring after the SELECT stage, which is where the columns are named. Because the WHERE clause is processed before the SELECT, the columns have not yet been named and the aliases are not yet known.

The INSERT command is used to add a record to a table.

• INSERT INTO table (column,...) VALUES (value,...)

The UPDATE command is used to modify records in a table. The operation may result in 1 or more records being updated, or in no records being updated if none match the criteria.

• UPDATE table SET (column=value,...) [WHERE criteria]

The DELETE command is used to remove records from a table. The operation may result in 1 or more records being deleted, or in no records being deleted if none match the criteria.

• DELETE FROM table [WHERE criteria]

The EXECUTE command is used to execute a procedure, such as a virtual procedure or a stored procedure. Procedures may have zero or more scalar input parameters. The return value from a procedure is a result set, the same as is returned from a SELECT. Note that EXEC can be used as a short form of this command.

• EXECUTE proc()
• EXECUTE proc(param1)
• EXECUTE proc(param1,param2)
• EXECUTE proc(name1=param1,name4=param4,name7=param7)
• EXEC proc( param1, param2, param3 )

MetaMatrix supports creating temporary,or "temp", tables. Temp tables are dynamically created, but are treated as any other physical table.

Temp tables can be defined implicitly by referencing them in a SELECT INTO or in an INSERT statement or explcitly with a CREATE TABLE statement. Implicitly created temp tables must have a name that starts with '#'.

Creation syntax:

• CREATE LOCAL TEMPORARY TABLE <temporary table name> (<column name> <data type>,...)
• SELECT <element name>,...INTO <temporary table name> FROM <table name>
• INSERT INTO <temporary table name> ((<column name>,...)VALUES (<value>,...)

Drop syntax:

• DROP TABLE <temporary table name>

Limitations:

• With the CREATE TABLE syntax only basic table definition (column name and type information) is supported.
• The "ON COMMIT" clause is not supported in the CREATE TABLE statement.
• "drop behavior" option is not supported in the drop statement.
• Only local temporary tables are supported. This implies that the scope of temp table will be either to the sesssion or the block of a virtual procedure that creates it.
• Session level temp tables are not fail-over safe.
• temp tables are non-transactional.
• Temp tables do not support update or delete operations.

The following example loads a temporary table with data from 2 sources, and with a manually inserted record, and then uses that temp table in a subsequent query. Note that the #temp temporary table is not accessable outside of the procedure block that creates it.