<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.5//EN" "http://www.oasis-open.org/docbook/xml/4.5/docbookx.dtd">
<chapter id="sql_support">
  <title>DML Support</title>
  <para>
    Teiid supports SQL for issuing queries and for defining view
    transformations; see also <link linkend="procedure_language">Procedure Language</link>
    for how SQL is used in virtual procedures and update procedures.
  </para>
  <para>
    Teiid provides nearly all of the functionality of SQL-92 DML.
    SQL-99 and later features are constantly being added based upon
    community need. The following does not attempt to cover SQL
    exhaustively, but rather highlights SQL's usage within Teiid. See
    the
    <link linkend="grammar">grammar</link>
    for the exact form of SQL accepted by Teiid.
  </para>
  <section id="identifiers">
    <title>Identifiers</title>
    <para> SQL commands contain references to tables and columns. These
      references are in the form of identifiers, which uniquely identify
      the tables and columns in the context of the command. All queries
      are processed in the context of a virtual database, or VDB.
      Because information can be federated across multiple sources,
      tables and columns must be scoped in some manner to avoid
      conflicts. This scoping is provided by schemas, which contain the
      information for each data source or set of views.</para>
    <itemizedlist>
      <para>Fully-qualified table and column names are of
        the following form, where the separate 'parts' of the identifier
        are delimited by periods.</para>
      <listitem>
        <para>TABLE: &lt;schema_name&gt;.&lt;table_spec&gt;</para>
      </listitem>
      <listitem>
        <para>COLUMN: &lt;schema_name&gt;.&lt;table_spec&gt;.&lt;column_name&gt;</para>
      </listitem>
    </itemizedlist>
    <itemizedlist>
      <para><emphasis>Syntax Rules:</emphasis></para>
      <listitem>
        <para>Identifiers can consist of alphanumeric characters,
          or the underscore (_) character, and must begin with an alphabetic
          character. Any Unicode character may be used in an identifier.
        </para>
      </listitem>
      <listitem>
        <para>Identifiers in double quotes can have any contents.  
        The double quote character can it's be escaped with an additional double quote. e.g. "some "" id"
        </para>
      </listitem>
      <listitem>
        <para> Because different data sources organize tables in different
          ways, some prepending catalog or schema or user information,
          Teiid allows table specification to be a dot-delimited construct.
          <note>
	        <para>When a table specification contains a dot resolving will allow for the match of
	        a partial name against any number of the end segments in the name.  e.g. a table with the 
	        fully-qualified name vdbname."sourcescema.sourcetable" would match the partial name
	        sourcetable.
	        </para>
          </note>
        </para>
      </listitem>
      <listitem>
        <para> Columns, schemas, and aliases identifiers cannot contain a dot.  
        </para>
      </listitem>
      <listitem>
        <para> Identifiers, even when quoted, are not case-sensitive in Teiid.</para>
      </listitem>      
    </itemizedlist>
    <itemizedlist>
      <para> Some examples of valid fully-qualified table identifiers are:
      </para>
      <listitem>
        <para>MySchema.Portfolios
        </para>
      </listitem>
      <listitem>
        <para>"MySchema.Portfolios"
        </para>      
      </listitem>
      <listitem>
        <para>MySchema.MyCatalog.dbo.Authors
        </para>
      </listitem>
    </itemizedlist>
    <itemizedlist>
      <para>Some examples of valid fully-qualified column identifiers
        are:</para>
      <listitem>
        <para>MySchema.Portfolios.portfolioID
        </para>
      </listitem>
      <listitem>
        <para>"MySchema.Portfolios"."portfolioID" 
        </para>
      </listitem>
      <listitem>
        <para>MySchema.MyCatalog.dbo.Authors.lastName
        </para>
      </listitem>
    </itemizedlist>
    <para> Fully-qualified identifiers can always be used in SQL commands.
      Partially- or unqualified forms can also be used, as long as the
      resulting names are unambiguous in the context of the command.
      Different forms of qualification can be mixed in the same query.
    </para>
    <section>
    	<title>Reserved Words</title>
    	<para>Teiid's reserved words include the standard SQL 2003 Foundation, SQL/MED, and SQL/XML reserved words, as well as Teiid specific words such as BIGINTEGER, BIGDECIMAL, or MAKEDEP. 
    	See the <xref linkend="grammar"/> TOKENS section for all reserved words.  They will appear as 'SMALLINT: "smallint"' where the quoted string is the actual lexical form. 
    	 </para>
    </section>
  </section>
  <section id="expressions">
    <title>Expressions</title>
    <para> Identifiers, literals, and functions can be combined into
      expressions. Expressions can be used almost anywhere in a query --
      SELECT, FROM (if specifying join criteria), WHERE, GROUP BY, HAVING, or ORDER BY.</para>
    <itemizedlist>
      <para>Teiid supports the following types of expressions:
      </para>
      <listitem>
        <para><link linkend="columnidentifiers">Column identifiers</link></para>
      </listitem>
      <listitem>
        <para><link linkend="literals">Literals</link></para>
      </listitem>
      <listitem>
        <para><link linkend="scalar_functions">Scalar functions</link></para>
      </listitem>
      <listitem>
        <para><link linkend="aggregate_functions">Aggregate functions</link></para>
      </listitem>
      <listitem>
        <para><link linkend="window_functions">Window functions</link></para>
      </listitem>
      <listitem>
        <para><link linkend="case">Case and searched case</link></para>
      </listitem>
      <listitem>
        <para><link linkend="scalar_subqueries">Scalar subqueries</link></para>
      </listitem>
      <listitem>
        <para><link linkend="parameter_references">Parameter references</link></para>
      </listitem>
    </itemizedlist>
    <section id="columnidentifiers">
      <title>Column Identifiers</title>
      <para>
        Column identifiers are used to specify the output columns in SELECT
        statements, the columns and their values for INSERT and UPDATE
        statements, and criteria used in WHERE and FROM clauses. They are
        also used in GROUP BY, HAVING, and ORDER BY clauses. The syntax for
        column identifiers was defined in the
        <link linkend="identifiers">Identifiers</link>
        section above.
      </para>
    </section>
    <section id="literals">
      <title>Literals</title>
      <para>
      Literal values represent fixed values. These can any of the 'standard' <link linkend="datatypes">data types</link>. 
      </para>
      <itemizedlist>
        <para>Syntax Rules:</para>
        <listitem>
          <para>Integer values will be assigned an integral data type big enough to hold the value (integer, long, or biginteger).</para>
        </listitem>
        <listitem>
          <para>Floating point values will always be parsed as a double.</para>
        </listitem>
        <listitem>
          <para>The keyword 'null' is used to represent an absent or unknown value and is inherently untyped. In many cases, a null literal value will be assigned an implied type based on context. For example, in the function '5 + null', the null value will be assigned the type 'integer' to match the type of the value '5'. A null literal used in the SELECT clause of a query with no implied context will be assigned to type 'string'.</para>
        </listitem>
      </itemizedlist>
      <itemizedlist>
        <para>Some examples of simple literal values are:</para>
        <listitem>
          <para><literal>‘abc’</literal></para>
        </listitem>
        <listitem>
          <para><literal>‘isn’’t true’</literal> - use an extra single tick to escape a tick in a string with single ticks</para>
        </listitem>
        <listitem>
          <para><literal>5</literal></para>
        </listitem>
        <listitem>
          <para><literal>-37.75e01</literal> - scientific notation</para>
        </listitem>
        <listitem>
          <para><literal>100.0</literal> - parsed as double</para>
        </listitem>
        <listitem>
          <para><literal>true</literal></para>
        </listitem>
        <listitem>
          <para><literal>false</literal></para>
        </listitem>
        <listitem>
          <para><literal>'\u0027'</literal> - unicode character</para>
        </listitem>
      </itemizedlist>
    </section>
    <section id="aggregate_functions">
      <title>Aggregate Functions</title>
      <para>Aggregate functions take sets of values from a group produced by an explicit or implicit GROUP BY and return a single scalar value computed from the group.</para>
      <itemizedlist>
        <para>Teiid supports the following aggregate functions:</para>
        <listitem>
          <para>COUNT(*) – count the number of values (including nulls and duplicates) in a group</para>
        </listitem>
        <listitem>
          <para>COUNT(x) – count the number of values (excluding nulls) in a group</para>
        </listitem>
        <listitem>
          <para>SUM(x) – sum of the values (excluding nulls) in a group</para>
        </listitem>
        <listitem>
          <para>AVG(x) – average of the values (excluding nulls) in a group</para>
        </listitem>
        <listitem>
          <para>MIN(x) – minimum value in a group (excluding null)</para>
        </listitem>
        <listitem>
          <para>MAX(x) – maximum value in a group (excluding null)</para>
        </listitem>
        <listitem>
          <para>ANY(x)/SOME(x) – returns TRUE if any value in the group is TRUE (excluding null)</para>
        </listitem>
        <listitem>
          <para>EVERY(x) – returns TRUE if every value in the group is TRUE (excluding null)</para>
        </listitem>
        <listitem>
          <para>VAR_POP(x) – biased variance (excluding null) logically equals (sum(x^2) - sum(x)^2/count(x))/count(x); returns a double; null if count = 0</para>
        </listitem>
        <listitem>
          <para>VAR_SAMP(x) – sample variance (excluding null) logically equals (sum(x^2) - sum(x)^2/count(x))/(count(x) - 1); returns a double; null if count &lt; 2</para>
        </listitem>
        <listitem>
          <para>STDDEV_POP(x) – standard deviation (excluding null) logically equals SQRT(VAR_POP(x))</para>
        </listitem>
        <listitem>
          <para>STDDEV_SAMP(x) – sample standar deviation (excluding null) logically equals SQRT(VAR_SAMP(x))</para>
        </listitem>
        <listitem>
          <para>TEXTAGG(FOR (expression [as name], ... [DELIMITER char] [QUOTE char] [HEADER] [ENCODING id]
          <link linkend="orderby_clause">[ORDER BY ...]</link>) –  CSV text aggregation of all expressions in each row of a group.
          When DELIMITER is not specified, by default comma(,) is used as delimiter. Double quotes(") is the default quote character.  
          Use QUOTE to specify a differnt value.  All non-null values will be quoted. 
          If HEADER is specified, the result contains the header row as the first line - the header line will be present even if there are no rows in a group. 
          This aggregation returns a blob. 
          </para>
        </listitem>                
        <listitem>
          <para>XMLAGG(xml_expr <link linkend="orderby_clause">[ORDER BY ...]</link>) – xml concatination of all xml expressions in a group (excluding null).  The ORDER BY clause cannot reference alias names or use positional ordering.</para>
        </listitem>                
      </itemizedlist>
      <itemizedlist>
        <para>Syntax Rules:
        </para>
        <listitem>
          <para>Some aggregate functions may contain a keyword 'DISTINCT' before the expression, indicating that duplicate expression values should be ignored. DISTINCT is not allowed in COUNT(*) and is not meaningful in MIN or MAX (result would be unchanged), so it can be used in COUNT, SUM, and AVG.
          </para>
        </listitem>
        <listitem>
          <para>Aggregate functions cannot be used in FROM, GROUP BY, or WHERE clauses without an intervening query expression.
          </para>
        </listitem>
        <listitem>
          <para>Aggregate functions cannot be nested within another aggregate function without an intervening query expression.
          </para>
        </listitem>
        <listitem>
          <para>Aggregate functions may be nested inside other functions.
          </para>
        </listitem>
        <listitem>
          <para>Any aggregate function may take an optional FILTER clasue of the form <synopsis label="Usage">FILTER ( WHERE condition )</synopsis>.  The condition may be any boolean value expression that does not contain a subquery or a correlated variable.
	      The filter will logically be evaluated for each row prior to the grouping operation.  If false the aggregate function will not accumulate a value for the given row.          
          </para>
        </listitem>
      </itemizedlist>
      <para>
      For more information on aggregates, see the sections on GROUP BY or HAVING.
      </para>
    </section>
    <section id="window_functions">
      <title>Window functions</title>
      <para>Teiid supports ANSI SQL 2003 window functions.  A window function allows an aggregrate function to be applied to a subset of the result set, without the need for a GROUP BY clause.  
      A window function is similar to an aggregate function, but requires the use of an OVER clause or window specification.
      </para>
      <para>
        Usage:
        <synopsis label="Usage">aggregate|ranking OVER ([PARTION BY expression [, expression]*] <link linkend="orderby_clause">[ORDER BY ...]</link>)</synopsis>
        aggregate can be any <xref linkend="aggregate_functions"/>.  Ranking can be one of ROW_NUMBER(), RANK(), DENSE_RANK().   
      </para>
      <itemizedlist>
        <para>Syntax Rules:
        </para>
        <listitem>
	      <para>Window functions can only appear in the SELECT and ORDER BY clauses of a query expression.
	      </para>
        </listitem>
        <listitem>
	      <para>Window functions cannot be nested in one another.
	      </para>
        </listitem>
        <listitem>
	      <para>Partitioning and order by expressions cannot contain subqueries or outer references.
	      </para>
        </listitem>
        <listitem>
	      <para>The ranking (ROW_NUMBER, RANK, DENSE_RANK) functions require the use of the window specification ORDER BY clause.
	      </para>
        </listitem>
        <listitem>
	      <para>An XMLAGG ORDER BY clause cannot be used when windowed.
	      </para>
        </listitem>        
        <listitem>
	      <para>The window specification ORDER BY clause cannot reference alias names or use positional ordering.
	      </para>
        </listitem>
        <listitem>
	      <para>Windowed aggregates may not use DISTINCT if the window specification is ordered.
	      </para>
        </listitem>
	  </itemizedlist>
	  <section>
	  	<title>Function Definitions</title>
	  	<itemizedlist>
	        <listitem>
	          <para>ROW_NUMBER() – functional the same as COUNT(*) with the same window specification.  Assigns a number to each row in a partition starting at 1.</para>
	        </listitem>
	        <listitem>
	          <para>RANK() – Assigns a number to each unique ordering value within each partition starting at 1, such that the next rank is equal to the count of prior rows.</para>
	        </listitem>
	        <listitem>
	          <para>DENSE_RANK() – Assigns a number to each unique ordering value within each partition starting at 1, such that the next rank is sequential.</para>
	        </listitem>
		</itemizedlist>	  	
	  </section>
	  <section>
	  	<title>Processing</title>
	  	<para>Window functions are logically processed just before creating the output from the SELECT clause. 	Window functions can use nested aggregates if a GROUP BY clause is present.
	  	The is no guarenteed affect on the output ordering from the presense of window functions.  The SELECT statement must have an ORDER BY clause to have a predictable ordering.
	  	</para><para>
	  	Teiid will process all window functions with the same window specification together.  In general a full pass over the row values coming into the SELECT clause will be required for each unique window specification.
	  	For each window specification the values will be grouped according to the PARTITION BY clause.  If no PARTITION BY clause is specified, then the entire input is treated as a single partition.
	  	The output value is determined based upon the current row value, it's peers (that is rows that are the same with respect to their ordering), and all prior row values based upon ordering in the partition.
	  	The ROW_NUMBER function will assign a unique value to every row regardless of the number of peers.
	  	</para>
	  	<example>
          <title>Example Windowed Results</title>
          <programlisting language="SQL">SELECT name, salary, max(salary) over (partition by name) as max_sal, 
          rank() over (order by salary) as rank, dense_rank() over (order by salary) as dense_rank, 
          row_number() over (order by salary) as row_num FROM employees</programlisting>
          <informaltable frame="all">
			<tgroup cols="6">
				<thead>
					<row>
						<entry>
							<para>name</para>
						</entry>
						<entry>
							<para>salary</para>
						</entry>
						<entry>
							<para>max_sal</para>
						</entry>
						<entry>
							<para>rank</para>
						</entry>
						<entry>
							<para>dense_rank</para>
						</entry>
						<entry>
							<para>row_num</para>
						</entry>
					</row>
				</thead>
				<tbody>
					<row>
					<entry><para>John</para></entry><entry><para>100000</para></entry><entry><para>100000</para></entry><entry><para>2</para></entry><entry><para>2</para></entry><entry><para>2</para></entry>
					</row>
					<row>
					<entry><para>Henry</para></entry><entry><para>50000</para></entry><entry><para>100000</para></entry><entry><para>5</para></entry><entry><para>4</para></entry><entry><para>5</para></entry>
					</row>
					<row>
					<entry><para>John</para></entry><entry><para>60000</para></entry><entry><para>60000</para></entry><entry><para>3</para></entry><entry><para>3</para></entry><entry><para>3</para></entry>
					</row>
					<row>
					<entry><para>Suzie</para></entry><entry><para>60000</para></entry><entry><para>150000</para></entry><entry><para>3</para></entry><entry><para>3</para></entry><entry><para>4</para></entry>
					</row>
					<row>
					<entry><para>Suzie</para></entry><entry><para>150000</para></entry><entry><para>150000</para></entry><entry><para>1</para></entry><entry><para>1</para></entry><entry><para>1</para></entry>
					</row>
				</tbody>
				</tgroup>
			</informaltable>						
        </example>
	  </section>
    </section>
    <section id="case">
      <title>Case and searched case</title>
      <para>
      Teiid supports two forms of the CASE expression which allows conditional logic in a scalar expression.
      </para>
      <itemizedlist>
        <para>Supported forms:
        </para>
        <listitem>
          <para>CASE &lt;expr&gt; ( WHEN &lt;expr&gt; THEN &lt;expr&gt;)+ [ELSE expr] END
          </para>
        </listitem>
        <listitem>
          <para>CASE ( WHEN &lt;criteria&gt; THEN &lt;expr&gt;)+ [ELSE expr] END
          </para>
        </listitem>
      </itemizedlist>
      <para>
        Each form allows for an output based on conditional logic. The first
        form starts with an initial expression and evaluates WHEN expressions
        until the values match, and outputs the THEN expression. If no WHEN is
        matched, the ELSE expression is output. If no WHEN is matched and no
        ELSE is specified, a null literal value is output. The second form
        (the searched case expression) searches the WHEN clauses, which
        specify an arbitrary criteria to evaluate. If any criteria evaluates
        to true, the THEN expression is evaluated and output. If no WHEN is
        true, the ELSE is evaluated or NULL is output if none exists.</para>
    </section>
    <section id="scalar_subqueries">
      <title>Scalar subqueries</title>
      <para>
        Subqueries can be used to produce a single scalar value in the SELECT,
        WHERE, or HAVING clauses only. A scalar subquery must have a single
        column in the SELECT clause and should return either 0 or 1 row. If no
        rows are returned, null will be returned as the scalar subquery value.
        For other types of subqueries, see the
        <link linkend="subqueires">Subqueries</link>
        section below.</para>
    </section>
    <section id="parameter_references">
      <title>Parameter references</title>
      <para> Parameters are specified using a '?' symbol. Parameters may
        only be used with PreparedStatement or CallableStatements in JDBC.
        Each parameter is linked to a value specified by 1-based index in
        the JDBC API.</para>
    </section>
  </section>
  <section id="criteria">
    <title>Criteria</title>
    <itemizedlist>
      <para>Criteria may be:
      </para>
      <listitem>
        <para>Predicates that evaluate to true or false
        </para>      
      </listitem>
      <listitem>
        <para>Logical criteria that combines criteria (AND, OR, NOT)
        </para>      
      </listitem>
      <listitem>
        <para>A value expression with type boolean
        </para>      
      </listitem>
    </itemizedlist>
    <itemizedlist>
      <para>Usage:
      </para>
      <listitem>
        <para><synopsis label="Usage">criteria AND|OR criteria</synopsis>
        </para>
      </listitem>
      <listitem>
        <para><synopsis label="Usage">NOT criteria</synopsis>
        </para>
      </listitem>
      <listitem>
        <para><synopsis label="Usage">(criteria)</synopsis>
        </para>
      </listitem>
      <listitem>
        <para><synopsis label="Usage">expression (=|&lt;&gt;|!=|&lt;|>|&lt;=|>=) (expression|((ANY|ALL|SOME) subquery))</synopsis>
        </para>
      </listitem>
      <listitem>
        <para><synopsis label="Usage">expression [NOT] IS NULL</synopsis>
        </para>
      </listitem>
      <listitem>
        <para><synopsis label="Usage">expression [NOT] IN (expression[,expression]*)|subquery</synopsis>
        </para>
      </listitem>
      <listitem>
        <para><synopsis label="Usage">expression [NOT] LIKE pattern [ESCAPE char]</synopsis>
        Matches the string expression against the given string pattern.  
        The pattern may contain % to match any number of characters and _ to match any single character.  The escape character can be used to escape the match characters % and _.</para>
      </listitem>
      <listitem>
        <para><synopsis label="Usage">expression [NOT] SIMILAR TO pattern [ESCAPE char]</synopsis>
        SIMILAR TO is a cross between LIKE and standard regular expression syntax.  % and _ are still used, rather than .* and . respectively.</para>
        <note><para>Teiid does not exhaustively validate SIMILAR TO pattern values.  Rather the pattern is converted to an equivalent regular expression.  
        Care should be taken not to rely on general regular expression features when using SIMILAR TO.  If additional features are needed, then LIKE_REGEX should be used.
        Usage of a non-literal pattern is discouraged as pushdown support is limited.</para></note>
      </listitem>
      <listitem>
        <para><synopsis label="Usage">expression [NOT] LIKE_REGEX pattern</synopsis>
        LIKE_REGEX allows for standard regular expression syntax to be used for matching.  This differs from SIMILAR TO and LIKE in that the escape character is no longer used (\ is already the 
        standard escape mechansim in regular expressions and % and _ have no special meaning.  
        The runtime engine uses the JRE implementation of regular expressions - see the <ulink url="http://download.oracle.com/javase/6/docs/api/java/util/regex/Pattern.html">java.util.regex.Pattern</ulink> class for details.
        <note><para>Teiid does not exhaustively validate LIKE_REGEX pattern values.  It is possible to use JRE only regular expression features that are not specified by the SQL specification.  Additional not all sources support
        the same regular expression flavor or extensions.  Care should be taken in pushdown situations to ensure that the pattern used will have same meaning in Teiid and across all applicable sources.</para></note>
        </para>
      </listitem>
      <listitem>
        <para><synopsis label="Usage">EXISTS(subquery)</synopsis>
        </para>
      </listitem>
      <listitem>
        <para><synopsis label="Usage">expression [NOT] BETWEEN minExpression AND maxExpression</synopsis>
        Teiid converts BETWEEN into the equivalent form expression &gt;= minExpression AND expression &lt;= maxExpression
        </para>
      </listitem>
      <listitem>
        <para><synopsis label="Usage">expression</synopsis>
        Where expression has type boolean.
        </para>
      </listitem>
    </itemizedlist>
    <itemizedlist>
      <para>Syntax Rules:
      </para>
      <listitem>
        <para>The precedence ordering from lowest to highest is comparison, NOT, AND, OR 
        </para>
      </listitem>
      <listitem>
        <para>Criteria nested by parenthesis will be logically evaluated prior to evaluating the parent criteria.
        </para>
      </listitem>
    </itemizedlist>
    <itemizedlist>
      <para>Some examples of valid criteria are:
      </para>
      <listitem>
        <para>(balance &gt; 2500.0)
        </para>
      </listitem>
      <listitem>
        <para>100*(50 - x)/(25 - y) &gt; z
        </para>
      </listitem>
      <listitem>
        <para>concat(areaCode,concat('-',phone)) LIKE '314%1'
        </para>
      </listitem>
    </itemizedlist>
    <note>
      <title>Comparing null Values</title>
      <para>Null values represent an unknown value. Comparison with a null value will evaluate to 'unknown', which can never be true even if 'not' is used.</para>
    </note>
  </section>
  <section id="sql_commands">
    <title>SQL Commands</title>
    <para>There are 4 basic commands for manipulating data in SQL, corresponding to the CRUD create, read, update, and delete operations: INSERT, SELECT, UPDATE, and DELETE. In addition, procedures can be executed using the EXECUTE command or through a <link linkend="procedural_relational">procedural relational command</link>.</para>
    <section id="select_command">
      <title>SELECT Command</title>
      <para>The SELECT command is used to retrieve records any number of relations.</para> 
      <itemizedlist>
        <para>A SELECT command has a number of clauses:</para>
        <listitem>  
          <para>
            <link linkend="with_clause">WITH ...</link>
          </para>
        </listitem>
        <listitem>  
          <para>
            <link linkend="select_clause">SELECT ...</link>
          </para>
        </listitem>
        <listitem>  
          <para>
            <link linkend="from_clause">[FROM ...]</link>
          </para>
        </listitem>
        <listitem>  
          <para>
            <link linkend="where_clause">[WHERE ...]</link>
          </para>
        </listitem>
        <listitem>  
          <para>
            <link linkend="groupby_clause">[GROUP BY ...]</link>
          </para>
        </listitem>
        <listitem>  
          <para>
            <link linkend="having_clause">[HAVING ...]</link>
          </para>
        </listitem>
        <listitem>  
          <para>
            <link linkend="orderby_clause">[ORDER BY ...]</link>
          </para>
        </listitem>
        <listitem>  
          <para>
            <link linkend="limit_clause">[(LIMIT ...) | ([OFFSET ...] [FETCH ...])]</link>
          </para>
        </listitem>
        <listitem>  
          <para>
            <link linkend="option_clause">[OPTION ...]</link>
          </para>
        </listitem>
      </itemizedlist>
      <para>
        All of these clauses other than OPTION are defined by the SQL specification. The specification also specifies the order that these clauses will be logically processed. Below is the processing order where each stage passes a set of rows to the following stage. Note that this processing model is logical and does not represent the way any actual database engine performs the processing, although it is a useful model for understanding questions about SQL.
      </para>
      <itemizedlist>
        <listitem>
          <para>WITH stage - gathers all rows from all with items in the order listed.  Subsequent with items and the main query can reference the a with item as if it is a table.
          </para>
        </listitem>
        <listitem>
          <para>FROM stage - gathers all rows from all tables involved in the query and logically joins them with a Cartesian product, producing a single large table with all columns from all tables. Joins and join criteria are then applied to filter rows that do not match the join structure.
          </para>
        </listitem>
        <listitem>
          <para>WHERE stage - applies a criteria to every output row from the FROM stage, further reducing the number of rows.
          </para>
        </listitem>
        <listitem>
          <para>GROUP BY stage - groups sets of rows with matching values in the group by columns.
          </para>
        </listitem>
        <listitem>
          <para>HAVING stage - applies criteria to each group of rows. Criteria can only be applied to columns that will have constant values within a group (those in the grouping columns or aggregate functions applied across the group).
          </para>
        </listitem>
        <listitem>
          <para>SELECT stage - specifies the column expressions that should be returned from the query. Expressions are evaluated, including aggregate functions based on the groups of rows, which will no longer exist after this point. The output columns are named using either column aliases or an implicit name determined by the engine. If SELECT DISTINCT is specified, duplicate removal will be performed on the rows being returned from the SELECT stage.
          </para>
        </listitem>
        <listitem>
          <para>ORDER BY stage - sorts the rows returned from the SELECT stage as desired. Supports sorting on multiple columns in specified order, ascending or descending. The output columns will be identical to those columns returned from the SELECT stage and will have the same name.
          </para>
        </listitem>
        <listitem>
          <para>LIMIT stage - returns only the specified rows (with skip and limit values).
          </para>
        </listitem>
      </itemizedlist>
      <para> This model can be used to understand many questions about SQL.
        For example, columns aliased in the SELECT clause can only be
        referenced by alias in the ORDER BY clause. Without knowledge of the
        processing model, this can be somewhat confusing. Seen in light of
        the model, it is clear that the ORDER BY stage is the only stage
        occurring after the SELECT stage, which is where the columns are
        named. Because the WHERE clause is processed before the SELECT, the
        columns have not yet been named and the aliases are not yet known.
      </para>
      <note>
        <para>
	      The explicit table syntax <code>TABLE x</code> may be used as a shortcut for <code>SELECT * FROM x</code>.
	    </para>
      </note>
    </section>
    <section id="insert_command">
      <title>INSERT Command</title>
      <para>The INSERT command is used to add a record to a table.</para>
      <itemizedlist>
        <para>Example Syntax</para>
        <listitem>
          <para>INSERT INTO table (column,...) VALUES (value,...)
          </para>
        </listitem>
        <listitem>
          <para>INSERT INTO table (column,...) query
          </para>
        </listitem>
      </itemizedlist>
    </section>
    <section id="update_command">
      <title>UPDATE Command</title>
      <para>The UPDATE command is used to modify records in a table. The operation may result in 1 or more records being updated, or in no records being updated if none match the criteria.</para>
      <itemizedlist>
        <para>Example Syntax</para>
        <listitem>
          <para>UPDATE table SET (column=value,...) [WHERE criteria]
          </para>
        </listitem>
      </itemizedlist>
    </section>
    <section id="delete_command">
      <title>DELETE Command</title>
      <para>The DELETE command is used to remove records from a table. The operation may result in 1 or more records being deleted, or in no records being deleted if none match the criteria. </para>
      <itemizedlist>
        <para>Example Syntax</para>
        <listitem>
          <para>DELETE FROM table [WHERE criteria]
          </para>
        </listitem>
      </itemizedlist>
    </section>
    <section id="exec_command">
      <title>EXECUTE Command</title>
      <para>The EXECUTE command is used to execute a procedure, such as a virtual procedure or a stored procedure. Procedures may have zero or more scalar input parameters. The return value from a procedure is a result set, the same as is returned from a SELECT. Note that EXEC or CALL can be used as a short form of this command. 
      </para>
      <itemizedlist>
        <para>Example Syntax</para>
        <listitem>
          <para>EXECUTE proc()
          </para>
        </listitem>
        <listitem>
          <para>EXECUTE proc(value, ...)
          </para>
        </listitem>
        <listitem>
          <para>EXECUTE proc(name1=>value1,name4=>param4, ...) - named parameter syntax
          </para>
        </listitem>
      </itemizedlist>
      <itemizedlist>
        <para>Syntax Rules:
        </para>
        <listitem>
          <para>The default order of parameter specification is the same
          as how they are defined in the procedure definition.
          </para>
        </listitem>
        <listitem>
          <para>You can specify the parameters in any order by name. Parameters that are have
          default values and/or are nullable in the metadata, can be omitted
          from the named parameter call and will have the appropriate value
          passed at runtime.
          </para>
        </listitem>
        <listitem>
          <para>If the procedure does not return a result set, the values from the RETURN, OUT, and IN_OUT parameters will be returned as a single row when used as an inline view query.
          </para>
        </listitem>
      </itemizedlist>
    </section>
    <section id="procedural_relational">
	  <title>Procedural Relational Command</title>
      <para> Procedural relational commands use the syntax of a SELECT to
		emulate an EXEC. In a procedural relational command a procedure group
		names is used in a FROM clause in place of a table. That procedure
		will be executed in place of a normal table access if all of the
		necessary input values can be found in criteria against the procedure.  
		Each combination of input values found in the criteria results in an execution of the procedure.
	  </para>
	  <para>
	    <itemizedlist>
          <para>Example Syntax</para>
          <listitem>
            <para>select * from proc
            </para>
          </listitem>
          <listitem>
            <para>select output_param1, output_param2 from proc where input_param1 = 'x'
            </para>
          </listitem>
          <listitem>
            <para>select output_param1, output_param2 from proc, table where input_param1 = table.col1 and input_param2 = table.col2
            </para>
          </listitem>
        </itemizedlist>
	    <itemizedlist>
          <para>Syntax Rules:</para>
          <listitem>
	        <para>The procedure as a table projects the same columns as an exec with the addition of the input parameters.
		      For procedures that do not return a result set, IN_OUT columns will be projected as two columns, one that represents
		      the output value and one named {column name}_IN that represents the
		      input of the parameter.</para>
          </listitem>
          <listitem>
            <para>Input values are passed via criteria. Values can be passed by '=','is null', or 'in' predicates.  Disjuncts are not allowed.  
            It is also not possible to pass the value of a non-comparable column through an equality predicate.
            </para>
          </listitem>
          <listitem>
            <para>The procedure view automatically has an access pattern on its IN and IN_OUT parameters which
            allows it to be planned correctly as a dependent join when necessary or fail when sufficient criteria cannot be found.
            </para>
          </listitem>
          <listitem>
            <para>Procedures containing duplicate names between the parameters (IN, IN_OUT, OUT, RETURN) and result set columns 
            cannot be used in a procedural relational command.
            </para>
          </listitem>
          <listitem>
            <para>Default values for IN, IN_OUT parameters are not used if there is no criteria present for a given input.  Default values are
            only valid for <link linkend="exec_command">named procedure syntax</link>.
            </para>
          </listitem>
        </itemizedlist>
      </para>
      <note>
		<title>Multiple Execution</title>
		  <para>The usage of 'in' or join criteria can result in
				the procedure being executed multiple times.</para>
	  </note>
	  <note>
	  	<title>Alternative Syntax</title>
	  	<para>
	  		None of issues listed in the syntax rules above exist if a <link linkend="nested_table">nested table reference</link> is used. 
	  	</para>
	  </note>
    </section>
  </section>
  <section id="set_operations">
    <title>Set Operations</title>
    <para>Teiid supports the UNION, UNION ALL, INTERSECT, EXCEPT set operation as a way of combining the results of query expressions.</para>
    <para>
      Usage:
      <synopsis label="Usage">queryExpression (UNION|INTERSECT|EXCEPT) [ALL] queryExpression [ORDER BY...]</synopsis>
    </para>
    <itemizedlist>
      <para>Syntax Rules:
      </para>
      <listitem>
        <para>The output columns will be named by the output columns of the first set operation branch. 
        </para>      
      </listitem>
      <listitem>
        <para>Each SELECT must have the same number of output columns and compatible data types for each relative column. Data type conversion will be performed if data types are inconsistent and implicit conversions exist.
        </para>
      </listitem>
      <listitem>
        <para>If UNION, INTERSECT, or EXCEPT is specified without all, then the output columns must be comparable types.
        </para>
      </listitem>
      <listitem>
        <para>INTERSECT ALL, and EXCEPT ALL are currently not supported.</para>
      </listitem>
    </itemizedlist>
  </section>
  <section id="subqueries">
    <title>Subqueries</title>
    <para>A subquery is a SQL query embedded within another SQL query. The query containing the subquery is the outer query.</para>
    <itemizedlist>
      <para>Supported subquery types:
      </para>
      <listitem>
        <para>Scalar subquery - a subquery that returns only a single column with a single value.  Scalar subqueries are a type of expression and can be used where single valued expressions are expected.
        </para>
      </listitem>
      <listitem>
        <para>Correlated subquery - a subquery that contains a column reference to from the outer query.
        </para>
      </listitem>
      <listitem>
        <para>Uncorrelated subquery - a subquery that contains no references to the outer sub-query.
        </para>
      </listitem>
    </itemizedlist>
    <section id="from_subquery">
      <title>Inline views</title>
      <para>Subqueries in the FROM clause of the outer query (also known as
        "inline views") can return any number of rows and columns. This type
        of subquery must always be given an alias.  An inline view is nearly identical to a traditional view. See also <xref linkend="with_clause"/>.</para>
      <example>
        <title>Example Subquery in FROM Clause (Inline View)</title>
        <programlisting language="SQL">SELECT a FROM (SELECT Y.b, Y.c FROM Y WHERE Y.d = ‘3’) AS X WHERE a = X.c AND b = X.b</programlisting>
      </example>
    </section>
    <section id="other_subqueries">
      <title>Subqueries can appear anywhere where an expression or criteria is expected.</title>
      <para>Subqueries are supported in quantified criteria, the EXISTS predicate, the IN predicate, and as <xref linkend="scalar_subqueries"/>.</para>
      <example>
        <title>Example Subquery in WHERE Using EXISTS</title>
        <programlisting>SELECT a FROM X WHERE EXISTS (SELECT 1 FROM Y WHERE c=X.a)</programlisting>
      </example>
      <example>
        <title>Example Quantified Comparison Subqueries</title>
        <programlisting language="SQL">SELECT a FROM X WHERE a &gt;= ANY (SELECT b FROM Y WHERE c=3) 
SELECT a FROM X WHERE a &lt; SOME (SELECT b FROM Y WHERE c=4) 
SELECT a FROM X WHERE a = ALL (SELECT b FROM Y WHERE c=2)</programlisting>
      </example>
      <example>
        <title>Example IN Subquery</title>
        <programlisting language="SQL">SELECT a FROM X WHERE a IN (SELECT b FROM Y WHERE c=3)</programlisting>
      </example>
      <para>See also <xref linkend="subquery_optimization"/>.</para>
    </section>
  </section>
</chapter>