<?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" [
<!ENTITY % CustomDTD SYSTEM "../../../../../../docbook/custom.dtd">
%CustomDTD;
]>
<chapter id="scalar_functions">
	<title>Scalar Functions</title>
	<para>
		Teiid provides an extensive set of built-in scalar functions. See also
		<link linkend="sql_support">SQL Support</link>
		and
		<link linkend="datatypes">Datatypes</link>
		. In addition, Teiid provides the capability for user defined functions or UDFs.  See the Developers Guide for adding UDFs.  Once added UDFs may be called just like any other function.
	</para>
	<section id="numeric_functions">
		<title>Numeric Functions</title>
		<para>Numeric functions return numeric values (integer, long,
			float, double, biginteger, bigdecimal). They generally take numeric
			values as inputs, though some take strings.</para>
		<informaltable frame="all">
			<tgroup cols="3">
				<thead>
					<row>
						<entry>
							<para>Function</para>
						</entry>
						<entry>
							<para>Definition</para>
						</entry>
						<entry>
							<para>Datatype Constraint</para>
						</entry>
					</row>
				</thead>
				<tbody>
					<row>
						<entry>
							<para>+ - * /</para>
						</entry>
						<entry>
							<para>Standard numeric operators</para>
						</entry>
						<entry>
							<para>x in {integer, long, float, double, biginteger,
								bigdecimal}, return type is same as x <footnote><para>The precision and scale of non-bigdecimal arithmetic function functions results matches that of Java.  The results of bigdecimal operations match Java, except for division, which uses a preferred scale of max(16, dividend.scale + divisor.precision + 1), which then has trailing zeros removed by setting the scale to max(dividend.scale, normalized scale)</para></footnote></para>
						</entry>
					</row>
					<row>
						<entry>
							<para>ABS(x)</para>
						</entry>
						<entry>
							<para>Absolute value of x</para>
						</entry>
						<entry>
							<para>See standard numeric operators above</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>ACOS(x)</para>
						</entry>
						<entry>
							<para>Arc cosine of x</para>
						</entry>
						<entry>
							<para>x in {double, bigdecimal}, return type is double</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>ASIN(x)</para>
						</entry>
						<entry>
							<para>Arc sine of x</para>
						</entry>
						<entry>
							<para>x in {double, bigdecimal}, return type is double</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>ATAN(x)</para>
						</entry>
						<entry>
							<para>Arc tangent of x</para>
						</entry>
						<entry>
							<para>x in {double, bigdecimal}, return type is double</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>ATAN2(x,y)</para>
						</entry>
						<entry>
							<para>Arc tangent of x and y</para>
						</entry>
						<entry>
							<para>x, y in {double, bigdecimal}, return type is double</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>CEILING(x)</para>
						</entry>
						<entry>
							<para>Ceiling of x</para>
						</entry>
						<entry>
							<para>x in {double, float}, return type is double</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>COS(x)</para>
						</entry>
						<entry>
							<para>Cosine of x</para>
						</entry>
						<entry>
							<para>x in {double, bigdecimal}, return type is double</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>COT(x)</para>
						</entry>
						<entry>
							<para>Cotangent of x</para>
						</entry>
						<entry>
							<para>x in {double, bigdecimal}, return type is double</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>DEGREES(x)</para>
						</entry>
						<entry>
							<para>Convert x degrees to radians</para>
						</entry>
						<entry>
							<para>x in {double, bigdecimal}, return type is double</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>EXP(x)</para>
						</entry>
						<entry>
							<para>e^x</para>
						</entry>
						<entry>
							<para>x in {double, float}, return type is double</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>FLOOR(x)</para>
						</entry>
						<entry>
							<para>Floor of x</para>
						</entry>
						<entry>
							<para>x in {double, float}, return type is double</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>FORMATBIGDECIMAL(x, y)</para>
						</entry>
						<entry>
							<para>Formats x using format y</para>
						</entry>
						<entry>
							<para>x is bigdecimal, y is string, returns string</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>FORMATBIGINTEGER(x, y)</para>
						</entry>
						<entry>
							<para>Formats x using format y</para>
						</entry>
						<entry>
							<para>x is biginteger, y is string, returns string</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>FORMATDOUBLE(x, y)</para>
						</entry>
						<entry>
							<para>Formats x using format y</para>
						</entry>
						<entry>
							<para>x is double, y is string, returns string</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>FORMATFLOAT(x, y)</para>
						</entry>
						<entry>
							<para>Formats x using format y</para>
						</entry>
						<entry>
							<para>x is float, y is string, returns string</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>FORMATINTEGER(x, y)</para>
						</entry>
						<entry>
							<para>Formats x using format y</para>
						</entry>
						<entry>
							<para>x is integer, y is string, returns string</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>FORMATLONG(x, y)</para>
						</entry>
						<entry>
							<para>Formats x using format y</para>
						</entry>
						<entry>
							<para>x is long, y is string, returns string</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>LOG(x)</para>
						</entry>
						<entry>
							<para>Natural log of x (base e)</para>
						</entry>
						<entry>
							<para>x in {double, float}, return type is double</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>LOG10(x)</para>
						</entry>
						<entry>
							<para>Log of x (base 10)</para>
						</entry>
						<entry>
							<para>x in {double, float}, return type is double</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>MOD(x, y)</para>
						</entry>
						<entry>
							<para>Modulus (remainder of x / y)</para>
						</entry>
						<entry>
							<para>x in {integer, long, float, double, biginteger, bigdecimal}, return
								type is same as x</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>PARSEBIGDECIMAL(x, y)</para>
						</entry>
						<entry>
							<para>Parses x using format y</para>
						</entry>
						<entry>
							<para>x, y are strings, returns bigdecimal</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>PARSEBIGINTEGER(x, y)</para>
						</entry>
						<entry>
							<para>Parses x using format y</para>
						</entry>
						<entry>
							<para>x, y are strings, returns biginteger</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>PARSEDOUBLE(x, y)</para>
						</entry>
						<entry>
							<para>Parses x using format y</para>
						</entry>
						<entry>
							<para>x, y are strings, returns double</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>PARSEFLOAT(x, y)</para>
						</entry>
						<entry>
							<para>Parses x using format y</para>
						</entry>
						<entry>
							<para>x, y are strings, returns float</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>PARSEINTEGER(x, y)</para>
						</entry>
						<entry>
							<para>Parses x using format y</para>
						</entry>
						<entry>
							<para>x, y are strings, returns integer</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>PARSELONG(x, y)</para>
						</entry>
						<entry>
							<para>Parses x using format y</para>
						</entry>
						<entry>
							<para>x, y are strings, returns long</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>PI()</para>
						</entry>
						<entry>
							<para>Value of Pi</para>
						</entry>
						<entry>
							<para>return is double</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>POWER(x,y)</para>
						</entry>
						<entry>
							<para>x to the y power</para>
						</entry>
						<entry>
							<para>x in {double, bigdecimal, biginteger}, return is the same type as x</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>RADIANS(x)</para>
						</entry>
						<entry>
							<para>Convert x radians to degrees</para>
						</entry>
						<entry>
							<para>x in {double, bigdecimal}, return type is double</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>RAND()</para>
						</entry>
						<entry>
							<para>Returns a random number, using generator established
								so far in the query or initializing with system clock if
								necessary.</para>
						</entry>
						<entry>
							<para>Returns double.</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>RAND(x)</para>
						</entry>
						<entry>
							<para>Returns a random number, using new generator seeded
								with x.</para>
						</entry>
						<entry>
							<para>x is integer, returns double.</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>ROUND(x,y)</para>
						</entry>
						<entry>
							<para>Round x to y places; negative values of y indicate
								places to the left of the decimal point</para>
						</entry>
						<entry>
							<para>x in {integer, float, double, bigdecimal} y is integer, return is same
								type as x</para>
						</entry>
					</row>
					<row>
						<entry>
							<para>SIGN(x)</para>
						</entry>
						<entry>
							<para>1 if x &gt; 0, 0 if x = 0, -1 if x &lt; 0</para>
            </entry>
              <entry>
                <para>x in {integer, long, float, double, biginteger, bigdecimal},
                  return type is integer</para>
              </entry>
          </row>
          <row>
            <entry>
              <para>SIN(x)</para>
            </entry>
            <entry>
              <para>Sine value of x</para>
            </entry>
            <entry>
              <para>x in {double, bigdecimal}, return type is double</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>SQRT(x)</para>
            </entry>
            <entry>
              <para>Square root of x</para>
            </entry>
            <entry>
              <para>x in {long, double, bigdecimal}, return type is double</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>TAN(x)</para>
            </entry>
            <entry>
              <para>Tangent of x</para>
            </entry>
            <entry>
              <para>x in {double, bigdecimal}, return type is double</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>BITAND(x, y)</para>
            </entry>
            <entry>
              <para>Bitwise AND of x and y</para>
            </entry>
            <entry>
              <para>x, y in {integer}, return type is integer</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>BITOR(x, y)</para>
            </entry>
            <entry>
              <para>Bitwise OR of x and y</para>
            </entry>
            <entry>
              <para>x, y in {integer}, return type is integer</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>BITXOR(x, y)</para>
            </entry>
            <entry>
              <para>Bitwise XOR of x and y</para>
            </entry>
            <entry>
              <para>x, y in {integer}, return type is integer</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>BITNOT(x)</para>
            </entry>
            <entry>
              <para>Bitwise NOT of x</para>
            </entry>
            <entry>
              <para>x in {integer}, return type is integer</para>
            </entry>
          </row>
        </tbody>
      </tgroup>
    </informaltable>
    <section>
      <title>Parsing Numeric Datatypes from Strings</title>
      <para>
        Teiid offers a set of functions you can use to parse numbers from
        strings. For each string, you need to provide the formatting of the
        string. These functions use the convention established by the
        java.text.DecimalFormat class to define the formats you can use with
        these functions. You can learn more about how this class defines
        numeric string formats by visiting the Sun Java Web site at the
        following
        <ulink
          url="http://java.sun.com/javase/6/docs/api/java/text/DecimalFormat.html">URL for Sun Java</ulink>.
      </para>
      <para>For example, you could use these function calls, with the
        formatting string that adheres to the java.text.DecimalFormat
        convention, to parse strings and return the datatype you need:
      </para>
      <informaltable frame="all">
        <tgroup cols="4">
          <thead>
            <row>
              <entry>
                <para>Input String</para>
              </entry>
              <entry>
                <para>Function Call to Format String</para>
              </entry>
              <entry>
                <para>Output Value</para>
              </entry>
              <entry>
                <para>Output Datatype</para>
              </entry>
            </row>          
          </thead>
          <tbody>
            <row>
              <entry>
                <para>'$25.30'</para>
              </entry>
              <entry>
                <para>parseDouble(cost, '$#,##0.00;($#,##0.00)')</para>
              </entry>
              <entry>
                <para>25.3</para>
              </entry>
              <entry>
                <para>double</para>
              </entry>
            </row>
            <row>
              <entry>
                <para>'25%'</para>
              </entry>
              <entry>
                <para>parseFloat(percent, '#,##0%')</para>
              </entry>
              <entry>
                <para>25</para>
              </entry>
              <entry>
                <para>float</para>
              </entry>
            </row>
            <row>
              <entry>
                <para>'2,534.1'</para>
              </entry>
              <entry>
                <para>parseFloat(total, '#,##0.###;-#,##0.###')
                </para>
              </entry>
              <entry>
                <para>2534.1</para>
              </entry>
              <entry>
                <para>float</para>
              </entry>
            </row>
            <row>
              <entry>
                <para>'1.234E3'</para>
              </entry>
              <entry>
                <para>parseLong(amt, '0.###E0')</para>
              </entry>
              <entry>
                <para>1234</para>
              </entry>
              <entry>
                <para>long</para>
              </entry>
            </row>
            <row>
              <entry>
                <para>'1,234,567'</para>
              </entry>
              <entry>
                <para>parseInteger(total, '#,##0;-#,##0')
                </para>
              </entry>
              <entry>
                <para>1234567</para>
              </entry>
              <entry>
                <para>integer</para>
              </entry>
            </row>
          </tbody>
        </tgroup>
      </informaltable>
    </section>
    <section>
      <title>Formatting Numeric Datatypes as Strings</title>
      <para>
        Teiid offers a set of functions you can use to convert numeric
        datatypes into strings. For each string, you need to provide the
        formatting. These functions use the convention established within
        the java.text.DecimalFormat class to define the formats you can
        use with these functions. You can learn more about how this class
        defines numeric string formats by visiting the Sun Java Web site
        at the following
        <ulink
          url="http://java.sun.com/javase/6/docs/api/java/text/DecimalFormat.html">URL for Sun Java</ulink>
        .
      </para>
      <para>For example, you could use these function calls, with the
        formatting string that adheres to the java.text.DecimalFormat
        convention, to format the numeric datatypes into strings:</para>
      <informaltable frame="all">
        <tgroup cols="4">
          <thead>
            <row>
              <entry>
                <para>Input Value</para>
              </entry>
              <entry>
                <para>Input Datatype</para>
              </entry>
              <entry>
                <para>Function Call to Format String</para>
              </entry>
              <entry>
                <para>Output String</para>
              </entry>
            </row>
          </thead>
          <tbody>
            <row>
              <entry>
                <para>25.3</para>
              </entry>
              <entry>
                <para>double</para>
              </entry>
              <entry>
                <para>formatDouble(cost, '$#,##0.00;($#,##0.00)')</para>
              </entry>
              <entry>
                <para>'$25.30'</para>
              </entry>
            </row>
            <row>
              <entry>
                <para>25</para>
              </entry>
              <entry>
                <para>float</para>
              </entry>
              <entry>
                <para>formatFloat(percent, '#,##0%')</para>
              </entry>
              <entry>
                <para>'25%'</para>
              </entry>
            </row>
            <row>
              <entry>
                <para>2534.1</para>
              </entry>
              <entry>
                <para>float</para>
              </entry>
              <entry>
                <para>formatFloat(total, '#,##0.###;-#,##0.###')
                </para>
              </entry>
              <entry>
                <para>'2,534.1'</para>
              </entry>
            </row>
            <row>
              <entry>
                <para>1234</para>
              </entry>
              <entry>
                <para>long</para>
              </entry>
              <entry>
                <para>formatLong(amt, '0.###E0')</para>
              </entry>
              <entry>
                <para>'1.234E3'</para>
              </entry>
            </row>
            <row>
              <entry>
                <para>1234567</para>
              </entry>
              <entry>
                <para>integer</para>
              </entry>
              <entry>
                <para>formatInteger(total, '#,##0;-#,##0')
                </para>
              </entry>
              <entry>
                <para>'1,234,567'</para>
              </entry>
            </row>
          </tbody>
        </tgroup>
      </informaltable>
    </section>
  </section>
  <section id="string_functions">
    <title>String Functions</title>
    <para>String functions generally take strings as inputs and return strings as outputs. </para>
    <para>Unless specified, all of the arguments and return types in the following table are strings and all indexes are 1-based. The 0 index is considered to be before the start of the string.</para>
    <informaltable frame="all">
      <tgroup cols="3">
        <thead>
          <row>
            <entry>
              <para>Function</para>
            </entry>
            <entry>
              <para>Definition</para>
            </entry>
            <entry>
              <para>Datatype Constraint</para>
            </entry>
          </row>
        </thead>
        <tbody>
          <row>
            <entry>
              <para>x || y</para>
            </entry>
            <entry>
              <para>Concatenation operator</para>
            </entry>
            <entry>
              <para>x,y in {string}, return type is string</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>ASCII(x)</para>
            </entry>
            <entry>
              <para>Provide ASCII value of the left most character in x.  The empty string will as input will return null. <footnoteref linkend="nonAscii"/></para>
            </entry>
            <entry>
              <para>return type is integer</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>CHR(x) CHAR(x)</para>
            </entry>
            <entry>
              <para>Provide the character for ASCII value x 
              	<footnote id="nonAscii"><para>Non-ASCII range characters or integers used in these functions 
              	may produce different results or exceptions depending on where the function is evalutated (Teiid vs. source).  
              	Teiid's uses Java default int to char and char to int conversions, which operates over UTF16 values.</para></footnote>
              </para>
            </entry>
            <entry>
              <para>x in {integer}</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>CONCAT(x, y)</para>
            </entry>
            <entry>
              <para>Concatenates x and y with ANSI semantics. If
                x and/or y is null, returns null.</para>
            </entry>
            <entry>
              <para>x, y in {string}</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>CONCAT2(x, y)</para>
            </entry>
            <entry>
              <para>Concatenates x and y with non-ANSI null
                semantics. If x and y is null, returns null. If only x or y
                is null, returns the other value.</para>
            </entry>
            <entry>
              <para>x, y in {string}</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>INITCAP(x)</para>
            </entry>
            <entry>
              <para>Make first letter of each word in string x capital
                and all others lowercase</para>
            </entry>
            <entry>
              <para>x in {string}</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>INSERT(str1, start, length, str2)</para>
            </entry>
            <entry>
              <para>Insert string2 into string1</para>
            </entry>
            <entry>
              <para>str1 in {string}, start in {integer}, length in
                {integer}, str2 in {string}</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>LCASE(x)</para>
            </entry>
            <entry>
              <para>Lowercase of x</para>
            </entry>
            <entry>
              <para>x in {string}</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>LEFT(x, y)</para>
            </entry>
            <entry>
              <para>Get left y characters of x</para>
            </entry>
            <entry>
              <para>x in {string}, y in {integer}, return string</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>LENGTH(x)</para>
            </entry>
            <entry>
              <para>Length of x</para>
            </entry>
            <entry>
              <para>return type is integer</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>LOCATE(x, y)</para>
            </entry>
            <entry>
              <para>Find position of x in y starting at beginning of y
              </para>
            </entry>
            <entry>
              <para>x in {string}, y in {string}, return integer</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>LOCATE(x, y, z)</para>
            </entry>
            <entry>
              <para>Find position of x in y starting at z</para>
            </entry>
            <entry>
              <para>x in {string}, y in {string}, z in {integer}, return
                integer</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>LPAD(x, y)</para>
            </entry>
            <entry>
              <para>Pad input string x with spaces on the left to the
                length of y</para>
            </entry>
            <entry>
              <para>x in {string}, y in {integer}, return string</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>LPAD(x, y, z)</para>
            </entry>
            <entry>
              <para>Pad input string x on the left to the length of y
                using character z</para>
            </entry>
            <entry>
              <para>x in {string}, y in {string}, z in {character}, return
                string</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>LTRIM(x)</para>
            </entry>
            <entry>
              <para>Left trim x of blank chars</para>
            </entry>
            <entry>
              <para>x in {string}, return string</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>QUERYSTRING(path [, expr [AS name] ...])</para>
            </entry>
            <entry>
              <para>Returns a properly encoded query string appended to the given path.  Null valued expressions are omitted, and a null path is treated as ''.</para>
              <para>Names are optional for column reference expressions.</para>
              <para>e.g. QUERYSTRING('path', 'value' as "&amp;x", ' &amp; ' as y, null as z) returns 'path?%26x=value&amp;y=%20%26%20'</para>
            </entry>
            <entry>
              <para>path, expr in {string}.  name is an identifier</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>REPEAT(str1,instances)</para>
            </entry>
            <entry>
              <para>Repeat string1 a specified number of times</para>
            </entry>
            <entry>
              <para>str1 in {string}, instances in {integer} return
                string</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>REPLACE(x, y, z)</para>
            </entry>
            <entry>
              <para>Replace all y in x with z</para>
            </entry>
            <entry>
              <para>x,y,z in {string}, return string</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>RIGHT(x, y)</para>
            </entry>
            <entry>
              <para>Get right y characters of x</para>
            </entry>
            <entry>
              <para>x in {string}, y in {integer}, return string</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>RPAD(input string x, pad length y)</para>
            </entry>
            <entry>
              <para>Pad input string x with spaces on the right to the
                length of y</para>
            </entry>
            <entry>
              <para>x in {string}, y in {integer}, return string</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>RPAD(x, y, z)</para>
            </entry>
            <entry>
              <para>Pad input string x on the right to the length of y
                using character z</para>
            </entry>
            <entry>
              <para>x in {string}, y in {string}, z in {character}, return
                string</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>RTRIM(x)</para>
            </entry>
            <entry>
              <para>Right trim x of blank chars</para>
            </entry>
            <entry>
              <para>x is string, return string</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>SUBSTRING(x, y)</para>
              <para>SUBSTRING(x FROM y)</para>
            </entry>
            <entry>
              <para>Get substring from x, from position y to the end of x
              </para>
            </entry>
            <entry>
              <para>y in {integer}</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>SUBSTRING(x, y, z)</para>
              <para>SUBSTRING(x FROM y FOR z)</para>
            </entry>
            <entry>
              <para>Get substring from x from position y with length z
              </para>
            </entry>
            <entry>
              <para>y, z in {integer}</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>TO_CHARS(x, encoding)</para>
            </entry>
            <entry>
              <para>Return a clob from the blob with the given encoding.  
              BASE64, HEX, and the builtin Java Charset names are valid values for the encoding.<footnote id="charset"><para>See the <ulink url="http://java.sun.com/j2se/1.5.0/docs/api/java/nio/charset/Charset.html">Charset JavaDoc</ulink> for more on supported Charset names.  For charsets, unmappable chars will be replaced with the charset default character.  binary formats, such as BASE64, will error in their conversion to bytes is a unrecognizable character is encountered.</para></footnote></para>
            </entry>
            <entry>
              <para>x is a blob, encoding is a string, and returns a clob</para>
            </entry>
          </row><row>
            <entry>
              <para id="to_bytes">TO_BYTES(x, encoding)</para>
            </entry>
            <entry>
              <para>Return a blob from the clob with the given encoding.  
              BASE64, HEX, and the builtin Java Charset names are valid values for the encoding.<footnoteref linkend="charset"/></para>
            </entry>
            <entry>
              <para>x in a clob, encoding is a string, and returns a blob</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>TRANSLATE(x, y, z)</para>
            </entry>
            <entry>
              <para>Translate string x by replacing each character
                in y with the character in z at the same position</para>
            </entry>
            <entry>
              <para>x in {string}</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>TRIM([[LEADING|TRAILING|BOTH] [x] FROM] y)</para>
            </entry>
            <entry>
              <para>Trim the leading, trailing, or both ends of a string y of character x.  
              If LEADING/TRAILING/BOTH is not specified, BOTH is used.  If no trim character x is specficed then the blank space ' ' is used.</para>
            </entry>
            <entry>
              <para>x in {character}, y in {string}</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>UCASE(x)</para>
            </entry>
            <entry>
              <para>Uppercase of x</para>
            </entry>
            <entry>
              <para>x in {string}</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>UNESCAPE(x)</para>
            </entry>
            <entry>
              <para>Unescaped version of x.  Possible escape sequences are \b - backspace, \t - tab, \n - line feed, \f - form feed, \r - carriage return.  \uXXXX, where X is a hex value, can be used to specify any unicode character.  \XXX, where X is an octal digit, can be used to specify an octal byte value.  
              If any other character appears after an escape character, that character will appear in the output and the escape character will be ignored.</para>
            </entry>
            <entry>
              <para>x in {string}</para>
            </entry>
          </row>
        </tbody>
      </tgroup>
    </informaltable>
  </section>
  <section id="date_time_functions">
    <title>Date/Time Functions</title>
    <para>Date and time functions return or operate on dates, times, or timestamps. </para>
    <para>Parse and format Date/Time functions use the convention established within
        the java.text.SimpleDateFormat class to define the formats you can
        use with these functions. You can learn more about how this class
        defines formats by visiting the Sun Java Web site
        at the following
        <ulink
          url="http://java.sun.com/javase/6/docs/api/java/text/SimpleDateFormat.html">URL for Sun Java</ulink>.
    </para>
    <informaltable frame="all">
      <tgroup cols="3">
        <thead>
          <row>
            <entry>
              <para>Function</para>
            </entry>
            <entry>
              <para>Definition</para>
            </entry>
            <entry>
              <para>Datatype Constraint</para>
            </entry>
          </row>        
        </thead>
        <tbody>
          <row>
            <entry>
              <para>CURDATE()</para>
            </entry>
            <entry>
              <para>Return current date</para>
            </entry>
            <entry>
              <para>returns date</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>CURTIME()</para>
            </entry>
            <entry>
              <para>Return current time</para>
            </entry>
            <entry>
              <para>returns time</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>NOW()</para>
            </entry>
            <entry>
              <para>Return current timestamp (date and time)</para>
            </entry>
            <entry>
              <para>returns timestamp</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>DAYNAME(x)</para>
            </entry>
            <entry>
              <para>Return name of day</para>
            </entry>
            <entry>
              <para>x in {date, timestamp}, returns string</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>DAYOFMONTH(x)</para>
            </entry>
            <entry>
              <para>Return day of month</para>
            </entry>
            <entry>
              <para>x in {date, timestamp}, returns integer</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>DAYOFWEEK(x)</para>
            </entry>
            <entry>
              <para>Return day of week (Sunday=1)</para>
            </entry>
            <entry>
              <para>x in {date, timestamp}, returns integer</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>DAYOFYEAR(x)</para>
            </entry>
            <entry>
              <para>Return Julian day number</para>
            </entry>
            <entry>
              <para>x in {date, timestamp}, returns integer</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>EXTRACT(YEAR|MONTH|DAY|HOUR|MINUTE|SECOND FROM x)</para>
            </entry>
            <entry>
              <para>Return the given field value from the date value x.
              Produces the same result as the assoceated YEAR, MONTH, DAYOFMONTH, HOUR, MINUTE, SECOND functions.
              </para>
              <para>The SQL specification also allows for TIMEZONE_HOUR and TIMEZONE_MINUTE as extraction targets.  
              In Teiid all date values are in the timezone of the server.</para>
            </entry>
            <entry>
              <para>x in {date, time, timestamp}, returns integer</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>FORMATDATE(x, y)</para>
            </entry>
            <entry>
              <para>Format date x using format y</para>
            </entry>
            <entry>
              <para>x is date, y is string, returns string</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>FORMATTIME(x, y)</para>
            </entry>
            <entry>
              <para>Format time x using format y</para>
            </entry>
            <entry>
              <para>x is time, y is string, returns string</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>FORMATTIMESTAMP(x, y)</para>
            </entry>
            <entry>
              <para>Format timestamp x using format y</para>
            </entry>
            <entry>
              <para>x is timestamp, y is string, returns string</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>FROM_UNIXTIME (unix_timestamp)</para>
            </entry>
            <entry>
              <para>Return the Unix timestamp (in seconds) as a Timestamp value
              </para>
            </entry>
            <entry>
              <para>Unix timestamp (in seconds)</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>HOUR(x)</para>
            </entry>
            <entry>
              <para>Return hour (in military 24-hour format)</para>
            </entry>
            <entry>
              <para>x in {time, timestamp}, returns integer</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>MINUTE(x)</para>
            </entry>
            <entry>
              <para>Return minute</para>
            </entry>
            <entry>
              <para>x in {time, timestamp}, returns integer</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>MODIFYTIMEZONE (timestamp, startTimeZone,
                endTimeZone)</para>
            </entry>
            <entry>
              <para>Returns a timestamp based upon the incoming timestamp
                adjusted for the differential between the start and end time
                zones.  i.e. if the server is in GMT-6, then modifytimezone({ts
                '2006-01-10 04:00:00.0'},'GMT-7', 'GMT-8') will return the
                timestamp {ts '2006-01-10 05:00:00.0'} as read in GMT-6.  The
                value has been adjusted 1 hour ahead to compensate for the
                difference between GMT-7 and GMT-8.</para>
            </entry>
            <entry>
              <para>startTimeZone and endTimeZone are strings,
                returns a timestamp</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>MODIFYTIMEZONE (timestamp, endTimeZone)</para>
            </entry>
            <entry>
              <para>Return a timestamp in the same manner as
                modifytimezone(timestamp, startTimeZone, endTimeZone), but will
                assume that the startTimeZone is the same as the server process.
              </para>
            </entry>
            <entry>
              <para>Timestamp is a timestamp; endTimeZone is a string,
                returns a timestamp</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>MONTH(x)</para>
            </entry>
            <entry>
              <para>Return month</para>
            </entry>
            <entry>
              <para>x in {date, timestamp}, returns integer</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>MONTHNAME(x)</para>
            </entry>
            <entry>
              <para>Return name of month</para>
            </entry>
            <entry>
              <para>x in {date, timestamp}, returns string</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>PARSEDATE(x, y)</para>
            </entry>
            <entry>
              <para>Parse date from x using format y</para>
            </entry>
            <entry>
              <para>x, y in {string}, returns date</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>PARSETIME(x, y)</para>
            </entry>
            <entry>
              <para>Parse time from x using format y</para>
            </entry>
            <entry>
              <para>x, y in {string}, returns time</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>PARSETIMESTAMP(x,y)</para>
            </entry>
            <entry>
              <para>Parse timestamp from x using format y</para>
            </entry>
            <entry>
              <para>x, y in {string}, returns timestamp</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>QUARTER(x)</para>
            </entry>
            <entry>
              <para>Return quarter</para>
            </entry>
            <entry>
              <para>x in {date, timestamp}, returns integer</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>SECOND(x)</para>
            </entry>
            <entry>
              <para>Return seconds</para>
            </entry>
            <entry>
              <para>x in {time, timestamp}, returns integer</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>TIMESTAMPCREATE(date, time)</para>
            </entry>
            <entry>
              <para>Create a timestamp from a date and time</para>
            </entry>
            <entry>
              <para>date in {date}, time in {time}, returns timestamp
              </para>
            </entry>
          </row>
          <row>
            <entry>
              <para>TIMESTAMPADD(interval, count,
                timestamp)</para>
            </entry>
            <entry>
              <para>Add a specified interval (hour, day of week, month)
                to the timestamp, where intervals can have the following
                definition:</para>
              <orderedlist>
                <listitem>
                  <para>SQL_TSI_FRAC_SECOND - fractional
                    seconds (billionths of a second)</para>
                </listitem>
                <listitem>
                  <para>SQL_TSI_SECOND - seconds</para>
                </listitem>
                <listitem>
                  <para>SQL_TSI_MINUTE - minutes</para>
                </listitem>
                <listitem>
                  <para>SQL_TSI_HOUR - hours</para>
                </listitem>
                <listitem>
                  <para>SQL_TSI_DAY - days</para>
                </listitem>
                <listitem>
                  <para>SQL_TSI_WEEK - weeks</para>
                </listitem>
                <listitem>
                  <para>SQL_TSI_MONTH - months</para>
                </listitem>
                <listitem>
                  <para>SQL_TSI_QUARTER - quarters (3 months)
                  </para>
                </listitem>
                <listitem>
                  <para>SQL_TSI_YEAR - years</para>
                </listitem>
              </orderedlist>
            </entry>
            <entry>
              <para>The interval constant may be specified either as a
                string literal or a constant value. Interval in {string},
                count in {integer}, timestamp in {date, time, timestamp}
              </para>
            </entry>
          </row>
          <row>
            <entry>
              <para>TIMESTAMPDIFF(interval,
                startTime, endTime)</para>
            </entry>
            <entry>
              <para>Calculate the approximate number of whole
                intervals in (endTime - startTime) using a specific interval
                type (as defined by the constants in TIMESTAMPADD). If
                (endTime &gt; startTime), a positive number will be returned. If (endTime &lt; startTime), a negative number will be returned. Calculations are approximate and may be less accurate over longer time spans.</para>
            </entry>
            <entry>
              <para>Interval in {string}; startTime, endTime in {timestamp}, returns a long.</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>WEEK(x)</para>
            </entry>
            <entry>
              <para>Return week in year</para>
            </entry>
            <entry>
              <para>x in {date, timestamp}, returns integer</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>YEAR(x)</para>
            </entry>
            <entry>
              <para>Return four-digit year</para>
            </entry>
            <entry>
              <para>x in {date, timestamp}, returns integer</para>
            </entry>
          </row>
        </tbody>
      </tgroup>
    </informaltable>
    <section>
      <title>Parsing Date Datatypes from Strings</title>
      <para>
        Teiid does not implicitly convert strings that
        contain dates presented in different formats, such as ‘19970101’ and
        ‘31/1/1996’ to date-related datatypes. You can, however, use the
        parseDate, parseTime, and parseTimestamp functions, described in the
        next section, to explicitly convert strings with a different format
        to the appropriate datatype. These functions use the convention
        established within the java.text.SimpleDateFormat class to define
        the formats you can use with these functions. You can learn more
        about how this class defines date and time string formats by
        visiting the
        <ulink
          url="http://java.sun.com/j2se/1.4.2/docs/api/java/text/SimpleDateFormat.html">Sun Java Web site</ulink>
        .
      </para>
      <para>For example, you could use these function calls, with the
        formatting string that adheres to the java.text.SimpleDateFormat
        convention, to parse strings and return the datatype you need:
      </para>
      <informaltable frame="all">
        <tgroup cols="2">
          <thead>
            <row>
              <entry>
                <para>String</para>
              </entry>
              <entry>
                <para>Function Call To Parse String</para>
              </entry>
            </row>
          </thead>
          <tbody>
            <row>
              <entry>
                <para>'1997010'</para>
              </entry>
              <entry>
                <para>parseDate(myDateString, 'yyyyMMdd')</para>
              </entry>
            </row>
            <row>
              <entry>
                <para>'31/1/1996'</para>
              </entry>
              <entry>
                <para>parseDate(myDateString, 'dd''/''MM''/''yyyy')</para>
              </entry>
            </row>
            <row>
              <entry>
                <para>'22:08:56 CST'</para>
              </entry>
              <entry>
                <para>parseTime (myTime, 'HH:mm:ss z')</para>
              </entry>
            </row>
            <row>
              <entry>
                <para>'03.24.2003 at 06:14:32'</para>
              </entry>
              <entry>
                <para>parseTimestamp(myTimestamp, 'MM.dd.yyyy ''at'' hh:mm:ss')</para>
              </entry>
            </row>
          </tbody>
        </tgroup>
      </informaltable>
    </section>
    <section>
      <title>Specifying Time Zones</title>
      <para>Time zones can be specified in several formats. Common
        abbreviations such as EST for "Eastern Standard Time" are allowed
        but discouraged, as they can be ambiguous. Unambiguous time zones
        are defined in the form continent or ocean/largest city. For
        example, America/New_York, America/Buenos_Aires, or Europe/London.
        Additionally, you can specify a custom time zone by GMT offset:
        GMT[+/-]HH:MM.</para>
      <para>For example: GMT-05:00</para>
    </section>
  </section>
  <section>
    <title>Type Conversion Functions</title>
    <para>
    Within your queries, you can convert between datatypes using the
    CONVERT or CAST keyword. See also <link linkend="type_conversions">Data Type Conversions</link>
    .
    </para>
    <informaltable frame="all">
      <tgroup cols="2">
        <thead>
          <row>
            <entry>
              <para>Function</para>
            </entry>
            <entry>
              <para>Definition</para>
            </entry>
          </row>
        </thead>
        <tbody>
          <row>
            <entry>
              <para>CONVERT(x, type)</para>
            </entry>
            <entry>
              <para>Convert x to type, where type is a Teiid
                Base Type</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>CAST(x AS type)</para>
            </entry>
            <entry>
              <para>Convert x to type, where type is a Teiid
                Base Type</para>
            </entry>
          </row>
        </tbody>
      </tgroup>
    </informaltable>
    <para>These functions are identical other than syntax; CAST is the standard SQL syntax, CONVERT is the standard JDBC/ODBC syntax. </para>
  </section>
  <section id="choice_functions">
    <title>Choice Functions</title>
    <para>Choice functions provide a way to select from two values based on some characteristic of one of the values. </para>
    <informaltable frame="all">
      <tgroup cols="3">
        <tbody>
          <row>
            <entry>
              <para>Function</para>
            </entry>
            <entry>
              <para>Definition</para>
            </entry>
            <entry>
              <para>Datatype Constraint</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>COALESCE(x,y+)</para>
            </entry>
            <entry>
              <para>Returns the first non-null parameter</para>
            </entry>
            <entry>
              <para>x and all y's can be any compatible types</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>IFNULL(x,y)</para>
            </entry>
            <entry>
              <para>If x is null, return y; else return x</para>
            </entry>
            <entry>
              <para>x, y, and the return type must be the same type but
                can be any type</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>NVL(x,y)</para>
            </entry>
            <entry>
              <para>If x is null, return y; else return x</para>
            </entry>
            <entry>
              <para>x, y, and the return type must be the same type but
                can be any type</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>NULLIF(param1, param2)</para>
            </entry>
            <entry>
              <para>Equivalent to case when (param1 = param2) then null else param1</para>
            </entry>
            <entry>
              <para>param1 and param2 must be compatable comparable types</para>
            </entry>
          </row>
        </tbody>
      </tgroup>
    </informaltable>
    <para>IFNULL and NVL are aliases of each other. They are the same function. </para>
  </section>
  <section>
    <title>Decode Functions</title>
    <para>Decode functions allow you to have the Teiid Server
      examine the contents of a column in a result set and alter, or
      decode, the value so that your application can better use the
      results.</para>
    <informaltable frame="all">
      <tgroup cols="3">
        <thead>
          <row>
            <entry>
              <para>Function</para>
            </entry>
            <entry>
              <para>Definition</para>
            </entry>
            <entry>
              <para>Datatype Constraint</para>
            </entry>
          </row>
        </thead>
        <tbody>
          <row>
            <entry>
              <para>DECODESTRING(x, y [, z])</para>
            </entry>
            <entry>
              <para>Decode column x using string of value pairs y
                with optional delimiter z and return the decoded column as a string.
                If a delimiter is not specified , is used.
                y has the formate SearchDelimResultDelimSearchDelimResult[DelimDefault]
                Returns Default if specified or x if there are no matches.
              </para>
              <warning><para>Deprecated. Use a CASE expression instead.</para></warning>
            </entry>
            <entry>
              <para>all string</para>
            </entry>
          </row>
          <row>
            <entry>
              <para>DECODEINTEGER(x, y [, z])</para>
            </entry>
            <entry>
              <para>Decode column x using string of value pairs y
                with optional delimiter z and return the decoded column as an
                integer. If a delimiter is not specified , is used.
                y has the formate SearchDelimResultDelimSearchDelimResult[DelimDefault]
                Returns Default if specified or x if there are no matches.
                </para>
              <warning><para>Deprecated. Use a CASE expression instead.</para></warning>
            </entry>
            <entry>
              <para>all string parameters, return integer</para>
            </entry>
          </row>
        </tbody>
      </tgroup>
    </informaltable>
    <para>Within each function call, you include the following arguments:</para>
    <orderedlist>
      <listitem>
        <para>x is the input value for the decode operation. This will
          generally be a column name.</para>
      </listitem>
      <listitem>
        <para>y is the literal string that contains a delimited set of
          input values and output values.</para>
      </listitem>
      <listitem>
        <para>z is an optional parameter on these methods that allows you
          to specify what delimiter the string specified in y uses.</para>
      </listitem>
    </orderedlist>
    <para>For example, your application might query a table called
      PARTS that contains a column called IS_IN_STOCK which contains a
      Boolean value that you need to change into an integer for your
      application to process. In this case, you can use the DECODEINTEGER
      function to change the Boolean values to integers:</para>
    <programlisting>SELECT DECODEINTEGER(IS_IN_STOCK, 'false, 0, true, 1') FROM PartsSupplier.PARTS;</programlisting> 
    <para>When the Teiid System encounters the value false in the
      result set, it replaces the value with 0.</para>
    <para>If, instead of using integers, your application requires
      string values, you can use the DECODESTRING function to return the
      string values you need:</para>
    <programlisting>SELECT DECODESTRING(IS_IN_STOCK, 'false, no, true, yes, null') FROM PartsSupplier.PARTS;</programlisting>
    <para>In addition to two input/output value pairs, this sample query
      provides a value to use if the column does not contain any of the
      preceding input values. If the row in the IS_IN_STOCK column does
      not contain true or false, the Teiid Server inserts a null into
      the result set.</para>
    <para>When you use these DECODE functions, you can provide as many
      input/output value pairs if you want within the string. By default,
      the Teiid System expects a comma delimiter, but you can add a
      third parameter to the function call to specify a different
      delimiter:</para>
    <programlisting>SELECT DECODESTRING(IS_IN_STOCK, 'false:no:true:yes:null',’:’) FROM PartsSupplier.PARTS;</programlisting>
    <para>You can use keyword null in the DECODE string as either an
      input value or an output value to represent a null value. However,
      if you need to use the literal string null as an input or output
      value (which means the word null appears in the column and not a
      null value) you can put the word in quotes: "null".</para>
    <programlisting>SELECT DECODESTRING( IS_IN_STOCK, 'null,no,"null",no,nil,no,false,no,true,yes' ) FROM PartsSupplier.PARTS;</programlisting>
    <para>If the DECODE function does not find a matching output value
      in the column and you have not specified a default value, the DECODE
      function will return the original value the Teiid Server found
      in that column.</para>
  </section>
  <section>
    <title>Lookup Function</title>
    <para>The Lookup function allows you to cache a key value pair table and access it through a scalar function. This caching
    accelerates response time to queries that use the lookup tables, known
    in business terminology as lookup tables or code tables.</para>
    <para><synopsis>LOOKUP(codeTable, returnColumn, keyColumn, keyValue)</synopsis></para>
    <para>In the lookup table codeTable, find the row where
      keyColumn has the value keyValue and return the
      associated returnColumn value or null if no matching key is found. codeTable must be a string literal that is the fully-qualified name of the target table.
      returnColumn and key Column must also be string literals of just the relevant column names.
      The keyValue can be any expression that must match the datatype of the keyColumn.  The return datatype
      matches that of returnColumn.
    </para>
    <example>
		<title>Country Code Lookup</title>
		<programlisting>lookup('ISOCountryCodes', 'CountryName', 'CountryCode', 'US')</programlisting>
		<para>A ISOCountryCodes table used to translate country name to
      ISO codes. One column, CountryName, represents a key column.
      A second column, CountryCode, would represent the ISO code of the country. 
      Hence, a query to this lookup table would
      provide a CountryName, shown above as 'US', and expect a CountryCode value in response.
		</para>
	</example>
    <para>When you call this function for any combination of codeTable, returnColumn, and
      keyColumn for the first time, the Teiid System caches the result. 
      The Teiid System uses this cache for all
      queries, in all sessions, that later access this lookup table.</para>
    <para>The Teiid System unloads these cached lookup tables when you stop and restart
      the Teiid System. Thus, you should not use this function for
      data that is subject to updates. Instead, you can use it against
      static data that does not change over time.</para>
    <para>See the Caching Guide for more on the caching aspects of the lookup function.</para>
    <note>
      <itemizedlist>
        <listitem>
          <para>The keyColumn is expected to contain unique
            values. If the column contains duplicate values, an exception will be thrown.</para>
        </listitem>
      </itemizedlist>
    </note>
  </section>
  <section>
    <title>System Functions</title>
    <para>System functions provide access to information in the Teiid system from within a query. </para>
      <section>
    	<title>COMMANDPAYLOAD</title>
    	<para>Retrieve a string from the command payload
                or null if no command payload was specified. The command
                payload is set by the <code>TeiidStatement.setPayload</code> method on the Teiid JDBC API
                extensions on a per-query basis.</para>
    	<para><synopsis>COMMANDPAYLOAD([key])</synopsis></para>
    	<para>If the key parameter is provided, the command payload object is cast to a
                java.util.Properties object and the corresponding property value for the key is returned.  If the key is not specified the return value is the command payload object toString value.</para>
    	<para>key, return value are strings</para>
     </section>
     <section>
    	<title>ENV</title>
    	<para>Retrieve a system environment property.</para>
    	<para><synopsis>ENV(key)</synopsis></para>
    	<para>To prevent untrusted access to system properties, this function is not enabled by default.  The ENV function may be enabled via the allowEnvFunction property in the &teiid-deployer-beans; file.</para>
    	<para>key, return value are strings</para>
     </section>
     <section>
    	<title>SESSION_ID</title>
    	<para>Retrieve the string form of the current session id.</para>
    	<para><synopsis>SESSION_ID()</synopsis></para>
    	<para>return value is string.</para>
     </section>
     <section>
    	<title>USER</title>
    	<para>Retrieve the name of the user executing the query.</para>
    	<para><synopsis>USER()</synopsis></para>
    	<para>return value is string.</para>
     </section>
     <section>
    	<title>CURRENT_DATABASE</title>
    	<para>Retrieve the catalog name of the database. The VDB name is always the catalog name.</para>
    	<para><synopsis>CURRENT_DATABASE()</synopsis></para>
    	<para>return value is string.</para>
     </section>
  </section>
  <section id="xml_functions">
    <title>XML Functions</title>
    <para>XML functions provide functionality for working with XML data. </para>
    <section>
    	<title>JSONTOXML</title>
    	<para>Returns an xml document from JSON.</para>
        <para><synopsis>JSONTOXML(rootElementName, json)</synopsis></para>
        <para>rootElementName is a string, json is in {clob, blob}. Return value is xml.</para>
        <para>The appropriate UTF encoding (8, 16LE. 16BE, 32LE, 32BE) will be detected for JSON blobs.  If another encoding is used, see the to_chars function.</para>
        <para>The result is always a well-formed XML document.</para>
        <para>The mapping to XML uses the following rules:
        <itemizedlist>
        	<listitem>
        		<para>The current element name is initially the rootElementName, and becomes the object value name as the JSON structure is traversed.</para>
        	</listitem>
        	<listitem>
        		<para>All element names must be valid xml 1.1 names.  Invalid names are fully escaped according to the SQLXML specification.</para>
        	</listitem>
        	<listitem>
        		<para>Each object or primitive value will be enclosed in an element with the current name.</para>
        	</listitem>
        	<listitem>
        		<para>Unless an array value is the root, it will not be enclosed in an additional element.</para>
        	</listitem>
        	<listitem>
        		<para>Null values will be represented by an empty element with the attribute xsi:nil="true"</para>
        	</listitem>
        </itemizedlist> 
        </para>
        <example>
        	<title>Sample JSON to XML for jsonToXml('person', x)</title>
        	<para>JSON:</para>
        	<programlisting language="JSON">{ "firstName" : "John" , "children" : [ "Randy", "Judy" ] }</programlisting>
        	<para>XML:</para>
        	<programlisting language="XML"><![CDATA[<?xml version="1.0" ?><person><firstName>John</firstName><children>Randy</children><children>Judy<children></person>]]></programlisting>
        </example>
        <example>
        	<title>Sample JSON to XML for jsonToXml('person', x) with a root array.</title>
        	<para>JSON:</para>
        	<programlisting language="JSON">[{ "firstName" : "George" }, { "firstName" : "Jerry" }]</programlisting>
        	<para>XML (Notice there is an extra "person" wrapping element to keep the XML well-formed):</para>
        	<programlisting language="XML"><![CDATA[<?xml version="1.0" ?><person><person><firstName>George</firstName></person><person><firstName>Jerry</firstName></person></person>]]></programlisting>
        </example>
    </section>
    <section>
    	<title>XMLCOMMENT</title>
    	<para>Returns an xml comment.</para>
    	<para><synopsis>XMLCOMMENT(comment)</synopsis></para>
    	<para>Comment is a string.  Return value is xml.</para>
    </section>
    <section>
    	<title>XMLCONCAT</title>
    	<para>Returns an XML with the concatination of the given xml types.</para>
    	<para><synopsis>XMLCONCAT(content [, content]*)</synopsis></para>
    	<para>Content is xml. Return value is xml.</para>
    	<para>If a value is null, it will be ignored.  If all values are null, null is returned.</para>
    </section>
    <section>
    	<title>XMLELEMENT</title>
    	<para>Returns an XML element with the given name and content.</para>
    	<para><synopsis>XMLELEMENT([NAME] name [, &lt;NSP&gt;] [, &lt;ATTR&gt;][, content]*)</synopsis></para>
          <para><synopsis>ATTR:=XMLATTRIBUTES(exp [AS name] [, exp [AS name]]*)</synopsis></para>
          <para><synopsis>NSP:=XMLNAMESPACES((uri AS prefix | DEFAULT uri | NO DEFAULT))+</synopsis></para>
          <para>If the content value is of a type other than xml, it will be escaped when added to the parent element.  Null content values are ignored.  
          Whitespace in XML or the string values of the content is preserved, but no whitespace is added between content values.</para>
          <para id="xmlnamespaces">XMLNAMESPACES is used provide namespace information.  NO DEFAULT is equivalent to defining the default namespace to the null uri - xmlns="".  
          Only one DEFAULT or NO DEFAULT namespace item may be specified.  The namespace prefixes xmlns and xml are reserved.</para>
          <para>If a attribute name is not supplied, the expression must be a column reference, in which case the attribute name will be the column name. Null attribute values are ignored.</para>
		<para>Name, prefix are identifiers. uri is a string literal. content can be any type. Return value is xml.  The return value is valid for use in places where a document is expected.</para>         
		<example>
		   <title>XMLELEMENT of mixed values</title>
		   <para>with an xml_value of &lt;doc/&gt;, <programlisting>XMLELEMENT(NAME "elem", 1, '&lt;2/&gt;', xml_value)</programlisting>
           		Returns: <code>&lt;elem&gt;1&amp;lt;2/&amp;gt;&lt;doc/&gt;&lt;elem/&gt;</code>
           </para>
		</example>   
    </section>
    <section>
    	<title>XMLFOREST</title>
    	<para>Returns an concatination of XML elements for each content item.</para>
    	<para><synopsis>XMLFOREST(content [AS name] [, &lt;NSP&gt;] [, content [AS name]]*)</synopsis></para>
        <para>See XMLELEMENT for the definition of NSP - <link linkend="xmlnamespaces">XMLNAMESPACES</link>.</para>
   		<para>Name is an identifier. Content can be any type. Return value is xml.</para>
		<para>If a name is not supplied for a content item, the expression must be a column reference, in which case the element name will be a partially escaped version of the column name.</para>
    </section>
    <section id="xmlparse">
    	<title>XMLPARSE</title>
    	<para>Returns an XML type representation of the string value expression.</para>
        <para><synopsis>XMLPARSE((DOCUMENT|CONTENT) expr [WELLFORMED])</synopsis></para>
        <para>expr in {string, clob, blob}. Return value is xml.</para>
        <para>If DOCIMENT is specfied then the expression must have a single
        root element and may or may not contain an XML declaration.</para>
        <para>
              If WELLFORMED is specified then validation is skipped; this is especially useful for CLOB and BLOB known to already be valid.  
        </para>
    </section>
    <section>
    	<title>XMLPI</title>
    	<para>Returns an xml processing instruction.</para>
        <para><synopsis>XMLPI([NAME] name [, content])</synopsis></para>
        <para>Name is an identifier. Content is a string. Return value is xml.</para>
    </section>
    <section id="xmlquery">
    	<title>XMLQUERY</title>
    	<para>Returns the XML result from evaluating the given xquery.</para>
    	<para><synopsis>XMLQUERY([&lt;NSP&gt;] xquery [&lt;PASSING&gt;] [(NULL|EMPTY) ON EMPTY]]</synopsis></para>
    	<para><synopsis>PASSING:=PASSING exp [AS name] [, exp [AS name]]*</synopsis></para>
    	<para>See XMLELEMENT for the definition of NSP - <link linkend="xmlnamespaces">XMLNAMESPACES</link>.</para><para>Namespaces may also be directly declared in the xquery prolog.</para>
             <para id="passing">The optional PASSING clause is used to provide the context item, which does not have a name, and named global variable values.  
             If the xquery uses a context item and none is provided, then an exception will be raised.  
             Only one context item may be specified and should be an XML type.  All non-context non-XML passing values will be converted to an appropriate XML type.</para>
             <para>The ON EMPTY clause is used to specify the result when the evaluted sequence is empty.  
             EMPTY ON EMPTY, the default, returns an empty XML result.  NULL ON EMPTY returns a null result.</para>
             <para>xquery in string. Return value is xml.</para>
             <para>XMLQUERY is part of the SQL/XML 2006 specification.</para>
             <para>See also <link linkend="xmltable">XMLTABLE</link></para>
             <note><para>See also <xref linkend="xquery_optimization"/></para></note>
    </section>
    <section id="xmlserialize">
    	<title>XMLSERIALIZE</title>
    	<para>Returns a character type representation of the xml expression.</para>
        <para><synopsis>XMLSERIALIZE([(DOCUMENT|CONTENT)] xml [AS datatype])</synopsis></para>
        <para>Return value mathces datatype.</para>
		<para>Only a character type (string, varchar, clob) may be specified as the datatype.  CONTENT is the default.  
              If DOCUMENT is specified and the xml is not a valid document or fragment, then an exception is raised. 
              </para>
    </section>
    <section>
    	<title>XSLTRANSFORM</title>
    	<para>Applies an XSL stylesheet to the given document.</para>
    	<para><synopsis>XSLTRANSFORM(doc, xsl)</synopsis></para>
    	<para>Doc, xsl in {string, clob, xml}. Return value is a clob.</para>
    	<para>If either argument is null, the result is null.</para>
    </section>
    <section>
    	<title>XPATHVALUE</title>
    	<para>Applies the XPATH expression to the document and returns a
               string value for the first matching result.</para>
    	<para><synopsis>XPATHVALUE(doc, xpath)</synopsis></para>
    	<para>Doc and xpath in {string, clob, xml}. Return value is a string.</para>
    	<para>Matching a non-text node will still produce a string result, which includes all descendent text nodes.</para>
    	<example>
        	<title>Sample xpathValue Ignoring Namespaces</title>
        	<para>XML value:</para>
        	<programlisting language="XML"><![CDATA[<?xml version="1.0" ?><ns1:return xmlns:ns1="http://com.test.ws/exampleWebService">Hello<x> World</x></return>]]></programlisting>
        	<para>Function:</para>
        	<programlisting language="SQL">xpathValue(value, '/*[local-name()="return"])</programlisting>
        	<para>Results in 'Hello World'</para>
        </example>
        <para>See also <xref linkend="xmlquery"/></para>
    </section>
  </section>
  <section>
    <title>Security Functions</title>
    <para>Security functions provide the ability to interact
    with the security system.</para>
    <section>
    	<title>HASROLE</title>
    	<para>Whether the current caller has the Teiid data role roleName.</para>
    	<para><synopsis>hasRole([roleType,] roleName)</synopsis></para>
        <para>roleName must be a string, the return type is boolean.</para>
        <para>The two argument form is provided for backwards compatibility. roleType is a string and must be 'data'.</para>
        <para>Role names are case-sensitive and only match Teiid <xref linkend="dataroles"/>. 
        JAAS roles/groups names are not valid for this function - unless there is corresponding data role with the same name.</para>
    </section>
  </section>
  <section>
    <title>Miscellaneous Functions</title>
    <para>Other functions.</para>
    <section id="array_get">
    	<title>array_get</title>
    	<para>Retuns the object value at a given array index.</para>
    	<para><synopsis>array_get(array, index)</synopsis></para>
        <para>array is the object type, index must be an integer, and the return type is object.</para>
        <para>1-based indexing is used.  The actual array value should be a java.sql.Array or java array type.  An exception will be thrown if the array value is the wrong type of the index is out of bounds.</para>
    </section>
    <section>
    	<title>array_length</title>
    	<para>Returns the length for a given array</para>
    	<para><synopsis>array_length(array)</synopsis></para>
        <para>array is the object type, and the return type is integer.</para>
        <para>The actual array value should be a java.sql.Array or java array type.  An exception will be thrown if the array value is the wrong type.</para>
    </section>
    <section>
    	<title>uuid</title>
    	<para>Retuns a universally unique identifier.</para>
    	<para><synopsis>uuid()</synopsis></para>
        <para>the return type is string.</para>
        <para>Generates a type 4 (pseudo randomly generated) UUID using a cryptographically strong random number generator.  The format is XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX where each X is a hex digit.</para>
    </section>
  </section>
  <section id="nondeterministic_functions">
  	<title>Nondeterministic Function Handling</title>
	<para>Teiid categorizes functions by varying degrees of determinism.
		When a function is evaluated and to what extent the result can be
		cached are based upon its determinism level.</para>
  	<orderedlist>
		<listitem>
			<para>Deterministic - the function will always return the same result
				for the given inputs. Deterministic functions are evaluated by the
				engine as soon as all input values are known, which may occur as soon
				as the rewrite phase. Some functions, such as the lookup function, are
				not truly deterministic, but is treated as such for performance. All
				functions not categorized below are considered deterministic.</para>
		</listitem>
		<listitem>
			<para>User Deterministic - the function will return the same
				result for the given inputs for the same user. This includes the hasRole
				and user functions. User deterministic functions are evaluated
				by the engine as soon as all input values are known, which may occur as soon
				as the rewrite phase. If a user deterministic function is evaluated
				during the creation of a prepared processing plan, then the resulting
				plan will be cached only for the user.</para>
		</listitem>
		<listitem>
			<para>Session Deterministic - the function will return the same
				result for the given inputs under the same user session. This category includes the env
				function. Session deterministic functions are evaluated
				by the engine as soon as all input values are known, which may occur as soon
				as the rewrite phase. If a session deterministic function is evaluated
				during the creation of a prepared processing plan, then the resulting
				plan will be cached only for the user's session.</para>
		</listitem>
		<listitem>
			<para>Command Deterministic - the result of function evaluation is
				only deterministic within the scope of the user command. This category
				include the curdate, curtime, now, and commandpayload functions.
				Command deterministic functions are delayed in evaluation until
				processing to ensure that even prepared plans utilizing these
				functions will be executed with relevant values. Command deterministic function
				evaulation will occur prior to pushdown - however multiple occurances
				of the same command deterministic time function are not guarenteed to
				evaluate to the same value. </para>
		</listitem>
		<listitem>
			<para>Nondeterministic - the result of function evaluation is fully
				nondeterministic. This category includes the rand function and UDFs
				marked as nondeterministic. Nondeterministic functions are delayed in
				evaluation until processing with a preference for pushdown. If the
				function is not pushed down, then it may be evaluated for every row in
				it's execution context (for example if the function is used in the
				select clause).</para>
		</listitem>
  	</orderedlist>
  </section>
</chapter>