<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head profile="http://internetalchemy.org/2003/02/profile">
<link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" />
<link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" />
<meta name="dc.subject" content="SQL" />
<meta name="dc.subject" content="SQL Reference" />
<meta name="dc.subject" content="Select" />
<meta name="dc.subject" content="Update" />
<meta name="dc.subject" content="delete" />
<meta name="dc.subject" content="Select Statement" />
<meta name="dc.subject" content="SQL Syntax" />
<meta name="dc.subject" content="Syntax" />
<meta name="dc.title" content="8. SQL Reference" />
<meta name="dc.subject" content="8. SQL Reference" />
<meta name="dc.creator" content="OpenLink Software Documentation Team ; " />
<meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" />
<link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" />
<link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" />
<link rel="parent" href="sqlreference.html" title="Chapter Contents" />
<link rel="prev" href="anytimequeries.html" title="Anytime Queries" />
<link rel="next" href="aggregates.html" title="Standard and User-Defined Aggregate Functions" />
<link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="doc.css" />
<link rel="stylesheet" type="text/css" href="/doc/translation.css" />
<title>8. SQL Reference</title>
<meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" />
<meta name="author" content="OpenLink Software Documentation Team ; " />
<meta name="copyright" content="OpenLink Software, 1999 - 2009" />
<meta name="keywords" content="SQL; SQL Reference; Select; Update; delete; Select Statement; SQL Syntax; Syntax; " />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="besteffortunion" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>8. SQL Reference</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="sqlreference.html">Chapter Contents</a> | <a class="link" href="anytimequeries.html" title="Anytime Queries">Prev</a> | <a class="link" href="aggregates.html" title="Standard and User-Defined Aggregate Functions">Next</a>
</div>
</div>
<div id="currenttoc">
<form method="post" action="/doc/adv_search.vspx">
<div class="search">Keyword Search: <br />
<input type="text" name="q" /> <input type="submit" name="go" value="Go" />
</div>
</form>
<div>
<a href="http://www.openlinksw.com/">www.openlinksw.com</a>
</div>
<div>
<a href="http://docs.openlinksw.com/">docs.openlinksw.com</a>
</div>
<br />
<div>
<a href="index.html">Book Home</a>
</div>
<br />
<div>
<a href="contents.html">Contents</a>
</div>
<div>
<a href="preface.html">Preface</a>
</div>
<br />
<div class="selected">
<a href="sqlreference.html">SQL Reference</a>
</div>
<br />
<div>
<a href="sqlrefDATATYPES.html">Datatypes</a>
</div>
<div>
<a href="udt.html">User Defined Types</a>
</div>
<div>
<a href="sqlrefxmldatatype.html">XML Column Type</a>
</div>
<div>
<a href="catidentifiers.html">Identifier Case & Quoting</a>
</div>
<div>
<a href="wideidentifiers.html">Wide Character Identifiers</a>
</div>
<div>
<a href="QUALIFIEDNAMES.html">Qualified Names</a>
</div>
<div>
<a href="litsbraceescs.html">Literals, Brace Escapes</a>
</div>
<div>
<a href="CREATETABLE.html">CREATE TABLE Statement</a>
</div>
<div>
<a href="DROPTABLE.html">DROP TABLE Statement</a>
</div>
<div>
<a href="CREATEINDEX.html">CREATE INDEX Statement</a>
</div>
<div>
<a href="DROPINDEX.html">DROP INDEX Statement</a>
</div>
<div>
<a href="ALTERTABLE.html">ALTER TABLE Statement</a>
</div>
<div>
<a href="CREATEVIEW.html">CREATE VIEW Statement</a>
</div>
<div>
<a href="CREATEXMLSCHEMA.html">CREATE XML SCHEMA Statement</a>
</div>
<div>
<a href="DROPXMLSCHEMA.html">DROP XML SCHEMA Statement</a>
</div>
<div>
<a href="sequenceobjects.html">Sequence Objects</a>
</div>
<div>
<a href="insertSTMT.html">INSERT Statement</a>
</div>
<div>
<a href="updatestmt.html">UPDATE Statement</a>
</div>
<div>
<a href="SELECTSTMT.html">SELECT Statement</a>
</div>
<div>
<a href="COMMIT_ROLLBACK.html">COMMIT WORK, ROLLBACK WORK Statement</a>
</div>
<div>
<a href="CHECKPOINT.html">CHECKPOINT, SHUTDOWN Statement</a>
</div>
<div>
<a href="spasviewsandtables.html">Stored Procedures as Views & Derived Tables</a>
</div>
<div>
<a href="GRANT.html">GRANT, REVOKE Statement</a>
</div>
<div>
<a href="SETstmt.html">SET Statement</a>
</div>
<div>
<a href="anytimequeries.html">Anytime Queries</a>
</div>
<div class="selected">
<a href="besteffortunion.html">Best Effort Union</a>
</div>
<div>
<a href="aggregates.html">Standard and User-Defined Aggregate Functions</a>
</div>
<div>
<a href="sqloptimizer.html">Virtuoso SQL Optimization</a>
</div>
<div>
<a href="sqlinverse.html">SQL Inverse Functions</a>
</div>
<div>
<a href="GRAMMAR.html">SQL Grammar</a>
</div>
<div>
<a href="BITMAPINDICES.html">Bitmap Indices</a>
</div>
<div>
<a href="transitivityinsQL.html">Transitivity in SQL</a>
</div>
<div>
<a href="sqlreffastphrasematch.html">Fast Phrase Match Processor</a>
</div>
<br />
</div>
<div id="text">
<a name="besteffortunion" />
<h2>8.26. Best Effort Union</h2>
<p>
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.
</p>
<p>
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.
</p>
<p>
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.
</p>
<p>
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.
</p>
<div class="note">
<div class="notetitle">Note</div>
<p>No error encountered during a best effort union will be signalled in the normal fashion.
</p>
</div>
<p>
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.
</p>
<p>
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.
</p>
<a name="" />
<div class="example">
<div class="exampletitle">Examples</div>
<div>
<pre class="programlisting">
select 2222, 1 / 0 from sys_users best union all select key_id, 1 / (1000 - key_id) from sys_keys;
</pre>
</div>
<p>
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.
</p>
<p>
To see the errors one can write:
</p>
<div>
<pre class="programlisting">
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;
</pre>
</div>
</div>
<div class="note">
<div class="notetitle">Note</div>
<p>The columns are named by the first term, hence the AS declaration in
the second term is optional.
</p>
</div>
<p>
The BEST keyword does not affect the ALL or CORRESPONDING BY options of UNION.
</p>
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="anytimequeries.html" title="Anytime Queries">Previous</a>
<br />Anytime Queries</td>
<td align="center" width="34%">
<a href="sqlreference.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="aggregates.html" title="Standard and User-Defined Aggregate Functions">Next</a>
<br />Standard and User-Defined Aggregate Functions</td>
</tr>
</table>
</div>
<div id="footer">
<div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div>
<div id="validation">
<a href="http://validator.w3.org/check/referer">
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
</a>
<a href="http://jigsaw.w3.org/css-validator/">
<img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" />
</a>
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
71d40963b505df4524269198e237b3e3
>
files
>
62
