EIQ Product Server SQL Syntax.. 1

Common Syntax. 1

Schema-related Commands Syntax. 1

Virtual Schema View SQL Syntax. 2

SELECT Syntax. 2

Nested SELECT Syntax. 4

Text Search Syntax. 5

Examples. 5

Text Search Functions. 5

Link Index Query Syntax. 6

A-to-B Query Syntax. 6

Degrees of Separation (DoS) Query Syntax. 6

UPDATE syntax. 6

Native API SQL Syntax. 7

Appendix A: Queries resolved entirely at EIQ Indexes. 7

Appendix B: Advanced Settings for Query Processing. 8

EIQ Product Server SQL Syntax

This document outlines SQL query syntax supported by EIQ Product Servers.

 

Depending on the EIQ Product server configuration and query interface used, there are two levels of SQL support:

 

• Virtual Schema View (SuperSchema) SQL syntax - This syntax support level applies mainly to EIQ SuperAdapter, EIQ ConventionalAdapter, and EIQ Federation Server configurations
• Native API SQL syntax - This syntax support level applies to EIQ TurboAdapter configurations and direct connections to EIQ Indexes and EIQ Non-Virtual Indexes (ENVI). 

 

Apart from the above, applications connecting to a data source through EIQ Product servers using an 'REGDB' connection option have to use the native data source supported SQL syntax (SQL Server, Oracle, DB2, etc.).

 

EIQ Products use TQL, a lower level query language, underneath the SQL. TQL is a procedure language (as opposed to SQL which is a declarative language) and offers developers extraordinary flexibility and control over query processing steps. TQL is useful for custom development requiring such flexibility and control. See EIQ Product SDK for more details.

 

Common Syntax

Schema-related Commands Syntax

SCHEMA TABLES

This command returns information about the tables that are available for the current schema. It gives details such as table name, schema name, date created, date modified, record length, and the maximum number of records. If the user connects directly to an EIQ Index, it returns all the tables in the index with the above details for each table.

 

SCHEMA COLUMNS

This command returns information about the columns that are available for the current schema. It gives details such as table name, column name, data type, nullable, number precision, character length, and key info (4 indicates 'Results Only' column, 53 indicates Virtual Key, 33 indicates Non-Virtual Key). If the user connects directly to an EIQ Index, it returns all the columns from the indexed tables. If the user connects to an EIQ TurboAdapter, it returns indexed columns as well as non-indexed (RESULTS ONLY) columns for the indexed tables.

 

To obtain column information for a specific table, we use the syntax: 

 

Schema Columns <CatalogName> <SchemaName> <TableName>

 

A 'NULL' value for an argument indicates that it will not be filtered for specific values. For example:

"Schema Columns NULL NULL MyTable"

This returns information on all columns for a view named MyTable.

 

Key Types:

4          - 'Results Only' column

53        - Virtual Key

33        - Non-Virtual Key

 

Virtual Schema View SQL Syntax

SELECT Syntax

A subset of theSQL-92 SELECT command is supported on the virtual schema view interface. A virtual schema view presents a single table, flat schema interface and shields the applications from the details of underlying data source schemas. Therefore, there is no support for JOINs across tables in the virtual schema view. Any JOINs required at the data source schema level are handled through the Automatic Query Processing feature by EIQ Product Servers. The following is the supported syntax for the SELECT statement:

 

[DS="<DataSource>,<DataSource>,...";]

SELECT [DISTINCT] <FieldList>

FROM <ViewName>

[WHERE <Conditions>]

[ORDER BY <OrderByFieldList>]

[RECORDS(StartRow, EndRow)]

 

Where:

 <DataSource> ::= External Data Source Alias as registered with the EIQ Product Server

<ViewName> ::=

                 MYTABLE

                 | SuperSchema table defined for the connected Virtual Data Source (VDS)

FieldList> ::=

                   *

                 | <FieldName>[,<FieldName>[,...]]

                 | COUNT (*)

<Conditions> ::= <Condition> [ { <AND|OR> <Condition> }...]

                          | <FieldName1>[,<FieldName2>[,.]] [NOT] IN (SELECT <FieldName3>[,<FieldName4>[,.]] FROM <TableName> WHERE  <Conditions>)

                          | <FieldName1>[,<FieldName2>[,.]] <Compare Operator> (SELECT <FieldName3>[,<FieldName4>[,.]] FROM <TableName> WHERE <Conditions>)

