JDBC driver: 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(<expresson>)
Returns the average of the values defined in the expression. This function can be used with numeric data only.
BASE(<expresson>)
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(<expresson>)
Returns a count of the cases in a column.
MIN(<expresson>)
Returns the lowest value in the expression.
MAX(<expresson>)
Returns the highest value in the expression.
STDEV(<expresson>)
Returns the standard deviation of the values in the expression. This function can be used with only numeric data.
SUM(<expresson>)
With numeric data, returns the sum of the values.
With categorical data, returns the union of the categorical values.
See also