# 8. SQL Reference

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
SQL Reference
Datatypes
User Defined Types
XML Column Type
Identifier Case & Quoting
Wide Character Identifiers
Qualified Names
Literals, Brace Escapes
CREATE TABLE Statement
DROP TABLE Statement
CREATE INDEX Statement
DROP INDEX Statement
ALTER TABLE Statement
CREATE VIEW Statement
CREATE XML SCHEMA Statement
DROP XML SCHEMA Statement
Sequence Objects
INSERT Statement
UPDATE Statement
SELECT Statement
COMMIT WORK, ROLLBACK WORK Statement
CHECKPOINT, SHUTDOWN Statement
Stored Procedures as Views & Derived Tables
GRANT, REVOKE Statement
SET Statement
Best Effort Union
Standard and User-Defined Aggregate Functions
Virtuoso SQL Optimization
SQL Inverse Functions
SQL Grammar
Bitmap Indices

## 8.25. Best Effort Union

Virtuoso offers a SQL extension for an error tolerant UNION operation. The idea is that when querying multiple remote data sources in a single union construct some of the participating data sources may be allowed to fail while still returning a result for the successfully queried data sources.

The construct is introduced by the BEST keyword before UNION or UNION ALL. If a query expression of multiple unions has a single BEST keyword the entire union chain is considered as a best effort union. It is however recommended to have the BEST keyword in FROM of all the UNION keywords.

When a run time error occurs during the evaluation of a term in a best effort union the evaluation of the term is interrupted and the union continues with the next term. The partial result set that may have been generated by the failed term is considered when making the result.

Aliasing constant columns selected in the terms of the union by the names __SQLSTATE, __MESSAGE and __SET_NO retrieve individual error messages. If these are present and a union term encounters an error an extra row is generated for the term with all NULLs and the __SQLSTATE, __MESSAGE and __SET_NO columns set to the SQL state, SQL message and the union term number respectively. If neither of these is specified and a union term fails without producing any result rows the error will not be visible.

Note

No error encountered during a best effort union will be signaled in the normal fashion.

If a term of a best effort union meets the criteria for a pass through query on a specific remote database and a transaction error occurs when evaluating it, the transaction on the VDB is not aborted as would normally happen as a result of a VDB transaction error.

Thus if a pass through term dies of deadlock on its data source the query continues normally for other data sources referenced in the best effort union. The VDB will however get the transaction error when attempting to commit the transaction where the best effort union took place since the remote transaction branch will still be deadlocked.

Examples

    select 2222, 1 / 0 from sys_users best union all select key_id, 1 / (1000 - key_id)  from sys_keys;

The first term will immediately hit the /0 error and will produce no rows. The second term will produce a few rows for system tables but will hit /0 when getting to key_id 1001.

To see the errors one can write:

    select '00000' as __sqlstate, '' as __message, 2222, 1 / 0 from sys_users best union
    all select '00000' as __sqlstate, '', key_id, 1 / (1001 - key_id)  from sys_keys;

Note

The columns are named by the first term, hence the AS declaration in the second term is optional.

The BEST keyword does not affect the ALL or CORRESPONDING BY options of UNION.

Previous SET Statement | Chapter Contents | Next Standard and User-Defined Aggregate Functions