SQL syntax

Tables

The UNICOM Intelligence JDBC Driver supports the VDATA, HDATA and SYS.* tables.

VDATA and HDATA are supported by the IUNICOM IntelligenceOLE DB provider. After a query, the JDBC driver returns a standard JDBC result set (the JDBC driver converts results returned by the UNICOM Intelligence OLE DB provider into a JDBC result set). The transformation can result in multiple tables being returned. For more information, see Query from VDATA and Query from HDATA.

▪VDATA: A single, flat table that contains a column for each variable that can be presented in a flattened form.

▪HDATA: A set of hierarchical tables (the top-level table is called HDATA). Each lower-level table represents the data in a loop or grid.

For more information, see Virtual tables.

SQL syntax

The JDBC driver works in pass-through mode. For more detailed information about SQL query statements, see “SQL Queries” in the UNICOM Intelligence Developer Library. That section contains information about: basic SQL queries, advanced SQL queries, hierarchical SQL queries, and advanced hierarchical SQL queries.

Expression evaluation

The UNICOM Intelligence Evaluate component implements expression parsing and evaluation. The component is used by the Case Data Model (CDM), the Metadata Model (MDM), and the mrScript engine. The Evaluate component supports standard expression grammar. The parser is unique, however, in its support for categorical variables and its optional support for single or double-quoted string literals. For more information about expression evaluation, see Expression evaluation.

UNICOM Intelligence Function Library

The UNICOM Intelligence Function Library supplements basic expression evaluation with more advanced operations. For more information about the function library, see UNICOM Intelligence Function Library.

The functions are in these groups:

▪Categorical functions: Operate on categorical data.

▪Text, categorical, or array functions: Operate on text or categorical data, or arrays.

▪Text functions: Operate on text data.

▪Date and time functions: Operate on date and time data.

▪Conversion functions: Convert data from one data type to another.

▪Random number functions: Generate random numbers.

▪List functions: Functions for ordering, and retrieving items from, category lists, arrays, and collections of objects.

▪Mathematical functions.

▪Miscellaneous functions.

Aggregate functions

The Case Data Model supports the following aggregate functions. These functions can be used in SQL queries to summarize information from multiple rows.

AVG(expr)

Returns the average of the values defined in the expression. This function can be used with numeric data only.

BASE(expr)

Returns the total number of cases included in the expression. Generally, the base includes every case for which the value is not NULL. However, if the IsInBase attribute has been set to True, the case is included in the base regardless of the value. Conversely, if the IsInBase attribute has been set to False, the case is excluded from the base regardless of the value.

COUNT(expr)

Returns a count of the cases in a column.

SUM(expr)

When used with numeric data, returns the sum of the values. When used with categorical data, it returns the union of the categorical values.

MIN(expr)

Returns the lowest value defined in the expression.

MAX(expr)

Returns the highest value defined in the expression.

STDEV(expr)

Returns the standard deviation of the values defined in the expression. This function can be used with only numeric data. The standard deviation is a measure of dispersion around the mean. In a normal distribution, 68% of cases fall within one standard deviation of the mean and 95% of cases fall within 2 standard deviations. For example, if the mean age is 45 with a standard deviation of 10, then 95% of the cases would be between 25 and 65 in a normal distribution.

See also