SQL syntax
Tables
The UNICOM Intelligence JDBC Driver supports the VDATA, HDATA and SYS.* tables.
VDATA and HDATA are supported by the UNICOM Intelligence OLE 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 information about SQL query statements, see “SQL queries” in the UNICOM Intelligence Developer Documentation Library. The section includes information about:
▪basic SQL queries
▪advanced SQL queries
▪hierarchical SQL queries
▪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.
See also
UNICOM Intelligence Developer Documentation Library
The UNICOM Intelligence Developer Documentation Library supplements basic expression evaluation with more advanced operations. For more information about the function library, see “UNICOM Intelligence Function Library” in the UNICOM Intelligence Developer Documentation Library.
See also
Aggregate functions
The Case Data Model provides support for a number of 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 numeric data only. 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