<Condition> ::=

                    FieldName <Compare Operator> <Value>

                  | FieldName [NOT] IN (<Value>,...)

                  | FieldName [NOT] LIKE 'String Value'

                  | FieldName [NOT] BETWEEN <Value> AND <Value>

                  | FieldName IS [NOT] NULL

                  | CONATINS()

      | FieldName IN ( SELECT <FieldName1> ..)

 <Compare Operator> ::=

                   =

                 | >=

                 | <=

                 | <

                 | >

                 | <>

                 | !=

<Value> ::=

                  'String Value'

                | NumericValue

                | DateValue

<NumericValue> ::= xxx[.yyy]

<DateValue> ::=

                  'mm/dd/yyyy'

                | 'yyyy-mm-dd'

<CONATINS()> Please refer to Text Search Syntax section below for details

<OrderFieldList> ::= <FieldName> [ASC | DESC] [,.]

 

Comments:

1. The query results are ordered by the data source by default.
2. ORDER BY orders results for each data source but not across the data sources.
3. If a SuperSchema column has no corresponding mapped column in a data source, any query conditions involving that column are ignored for that data source.
4. The SELECT command supports the Text Search function CONTAINS() in the WHERE condition. CONTAINS() could be used along with structured SQL queries.
5. When [DS="<DataSource>,<DataSource>,...";] is present, EIQ Product Servers limit queries only to the specified data source(s).
6. EIQ TurboAdapters also support SQL queries using external data source schema. See "Appendix A: Supported SQL syntax" in the WhamNAPI documentation for more information.

 

Options for GROUP BY:

 

setoption COUNTAGGREGATION 1|0

 

Where,

1 - count across data sources

0 - count within each data source ( i.e. UNION )

Nested SELECT Syntax

Nested SELECT is supported on the virtual schema view level.

 

Syntax:

SELECT <FieldList> FROM <ViewName> WHERE (<Target FieldList>) IN (SELECT <Source FieldList> FROM <ViewName> [WHERE <Conditions>] )

Where:

 <Target FieldList> ::= <FieldName>[,<FieldName>[,...]]

<Source FieldList> ::= <FieldName>[,<FieldName>[,...]]

 

SELECT <FieldList> FROM <ViewName> WHERE (<Target FieldList>) <Compare Operator> (SELECT <Scalar Value> FROM <ViewName> [WHERE <Conditions>] )

Where:

 <Compare Operator> ::=

                   =

                 | >=

                 | <=

                 | <

                 | >

                 | <>

                 | !=

 

<Scalar Value> ::= One single record with one column value.

 

Comments

• Both the Target FieldList and Source FieldList must have the same number of Fields
• For an EIQ Federation Server, all the Fields in the Target FieldList and Source FieldList must have SuperSchema Mappings for all data sources in the Virtual Data Source. In this case, the nested query performs external an join across the data sources through EIQ SuperAdapters.

 

Examples:

1.    SELECT First_Name, Family_Name, City FROM Mytable WHERE City IN

(SELECT City FROM Mytable WHERE State = 'TX')

2.    SELECT Familyname, Firstname, City FROM Mytable WHERE City IN

(SELECT City FROM Mytable WHERE Firstname IN

( SELECT Firstname FROM Mytable WHERE Familyname ='WANG' ))

3.    SELECT State,Alertlevel FROM Mytable WHERE State NOT IN

(SELECT State FROM Mytable WHERE Alertlevel>5 OR Alertlevel<4)

4.    SELECT Familyname, Firstname, DOB, DateEntered FROM Mytable WHERE DateEntered >

( SELECT max(DateEntered) FROM Mytable WHERE Familyname='Abadia' AND Firstname='Molly')

Note: In the above query, the inner SELECT must return only one scalar value. Otherwise it would return in error.

5.    SELECT "DATA SOURCE", NAME_LAST, NAME_FIRST,DOB from MYTABLE where ( CITY, STATE ) IN

(SELECT STREET, CITY, STATE FROM MYTABLE

 WHERE  NAME_LAST = 'Fleet' AND NAME_FIRST = 'Mark' AND DOB = '1960-10-15' )

The above query returns all persons from every data source who live in the same city and state as the person whose name is 'Fleet, Mark' and date of birth is '1960-10-15':

Text Search Syntax

To perform a text search, use the following syntax in a SQL SELECT statement's WHERE clause.

 

Syntax:

CONTAINS('TextColumnName, SearchText')

 

SearchText  can be "Word" or FunctionName("Word")

 

FunctionName can be any of STEM, SYN(ONYM), SOUNDEX, METAPHONE, PROX(IMITY) text functions.

 

To Indicate weighted/non-weighted words, the syntax is:

 

Word:n

 

where n is 0,1,2, or 3 to indicate the weight level (weights in increasing order from low-weighted to high weighted) of the word in a document. When there is no weight mentioned at the end of the word, it means non weighted words (0 weight).

