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: Functions that operate on categorical data only.
▪Text, categorical, or array functions: Functions that operate on text or categorical data, or arrays.
▪Text functions: Functions that operate on text data only.
▪Date and time functions: Functions that operate on date and time data only.
▪Conversion functions: Functions that convert data from one data type to another.
▪Random number functions: Functions for generating random numbers.
▪List functions: Functions for ordering, and retrieving items from, category lists, arrays, and collections of objects.
▪Mathematical functions: A number of mathematical functions.
▪Miscellaneous functions: Functions that do not apply to any of the other groups.
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 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