Examples

 // text search

SELECT * FROM TABLE1 WHERE CONTAINS('TextColumnName, Word')

 

// using various text search functions

SELECT * FROM TABLE1 WHERE CONTAINS('TextColumnName, Stem(Word)')

SELECT * FROM TABLE1 WHERE CONTAINS('TextColumnName, Syn(Word)')

SELECT * FROM TABLE1 WHERE CONTAINS('TextColumnName, Soundex(Word)')

SELECT * FROM TABLE1 WHERE CONTAINS('TextColumnName, Metaphone(Word)')

SELECT * FROM TABLE1 WHERE CONTAINS('TextColumnName, Prox(Word1 Word2)')

 

// phrase search

SELECT * FROM TABLE1 WHERE CONTAINS('TextColumnName, Word1 Word2 . . .')

 

// combination of structured and text search

SELECT * FROM TABLE1 WHERE ColumnA = 'nnn' AND CONTAINS('TextColumnName, Word') OR ColumnB > 'x'

Text Search Functions

1.    Stemming [Stem(Word)]: When stemming is used, the EIQ Server retrieves words that arise from a common stem (root). For instance, Stem(Unites) retrieves results containing words like "Unites" and "United" as both have the same stem "Unite".

2.    Synonym [Syn(Word)]: When Synonym is applied, the EIQ Server retrieves words that are synonyms of the specified word. For instance, Syn(United) returns results that contain "Joined", "Combined", and other synonyms of United. 

3.    Soundex [Soundex(Word)]: When Soundex is applied, the EIQ Server returns results containing words that sound similar to the search word. For instance, Soundex(Omar) returns results containing 'Omeir' because they sound similar. 

4.    Metaphone [Metaphone(Word)]: Metaphone is similar to Soundex. However, its algorithm is more accurate because of its knowledge of the basic rules of English pronunciation. 

5.    Proximity [Prox(Word1 Word2)]: The proximity function retrieves results in which "Word1" and "Word2" appear in close proximity to each other in a given document or record. For instance, Prox(United Canada) retrieves documents and records containing ".United States, Canada...", ".United Canada .", and etc.

Link Index Query Syntax

A-to-B Query Syntax

LINK FROM <DataSourceName>.<EntityA_Name> WHERE <EntityA_Attribute_Conditions> TO <DataSourceName>.<EntityB_Name> WHERE <EntityB_Attribute_Conditions>

Degrees of Separation (DoS) Query Syntax

LINK FROM <DataSourceName>.<Entity_Name> WHERE <Entity_Attribute_Conditions> DOS UPTO <DOS_Value>

 

Where:

<DataSourceName>                         : Name of the corresponding EIQ Data source for an entity of interest - as defined at the Query Server.

<Entity_Name>                                   : Entity Name as defined in the Query Server Configuration.

<Entity_Attribute_Conditions>            : One or more conditions of the form "<Entity Attribute> <Compare Operator> <Value>" separated by 'AND' or 'OR'. ALL attributes for an entity should be included while specifying conditions for it.

<DOS_Value>                                    : Numeric value specifying the degrees of separation desired in the explosion of a particular entity.

 

UPDATE syntax

The UPDATE command is used to modify existing values in data sources.

 

IMPORTANT:

·         Virtual schema views may apply transforms to change the data types and formats of the original source data and may even use substitute values when presenting results. You must take the utmost care when updating values in the original data sources as it can cause inconsistencies in data. Be sure and use only the columns with no transforms in the WHERE clause when isolating the records for updates. This restriction should be observed until reverse transform support is implemented.

·         An EIQ Index update mechanism through EIQ RTIS must be in place for the target data source. Otherwise, the changes are not reflected in the EIQ Indexes.

 

UPDATE <TableName> SET <StandardColumnAssignmentList> [WHERE <Conditions>]

 

Where

TableName := virtual schema view (MyTable or any custom view)

ColumnAssignmentList = <StandardColumnName> = < Value > [, .]

<Value> ::= Value must conform to the original data source type, format etc. (this applies until support for reverse transforms is implemented).

<Condition> ::= < StandardColumnName > <Compare Operator> <Value>

                        |  any filter syntax that the original data source driver supports except parameterized queries and nested selects

 

Note:

1.     All StandardColumnNames must be mapped to a SINGLE data source table, otherwise, an error is returned.

2.     EIQ Server translates the virtual schema view (SuperSchema) into data source specific schemas and submits the SQL statements to the data source drivers. The EIQ Index is not involved during the update. EIQ Indexes can be updated through the normal update process.

3.     EIQ Product Servers do not apply transforms to a Value during the update and is updated as is. DO NOT use the transformed values. Note that SELECT statements using a virtual schema view show transformed values if any transforms are applied to the columns.

4.     Ensure that the columns used in the WHERE clause do not have any transforms in EIQ Indexes for the data source. This can isolate the wrong data and corrupt the data source.

 

Native API SQL Syntax

See Appendix A in the WhamNAPI Overview for Native API syntax.

 

Appendix A: Queries resolved entirely at EIQ Indexes

In conventional federated environments, queries at the federation level fail completely when data sources become unavailable. However, EIQ Product Servers can resolve most queries using EIQ Indexes, even when data sources become unavailable. Until the index inversion feature is fully implemented, this support is limited to only some types of queries.

 

At this time, EIQ Products can resolve queries entirely within EIQ Indexes when there is no need to access data sources for results data. These queries continue to return results even when data sources are unavailable. Queries returning aggregate data on indexed columns are a good example of these types of queries. Note that the results may not reflect any changes in the data sources during the time they are unavailable.

 

The following queries are currently supported:

 

1.  Single table, aggregate values, without any filters, and all the columns are keys BUT not global keys (i.e. the column name is present in only this table in the data source)

 

SELECT aggregatefunction1([distinct] column1), aggregatefunction2([distinct] column2), . from table

 

The above query works for the following EIQ Product Server configurations/connection types that expose the data source schema view:

• Virtual data sources with EIQ TurboAdapter configurations 
• Direct connections to EIQ indexes

 

Queries on VDSs that expose a virtual schema view work if all the mapped standard data model columns in the query are mapped to a single data source table and all the columns are keys but not global keys in the EIQ Indexes for the data source.

 

Examples:

SELECT AVG(salary), SUM(salary), VAR(salary), SUM(distinct reportsto), SUM(reportsto), AVG(distinct reportsto), AVG(reportsto), MAX(reportsto), MIN(reportsto), COUNT(reportsto) FROM employees

SELECT COUNT(region),MAX(region), 10-2  FROM customers

 

SELECT AVG(salary), SUM(salary), VAR(salary), SUM(distinct reportsto), SUM(reportsto), AVG(distinct reportsto), AVG(reportsto), MAX(reportsto), MIN(reportsto), COUNT(reportsto) FROM MyTable

 

2. Single table, with one GROUP BY column, without any filters and having clauses

 

This type of query works for the following EIQ Product Server configurations/connection types:

• Virtual data sources with a virtual schema view
• Virtual data sources with an EIQ TurboAdapter 
• Direct connections to EIQ indexes

 

SELECT aggregatefunction1([distinct] column), aggregatefunction2([distinct] column), . FROM table GROUP BY column

 

Examples:

SELECT COUNT (salary), SUM (salary), MAX (salary), MIN (salary), AVG (salary) from MyTable GROUP BY salary

SELECT COUNT(salary), SUM(salary), MAX(salary),MIN(salary),AVG(salary) from employees GROUP BY salary

 

3. Nested scalar SELECT

 

This type of query works for the following EIQ Product Server configurations/connection types:

• Virtual data sources with an EIQ TurboAdapter 
• Direct connections to EIQ indexes

 

Examples:

SELECT colum1, column2, . FROM table WHERE filter_column operator (SELECT aggregatefunction(innerColum) FROM table)

Where colum1, column2 must be non-virtual  keys, 'innerColumn' must be a key but not global key, and the inner select MUST return only a scalar value (i.e. one single record with one column value).

 

SELECT employeeid FROM employees WHERE salary> (SELCT avg(salary) FROM employees)

 

Appendix B: Advanced Settings for Query Processing

In the EIQ Server Configuration Tool’s 'Advanced Settings' tab, users can set properties to change the query behavior for EIQ SuperAdpaters and EIQ Federation Servers.

 

AggregateAcrossDataSources option:

 

0 (default)        : Server doesn't perform aggregations on results from across data sources. It presents the results from the data sources in UNION fashion.

1                      : Server aggregates results from across the data sources.

 

Example:

SELECT "data source", COUNT(*) as cust_count , MIN(customer_age) as cust_min_age,  MAX(customer_age) as cust_max_age FROM MyTable WHERE cid < 1000

 

With AggregateAcrossDataSource set to 0

RowID	Data Source	cust_count	cust_min_age	cust_max_age
1	DS1	136	21	80
2	DS2	48	18	73

 

With AggregateAcrossDataSource set to 1

RowID	Data Source	cust_count	cust_min_age	cust_max_age
1		184	18	80

 

 

Note: It does not currently support these aggregate functions: AVG(), SST(), STD()