Edit C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\odbc\7.0.1\help\reference\flatfile.13.2.html
<?xml version="1.0" encoding="utf-8"?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xml:lang="en" lang="en" xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html;charset=utf-8" /> <meta http-equiv="Content-Style-Type" content="text/css" /> <!-- MOTW-DISABLED saved from url=(0014)about:internet --> <title>Select Statement</title> <link rel="StyleSheet" href="css/flatfile.css" type="text/css" media="all" /> <link rel="StyleSheet" href="css/webworks.css" type="text/css" media="all" /> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/context.js"></script> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/towwhdir.js"></script> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/wwhpagef.js"></script> <script type="text/javascript" language="JavaScript1.2"> <!-- var WebWorksRootPath = ""; // --> </script> <script type="text/javascript" language="JavaScript1.2"> <!-- // Set reference to top level help frame // var WWHFrame = WWHGetWWHFrame("", true); // --> </script> <script type="text/javascript" language="JavaScript1.2" src="scripts/expand.js"></script> </head> <body class="" style="background-color: #FFFFEE;" onload="WWHUpdate();" onunload="WWHUnload();" onkeydown="WWHHandleKeyDown((document.all||document.getElementById||document.layers)?event:null);" onkeypress="WWHHandleKeyPress((document.all||document.getElementById||document.layers)?event:null);" onkeyup="WWHHandleKeyUp((document.all||document.getElementById||document.layers)?event:null);"> <br /> <div class="WebWorks_Breadcrumbs" style="text-align: left;"> <a class="WebWorks_Breadcrumb_Link" href="flatfile.13.1.html#106182">11 SQL Statements for Flat-File Drivers</a> : Select Statement</div> <hr align="left" /> <blockquote> <div class="head_1"><a name="106182">Select Statement</a></div> <div class="Body"><a name="106183">The form of the Select statement supported by the flat-file drivers is:</a></div> <div class="syntax_first"><a name="106184">SELECT [DISTINCT] {* | </a><span class="EquationVariables">column_expression</span>, ...}</div> <div class="syntax"><a name="106185">FROM </a><span class="EquationVariables">table_names</span> [<span class="EquationVariables">table_alias</span>] ...</div> <div class="syntax"><a name="106186">[ WHERE </a><span class="EquationVariables">expr1</span> <span class="EquationVariables">rel_operator</span> <span class="EquationVariables">expr2</span> ]</div> <div class="syntax"><a name="106187">[ GROUP BY {</a><span class="EquationVariables">column_expression</span>, ...} ]</div> <div class="syntax"><a name="106188">[ HAVING </a><span class="EquationVariables">expr1</span> <span class="EquationVariables">rel_operator</span> <span class="EquationVariables">expr2</span> ]</div> <div class="syntax"><a name="106189">[ UNION [ALL] (SELECT...) ]</a></div> <div class="syntax"><a name="106190">[ ORDER BY {</a><span class="EquationVariables">sort_expression</span> [DESC | ASC]}, ... ]</div> <div class="syntax"><a name="106191">[ FOR UPDATE [OF {</a><span class="EquationVariables">column_expression</span>, ...}] ]</div> <div class="head_2"><a name="106195">Select Clause</a></div> <div class="Body"><a name="106196">Follow Select with a list of column expressions you want to retrieve or an asterisk (*) to </a>retrieve all fields.</div> <div class="syntax_first"><a name="106197">SELECT [DISTINCT] {* | </a><span class="EquationVariables">column_expression</span>, [[AS] <span class="EquationVariables">column_alias</span>]. . .}</div> <div class="Body"><span class="EquationVariables"><a name="106198">column_expression</a></span> can be simply a field name (for example, LAST_NAME). More complex expressions may include mathematical operations or string manipulation (for example, SALARY * 1.05). See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'flatfile.13.2.html#106343', '');">â??SQL Expressionsâ??</a></span> for details.</div> <div class="Body"><span class="EquationVariables"><a name="106202">column_alias</a></span> can be used to give the column a descriptive name. For example, to assign the alias DEPARTMENT to the column DEP:</div> <div class="syntax_first"><a name="106203">SELECT dep AS department FROM emp</a></div> <div class="Body"><a name="106204">Separate multiple column expressions with commas (for example, LAST_NAME, </a>FIRST_NAME, HIRE_DATE).</div> <div class="Body"><a name="106205">Field names can be prefixed with the table name or alias. For example, EMP.LAST_NAME </a>or E.LAST_NAME, where E is the alias for the table EMP.</div> <div class="Body"><a name="106206">The Distinct operator can precede the first column expression. This operator eliminates </a>duplicate rows from the result of a query. For example:</div> <div class="syntax_first"><a name="106207">SELECT DISTINCT dep FROM emp</a></div> <div class="head_3"><a name="106210">Aggregate Functions</a></div> <div class="Body"><a name="106211">Aggregate functions can also be a part of a Select clause. Aggregate functions return a </a>single value from a set of rows. An aggregate can be used with a field name (for example, AVG(SALARY)) or in combination with a more complex column expression (for example, AVG(SALARY * 1.07)). The column expression can be preceded by the Distinct operator. The Distinct operator eliminates duplicate values from an aggregate expression. For example:</div> <div class="syntax_first"><a name="106212">COUNT (DISTINCT last_name)</a></div> <div class="Body"><a name="106213">In this example, only distinct last name values are counted.</a></div> <div class="Body"><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'flatfile.13.2.html#106221', '');" name="106217">Table 11-1</a></span> lists valid aggregate functions.</div> <table class="Format_A" cellspacing="0" summary=""> <caption> <div class="table_title">Table 11-1. <span style="color: #000000; font-size: 10.0pt; font-style: italic; font-variant: normal; font-weight: bold; text-transform: none; vertical-align: baseline;"><a name="106221">Aggregate Functions</a></span></div> </caption> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="106225">Aggregate</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="106227">Returns</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106229">SUM</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106231">The total of the values in a numeric field expression. For example, </a>SUM(SALARY) returns the sum of all salary field values.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106233">AVG</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106235">The average of the values in a numeric field expression. For example, </a>AVG(SALARY) returns the average of all salary field values.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106237">COUNT</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106239">The number of values in any field expression. For example, </a>COUNT(NAME) returns the number of name values. When using COUNT with a field name, COUNT returns the number of non-NULL field values. A special example is COUNT(*), which returns the number of rows in the set, including rows with NULL values.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106241">MAX</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106243">The maximum value in any field expression. For example, </a>MAX(SALARY) returns the maximum salary field value.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106245">MIN</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106247">The minimum value in any field expression. For example, </a>MIN(SALARY) returns the minimum salary field value.</div> </td> </tr> </table> <div class="head_2"><a name="106252">From Clause</a></div> <div class="Body"><a name="106253">The From clause indicates the tables to be used in the Select statement. The format of the </a>From clause is:</div> <div class="syntax_first"><a name="106254">FROM </a><span class="EquationVariables">table_names</span> [<span class="EquationVariables">table_alias</span>]</div> <div class="Body"><span class="EquationVariables"><a name="106255">table_names</a></span> can be one or more simple table names in the current working directory or complete path names.</div> <div class="Body"><span class="EquationVariables"><a name="106256">table_alias</a></span> is a name used to refer to a table in the rest of the Select statement. Database field names may be prefixed by the table alias. Given the table specification:</div> <div class="syntax_first"><a name="106257">FROM emp E</a></div> <div class="Body"><a name="106258">you may refer to the LAST_NAME field as E.LAST_NAME. Table aliases must be used if </a>the Select statement joins a table to itself. For example:</div> <div class="syntax_first"><a name="106259">SELECT * FROM emp E, emp F WHERE E.mgr_id = F.emp_id</a></div> <div class="Body"><a name="106260">The equal sign (=) includes only matching rows in the results. </a></div> <div class="Body"><a name="106261">If you are joining more than one table, you can use LEFT OUTER JOIN, which includes </a>non-matching rows in the first table you name. For example:</div> <div class="syntax_first"><a name="106262">SELECT * FROM T1 LEFT OUTER JOIN T2 on T1.key = T2.key </a></div> <div class="head_2"><a name="106265">Where Clause</a></div> <div class="Body"><a name="106266">The Where clause specifies the conditions that rows must meet to be retrieved. The Where </a>clause contains conditions in the form:</div> <div class="syntax_first"><a name="106267">WHERE </a><span class="EquationVariables">expr1</span> <span class="EquationVariables">rel_operator</span> <span class="EquationVariables">expr2</span></div> <div class="Body"><span class="EquationVariables"><a name="106268">expr1</a></span> and <span class="EquationVariables">expr2</span> can be field names, constant values, or expressions.</div> <div class="Body"><span class="EquationVariables"><a name="106269">rel_operator</a></span> is the relational operator that links the two expressions. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'flatfile.13.2.html#106343', '');">â??SQL Expressionsâ??</a></span> for details.</div> <div class="Body"><a name="106273">For example, the following Select statement retrieves the names of employees that make at </a>least $20,000.</div> <div class="syntax_first"><a name="106274">SELECT last_name,first_name FROM emp WHERE salary >= 20000</a></div> <div class="head_2"><a name="106277">Group By Clause</a></div> <div class="Body"><a name="106278">The Group By clause specifies the names of one or more fields by which the returned </a>values should be grouped. This clause is used to return a set of aggregate values. It has the following form:</div> <div class="syntax_first"><a name="106279">GROUP BY </a><span class="EquationVariables">column_expressions</span></div> <div class="Body"><span class="EquationVariables"><a name="106280">column_expressions</a></span> must match the column expression used in the Select clause. A column expression can be one or more field names of the database table, separated by a comma (,) or one or more expressions, separated by a comma (,). See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'flatfile.13.2.html#106343', '');">â??SQL Expressionsâ??</a></span> for details.</div> <div class="Body"><a name="106284">The following example sums the salaries in each department:</a></div> <div class="syntax_first"><a name="106285">SELECT dept_id, sum(salary) FROM emp GROUP BY dept_id</a></div> <div class="Body"><a name="106286">This statement returns one row for each distinct department ID. Each row contains the </a>department ID and the sum of the salaries of the employees in the department.</div> <div class="head_2"><a name="106289">Having Clause</a></div> <div class="Body"><a name="106290">The Having clause enables you to specify conditions for groups of rows (for example, </a>display only the departments that have salaries totaling more than $200,000). This clause is valid only if you have already defined a Group By clause. It has the following form:</div> <div class="syntax_first"><a name="106291">HAVING </a><span class="EquationVariables">expr1</span> <span class="EquationVariables">rel_operator</span> <span class="EquationVariables">expr2</span></div> <div class="Body"><span class="EquationVariables"><a name="106292">expr1</a></span> and <span class="EquationVariables">expr2</span> can be field names, constant values, or expressions. These expressions do not have to match a column expression in the Select clause.</div> <div class="Body"><span class="EquationVariables"><a name="106293">rel_operator</a></span> is the relational operator that links the two expressions. See <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'flatfile.13.2.html#106343', '');">â??SQL Expressionsâ??</a></span> for details.</div> <div class="Body"><a name="108631">The following example returns only the departments whose sums of salaries are greater </a>than $200,000:</div> <div class="syntax_first"><a name="108632">SELECT dept_id, sum(salary) FROM emp</a></div> <div class="syntax"><a name="108633">GROUP BY dept_id HAVING sum(salary) > 200000</a></div> <div class="head_2"><a name="106304">Union Operator</a></div> <div class="Body"><a name="106305">The Union operator combines the results of two Select statements into a single result. The </a>single result is all the returned rows from both Select statements. By default, duplicate rows are not returned. To return duplicate rows, use the All keyword (UNION ALL). The form is:</div> <div class="syntax_first"><a name="106306">SELECT statement</a></div> <div class="syntax"><a name="106307">UNION ALL</a></div> <div class="syntax"><a name="106308">SELECT </a><span class="EquationVariables">statement</span> </div> <div class="Body"><a name="106309">When using the Union operator, the Select lists for each Select statement must have the </a>same number of column expressions with the same data types, and must be specified in the same order. For example:</div> <div class="syntax_first"><a name="106310">SELECT last_name, salary, hire_date FROM emp</a></div> <div class="syntax"><a name="106311">UNION</a></div> <div class="syntax"><a name="106312">SELECT name, pay, birth_date FROM person</a></div> <div class="Body"><a name="106313">This example has the same number of column expressions, and each column expression, </a>in order, has the same data type.</div> <div class="Body"><a name="106314">The following example is </a><span class="Emphasis">not</span> valid because the data types of the column expressions are different (salary from emp has a different data type than last_name from raises). This example does have the same number of column expressions in each Select statement but the expressions are not in the same order by data type.</div> <div class="syntax_first"><a name="106315">SELECT last_name, salary FROM emp</a></div> <div class="syntax"><a name="106316">UNION</a></div> <div class="syntax"><a name="106317">SELECT salary, last_name FROM raises</a></div> <div class="head_2"><a name="106320">Order By Clause</a></div> <div class="Body"><a name="106321">The Order By clause indicates how the rows are to be sorted. The form is:</a></div> <div class="syntax_first"><a name="106322">ORDER BY {</a><span class="EquationVariables">sort_expression</span> [DESC | ASC]}, ...</div> <div class="Body"><span class="EquationVariables"><a name="106323">sort_expression</a></span> can be field names, expressions, or the positioned number of the column expression to use.</div> <div class="Body"><a name="106324">The default is to perform an ascending (ASC) sort.</a></div> <div class="Body"><a name="106325">For example, to sort by last_name and then by first_name, you could use either of the </a>following Select statements:</div> <div class="syntax_first"><a name="106326">SELECT emp_id, last_name, first_name FROM emp </a></div> <div class="syntax"><a name="106327">ORDER BY last_name, first_name</a></div> <div class="Body"><a name="106328">or</a></div> <div class="syntax_first"><a name="106329">SELECT emp_id, last_name, first_name FROM emp </a></div> <div class="syntax"><a name="106330">ORDER BY 2,3</a></div> <div class="Body"><a name="106331">In the second example, last_name is the second column expression following Select, so </a>Order By 2 sorts by last_name.</div> <div class="head_2"><a name="106333">For Update Clause</a></div> <div class="Body"><a name="106334">The For Update clause locks the rows of the database table selected by the Select </a>statement. The form is:</div> <div class="syntax_first"><a name="106335">FOR UPDATE OF </a><span class="EquationVariables">column_expressions</span></div> <div class="Body"><span class="EquationVariables"><a name="106336">column_expressions</a></span> is a list of field names in the database table that you intend to update, separated by a comma (,).</div> <div class="Body"><a name="106337">The following example returns all rows in the employee database that have a salary field </a>value of more than $20,000. When each record is fetched, it is locked. If the record is updated or deleted, the lock is held until you commit the change. Otherwise, the lock is released when you fetch the next record.</div> <div class="syntax_first"><a name="106338">SELECT * FROM emp WHERE salary > 20000</a></div> <div class="syntax"><a name="106339"> </a> FOR UPDATE OF last_name, first_name, salary</div> <div class="head_2"><a name="106343">SQL Expressions</a></div> <div class="Body"><a name="106344">Expressions are used in the Where clauses, Having clauses, and Order By clauses of SQL </a>Select statements.</div> <div class="Body"><a name="106345">Expressions enable you to use mathematical operations as well as character string and </a>date manipulation operators to form complex database queries.</div> <div class="Body"><a name="109127">The most common expression is a simple field name. You can combine a field name with </a>other expression elements.</div> <div class="Body"><a name="109205">Valid expression elements are as follows:</a></div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="109272">Field Names</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="109274">Constants</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="109418">Exponential notation</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="109295">Numeric operators</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="109310">Character operators</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="109325">Date operators</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="109338">Relational operators</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="109349">Logical operators</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="109360">Functions</a></div> </td> </tr> </table> </div> <div class="head_3"><a name="109191">Constants</a></div> <div class="Body"><a name="106370">Constants are values that do not change. For example, in the expression PRICE * 1.05, the </a>value 1.05 is a constant.</div> <div class="Body"><a name="106371">You must enclose character constants in pairs of single (') or double (") quotation marks. To </a>include a single quotation mark in a character constant enclosed by single quotation marks, use two single quotation marks together (for example, 'Don''t'). Similarly, if the constant is enclosed by double quotation marks, use two double quotation marks to include one.</div> <div class="Body"><a name="106372">You must enclose date and time constants in braces ({}), for example, {01/30/89} and </a>{12:35:10}. The form for date constants is MM/DD/YY or MM/DD/YYYY. The form for time constants is HH:MM:SS.</div> <div class="Body"><a name="106373">The logical constants are .T. and 1 for True and .F. and 0 for False. For portability, use 1 and </a>0.</div> <div class="head_3"><a name="106374">Exponential Notation</a></div> <div class="Body"><a name="106375">You can include exponential notation in expression elements. For example:</a></div> <div class="syntax_first"><a name="106376">SELECT col1, 3.4E+7 FROM table1 WHERE calc < 3.4E-6 * col2</a></div> <div class="head_3"><a name="106377">Numeric Operators</a></div> <div class="Body"><a name="106378">You can include the following operators in numeric expressions:</a></div> <table class="Format_B" cellspacing="0" summary=""> <caption></caption> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="106381">Operator</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="106383">Meaning</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106385"> </a>+</div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106387">Addition</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106389"> </a>â??</div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106391">Subtraction</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106393"> </a>*</div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106395">Multiplication</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106397"> </a>/</div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106399">Division</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106401"> </a>**</div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106403">Exponentiation</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106405"> </a>^</div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106407">Exponentiation</a></div> </td> </tr> </table> <div class="Body"><a name="106409">The following table shows examples of numeric expressions. For these examples, assume </a>salary is 20000.</div> <table class="Format_B" cellspacing="0" summary=""> <caption></caption> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="106412">Example</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="106414">Resulting value</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text_syntax"><span class="Syntax"><a name="106416">salary + 10000</a></span></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text_syntax"><span class="Syntax"><a name="106418">30000</a></span></div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text_syntax"><span class="Syntax"><a name="106420">salary * 1.1</a></span></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text_syntax"><span class="Syntax"><a name="106422">22000</a></span></div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text_syntax"><span class="Syntax"><a name="106424">2 ** 3</a></span></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text_syntax"><span class="Syntax"><a name="106426">8</a></span></div> </td> </tr> </table> <div class="Body"><a name="106428">You can precede numeric expressions with a unary plus (+) or minus (â??). For example, </a><span class="Syntax">â??(salary * 1.1)</span> is -22000.</div> <div class="head_3"><a name="106429">Character Operators</a></div> <div class="Body"><a name="106430">Character expressions can include the following operators:</a></div> <table class="Format_B" cellspacing="0" summary=""> <caption></caption> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="106433">Operator</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="106435">Meaning</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106437"> </a>+</div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106439">Concatenation, keeping trailing blanks.</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106441"> </a>â??</div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106443">Concatenation, moving trailing blanks to the end.</a></div> </td> </tr> </table> <div class="Body"><a name="106445">The following table shows examples of character expressions. In the examples, last_name </a>is <span class="Syntax">'JONES</span><span class="Syntax"> </span><span class="Syntax"> </span><span class="Syntax">'</span> and first_name is <span class="Syntax">'ROBERT</span><span class="Syntax"> </span><span class="Syntax"> </span><span class="Syntax">'</span>.</div> <table class="Format_B" cellspacing="0" summary=""> <caption></caption> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="109547">Example</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="109549">Resulting Value</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text_syntax"><a name="109551">first_name + last_name</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text_syntax"><a name="109553">'ROBERT</a> JONES '</div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text_syntax"><a name="109555">first_name â?? last_name</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text_syntax"><a name="109557">'ROBERTJONES</a> '</div> </td> </tr> </table> <div class="Body"><a name="106460">NOTE: Some flat-file drivers return character data with trailing blanks as shown in the table; </a>however, you cannot rely on the driver to return blanks. If you want an expression that works regardless of whether the drivers return trailing blanks, use the TRIM function before concatenating strings to make the expression portable. For example:</div> <div class="syntax_first"><a name="106461">TRIM(first_name) + '' + TRIM(last_name)</a></div> <div class="head_3"><a name="106462">Date Operators</a></div> <div class="Body"><a name="106463">You can include the following operators in date expressions:</a></div> <table class="Format_B" cellspacing="0" summary=""> <caption></caption> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="106466">Operator</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="106468">Meaning</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106470">+</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106472">Add a number of days to a date to produce a new date.</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106474">â??</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106476">The number of days between two dates, or subtract a </a>number of days from a date to produce a new date.</div> </td> </tr> </table> <div class="Body"><a name="106478">The following table shows examples of date expressions. In these examples, hire_date is </a>{01/30/1990}.</div> <table class="Format_B" cellspacing="0" summary=""> <caption></caption> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="108922">Example</a></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="108924">Resulting Value</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text_syntax"><span class="Syntax"><a name="108926">hire_date + 5</a></span></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text_syntax"><span class="Syntax"><a name="108928">{02/04/1990}</a></span></div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text_syntax"><span class="Syntax"><a name="108930">hire_date â?? {01/01/1990}</a></span></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text_syntax"><span class="Syntax"><a name="108932">29</a></span></div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text_syntax"><span class="Syntax"><a name="108934">hire_date â?? 10</a></span></div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text_syntax"><span class="Syntax"><a name="108936">{01/20/1990}</a></span></div> </td> </tr> </table> <div class="head_3"><a name="106497">Relational Operators</a></div> <div class="Body"><a name="106498">Relational operators separating any two expressions can be any one of those listed in </a><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'flatfile.13.2.html#106508', '');">Table 11-2</a></span>.</div> <table class="Format_A" cellspacing="0" summary=""> <caption> <div class="table_title">Table 11-2. <a name="106508">Relational Operators</a> </div> </caption> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="106512">Operator</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="106514">Meaning</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106516">=</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106518">Equal.</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106520"><></a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106522">Not Equal.</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106524">></a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106526">Greater Than.</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106528">>=</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106530">Greater Than or Equal.</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106532"><</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106534">Less Than.</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106536"><=</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106538">Less Than or Equal.</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106540">Like</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106542">Matching a pattern.</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106544">Not Like</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106546">Not matching a pattern.</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106548">Is NULL</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106550">Equal to NULL.</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106552">Is Not NULL</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106554">Not Equal to NULL.</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106556">Between</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106558">Range of values between a lower and upper bound.</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106560">In</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106562">A member of a set of specified values or a member of a subquery.</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106564">Exists</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106566">True if a subquery returned at least one record.</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106568">Any</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106570">Compares a value to each value returned by a subquery. Any must be </a>prefaced by =, <>, >, >=, <, or <=.</div> <div class="table_text"><a name="106571">=Any is equivalent to In.</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106573">All</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106575">Compares a value to each value returned by a subquery. All must be </a>prefaced by =, <>, >, >=, <, or <=.</div> </td> </tr> </table> <div class="Body"><a name="106578">The following list shows some examples of relational operators:</a></div> <div class="syntax_first"><a name="106579">salary <= 40000</a></div> <div class="syntax"><a name="106580">dept = 'D101'</a></div> <div class="syntax"><a name="106581">hire_date > {01/30/1989}</a></div> <div class="syntax"><a name="106582">salary + commission >= 50000</a></div> <div class="syntax"><a name="106583">last_name LIKE 'Jo%'</a></div> <div class="syntax"><a name="106584">salary IS NULL</a></div> <div class="syntax"><a name="106585">salary BETWEEN 10000 AND 20000</a></div> <div class="syntax"><a name="106586">WHERE salary = ANY (SELECT salary FROM emp WHERE dept = 'D101')</a></div> <div class="syntax"><a name="106587">WHERE salary > ALL (SELECT salary FROM emp WHERE dept = 'D101')</a></div> <div class="head_3"><a name="106588">Logical Operators</a></div> <div class="Body"><a name="106589">Two or more conditions may be combined to form more complex criteria. When two or more </a>conditions are present, they must be related by AND or OR. For example:</div> <div class="syntax_first"><a name="106590">salary = 40000 AND exempt = 1</a></div> <div class="Body"><a name="106591">The logical NOT operator is used to reverse the meaning. For example:</a></div> <div class="syntax_first"><a name="106592">NOT (salary = 40000 AND exempt = 1)</a></div> <div class="head_3"><a name="106594">Operator Precedence</a></div> <div class="Body"><a name="106595">As expressions become more complex, the order in which the expressions are evaluated </a>becomes important. <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'flatfile.13.2.html#106605', '');">Table 11-3</a></span> shows the order in which the operators are evaluated. The operators in the first line are evaluated first, then those in the second line, and so on. Operators in the same line are evaluated left to right in the expression.</div> <table class="Format_A" cellspacing="0" summary=""> <caption> <div class="table_title">Table 11-3. <a name="106605">Operator Precedence</a> </div> </caption> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="106609">Precedence</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="106611">Operator</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106613">1</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106615">Unary -, Unary +</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106617">2</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106619">**</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106621">3</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106623">*, /</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106625">4</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106627">+, - </a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106629">5</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106631">=, <>, <, <=, >, >=, LIKE, NOT LIKE, IS NULL, IS NOT NULL, </a>BETWEEN, IN, EXISTS, ANY, ALL</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106633">6</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106635">NOT</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106637">7</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106639">AND</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106641">8</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106643">OR</a></div> </td> </tr> </table> <div class="Body"><a name="106646">The following example shows the importance of precedence:</a></div> <div class="syntax_first"><a name="106647">WHERE salary > 40000 OR </a></div> <div class="syntax"><a name="106648">hire_date > {01/30/1989} AND </a></div> <div class="syntax"><a name="106649">dept = 'D101'</a></div> <div class="Body"><a name="106650">Because AND is evaluated first, this query retrieves employees in department D101 hired </a>after January 30, 1989, as well as every employee making more than $40,000, no matter what department or hire date.</div> <div class="Body"><a name="106651">To force the clause to be evaluated in a different order, use parentheses to enclose the </a>conditions to be evaluated first. For example:</div> <div class="syntax_first"><a name="106652">WHERE (salary > 40000 OR hire_date > {01/30/1989})</a></div> <div class="syntax"><a name="106653">AND dept = 'D101'</a></div> <div class="Body"><a name="106654">retrieves employees in department D101 that either make more than $40,000 or were hired </a>after January 30, 1989.</div> <div class="head_3"><a name="106655">Functions</a></div> <div class="Body"><a name="106656">The flat-file drivers support a number of functions that you may use in expressions. In </a><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'flatfile.13.2.html#106669', '');">Table 11-4</a></span> through <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'flatfile.13.2.html#106872', '');">Table 11-6</a></span>, the functions are grouped according to the type of result they return.</div> <table class="Format_E" cellspacing="0" summary=""> <caption> <div class="table_title_wide">Table 11-4. <a name="106669">Functions that Return Character Strings</a> </div> </caption> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="106673">Function</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="106675">Description</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106677">CHR</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106679">Converts an ASCII code into a one-character string.</a></div> <div class="table_text"><span class="Syntax"><a name="106680">CHR(67)</a></span> returns <span class="Syntax">C</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106682">RTRIM</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="109068">Removes trailing blanks from a string.</a></div> <div class="table_text"><span class="Syntax"><a name="106685">RTRIM('ABC</a></span><span class="Syntax"> </span><span class="Syntax"> </span><span class="Syntax">')</span> returns <span class="Syntax">ABC</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106687">TRIM</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106689">Removes trailing blanks from a string.</a></div> <div class="table_text"><span class="Syntax"><a name="106690">TRIM('ABC</a></span><span class="Syntax"> </span><span class="Syntax"> </span><span class="Syntax">')</span> returns <span class="Syntax">ABC</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106692">LTRIM</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106694">Removes leading blanks from a string.</a></div> <div class="table_text"><span class="Syntax"><a name="106695">LTRIM('</a></span><span class="Syntax"> </span><span class="Syntax"> </span><span class="Syntax">ABC')</span> returns <span class="Syntax">ABC</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106697">UPPER</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106699">Changes each letter of a string to uppercase.</a></div> <div class="table_text"><span class="Syntax"><a name="106700">UPPER('Allen')</a></span> returns <span class="Syntax">ALLEN</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106702">LOWER</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106704">Changes each letter of a string to lowercase.</a></div> <div class="table_text"><span class="Syntax"><a name="106705">LOWER('Allen')</a></span> returns <span class="Syntax">allen</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106707">LEFT</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106709">Returns leftmost characters of a string.</a></div> <div class="table_text"><span class="Syntax"><a name="106710">LEFT('Mattson',3)</a></span> returns <span class="Syntax">Mat</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106712">RIGHT</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106714">Returns rightmost characters of a string.</a></div> <div class="table_text"><span class="Syntax"><a name="106715">RIGHT('Mattson',4)</a></span> returns <span class="Syntax">tson</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106717">SUBSTR</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106719">Returns a substring of a string. Parameters are the string, the first </a>character to extract, and the number of characters to extract (optional).</div> <div class="table_text"><span class="Syntax"><a name="106720">SUBSTR('Conrad',2,3)</a></span> returns <span class="Syntax">onr</span>.</div> <div class="table_text"><span class="Syntax"><a name="106721">SUBSTR('Conrad',2)</a></span> returns <span class="Syntax">onrad</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106723">SPACE</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106725">Generates a string of blanks.</a></div> <div class="table_text"><span class="Syntax"><a name="106726">SPACE(5)</a></span> returns ' '.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106728">DTOC</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106730">Converts a date to a character string. An optional second parameter </a>determines the format of the result:</div> <div class="table_text"><a name="106731">0 (the default) returns MM/DD/YY.</a></div> <div class="table_text"><a name="106732">1 returns DD/MM/YY.</a></div> <div class="table_text"><a name="106733">2 returns YY/MM/DD.</a></div> <div class="table_text"><a name="106734">10 returns MM/DD/YYYY.</a></div> <div class="table_text"><a name="106735">11 returns DD/MM/YYYY.</a></div> <div class="table_text"><a name="106736">12 returns YYYY/MM/DD.</a></div> <div class="table_text"><a name="106737">An optional third parameter specifies the date separator character. If not </a>specified, a slash (/) is used.</div> <div class="table_text"><span class="Syntax"><a name="106738">DTOC({01/30/1997})</a></span> returns <span class="Syntax">01/30/97</span>.</div> <div class="table_text"><span class="Syntax"><a name="106739">DTOC({01/30/1997}, 0)</a></span> returns <span class="Syntax">01/30/97</span>.</div> <div class="table_text"><span class="Syntax"><a name="106740">DTOC({01/30/1997}, 1)</a></span> returns <span class="Syntax">30/01/97</span>.</div> <div class="table_text"><span class="Syntax"><a name="106741">DTOC({01/30/1997}, 2,'-')</a></span> returns <span class="Syntax">97-01-30</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106743">DTOS</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106745">Converts a date to a character string using the format YYYYMMDD.</a></div> <div class="table_text"><span class="Syntax"><a name="106746">DTOS({01/23/1990})</a></span> returns <span class="Syntax">19900123</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106748">IIF</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106750">Returns one of two values, true or false. Parameters are a logical </a>expression, the true value, and the false value. If the logical expression evaluates to true, the function returns the true value. Otherwise, it returns the false value.</div> <div class="table_text"><span class="Syntax"><a name="106751">IIF(salary>20000,'BIG','SMALL')</a></span> returns <span class="Syntax">BIG</span> if <span class="Syntax">salary</span> is greater than 20000. If not, it returns <span class="Syntax">SMALL</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106753">STR</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106755">Converts a number to a character string. Parameters are the number, </a>the total number of output characters (including the decimal point), and optionally the number of digits to the right of the decimal point.</div> <div class="table_text"><span class="Syntax"><a name="106756">STR(12.34567,4)</a></span> returns <span class="Syntax">12</span>.</div> <div class="table_text"><span class="Syntax"><a name="106757">STR(12.34567,4,1)</a></span> returns <span class="Syntax">12.3</span>.</div> <div class="table_text"><span class="Syntax"><a name="106758">STR(12.34567,6,3)</a></span> returns <span class="Syntax">12.346</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106760">STRVAL</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106762">Converts a value of any type to a character string.</a></div> <div class="table_text"><span class="Syntax"><a name="106763">STRVAL('Woltman')</a></span> returns <span class="Syntax">Woltman</span>.</div> <div class="table_text"><span class="Syntax"><a name="106764">STRVAL({12/25/1953})</a></span> returns <span class="Syntax">12/25/1953</span>.</div> <div class="table_text"><span class="Syntax"><a name="106765">STRVAL (5 * 3)</a></span> returns <span class="Syntax">15</span>.</div> <div class="table_text"><span class="Syntax"><a name="106766">STRVAL (4 = 5)</a></span> returns <span class="Syntax">'False'</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106768">TIME</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106770">Returns the time of day as a character string.</a></div> <div class="table_text"><a name="106771">At 9:49 PM, </a><span class="Syntax">TIME()</span> returns <span class="Syntax">21:49:00</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106773">TTOC</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106775">NOTE: This function applies only to flat-file drivers that support </a>SQL_TIMESTAMP: the Btrieve driver and the dBASE (access to FoxPro 3.0) driver.</div> <div class="table_text"><a name="106776">Converts a timestamp to a character string. An optional second </a>parameter determines the format of the result:</div> <div class="table_text"><a name="106777">When set to 0 or none (the default), MM/DD/YY HH:MM:SS</a> AM is returned.</div> <div class="table_text"><a name="106778">When set to 1, YYYYMMDDHHMMSS is returned, which is a suitable </a>format for indexing.</div> <div class="table_text"><span class="Syntax"><a name="106779">TTOC({1992-04-02 03:27:41})</a></span> returns 04/02/92 03:27:41 AM.</div> <div class="table_text"><span class="Syntax"><a name="106780">TTOC({1992-04-02 03:27:41, 1})</a></span> returns 19920402032741</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106782">USERNAME</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106784">For Btrieve, the logon ID specified at connect time is returned. For all </a>other flat-file drivers, an empty string is returned.</div> </td> </tr> </table> <table class="Format_E" cellspacing="0" summary=""> <caption> <div class="table_title_wide">Table 11-5. <a name="106791">Functions that Return Numbers</a> </div> </caption> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="106795">Function</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="106797">Description</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106799">MOD</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106801">Divides two numbers and returns the remainder of the division.</a></div> <div class="table_text"><span class="Syntax"><a name="106802">MOD(10,3)</a></span> returns <span class="Syntax">1</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106804">LEN</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106806">Returns the length of a string.</a></div> <div class="table_text"><span class="Syntax"><a name="106807">LEN('ABC')</a></span> returns <span class="Syntax">3</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106809">MONTH</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106811">Returns the month part of a date.</a></div> <div class="table_text"><span class="Syntax"><a name="106812">MONTH({01/30/1989})</a></span> returns <span class="Syntax">1</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106814">DAY</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106816">Returns the day part of a date.</a></div> <div class="table_text"><span class="Syntax"><a name="106817">DAY({01/30/1989})</a></span> returns <span class="Syntax">30</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106819">YEAR</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106821">Returns the year part of a date.</a></div> <div class="table_text"><span class="Syntax"><a name="106822">YEAR({01/30/1989})</a></span> returns <span class="Syntax">1989</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106824">MAX</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106826">Returns the larger of two numbers.</a></div> <div class="table_text"><span class="Syntax"><a name="106827">MAX(66,89)</a></span> returns <span class="Syntax">89</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106829">DAYOFWEEK</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106831">Returns the day of week (1-7) of a date expression.</a></div> <div class="table_text"><span class="Syntax"><a name="106832">DAYOFWEEK({05/01/1995})</a></span> returns <span class="Syntax">5</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106834">MIN</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106836">Returns the smaller of two numbers.</a></div> <div class="table_text"><span class="Syntax"><a name="106837">MIN(66,89) </a></span>returns <span class="Syntax">66</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106839">POW</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106841">Raises a number to a power.</a></div> <div class="table_text"><span class="Syntax"><a name="106842">POW(7,2)</a></span> returns <span class="Syntax">49</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106844">INT</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106846">Returns the integer part of a number.</a></div> <div class="table_text"><span class="Syntax"><a name="106847">INT(6.4321)</a></span> returns <span class="Syntax">6</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106849">ROUND</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106851">Rounds a number.</a></div> <div class="table_text"><span class="Syntax"><a name="106852">ROUND(123.456, 0) </a></span>returns <span class="Syntax">123</span>. </div> <div class="table_text"><span class="Syntax"><a name="106853">ROUND(123.456, 2)</a></span> returns<span class="Syntax"> 123.46</span>. </div> <div class="table_text"><span class="Syntax"><a name="106854">ROUND(123.456, â??2) </a></span>returns <span class="Syntax">100</span>. </div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106856">NUMVAL</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106858">Converts a character string to a number. If the character string is not </a>a valid number, a zero (0) is returned.</div> <div class="table_text"><span class="Syntax"><a name="106859">NUMVAL('123')</a></span> returns the number <span class="Syntax">123</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106861">VAL</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106863">Converts a character string to a number. If the character string is not </a>a valid number, a zero (0) is returned.</div> <div class="table_text"><span class="Syntax"><a name="106864">VAL('123')</a></span> returns the number <span class="Syntax">123</span>.</div> </td> </tr> </table> <table class="Format_E" cellspacing="0" summary=""> <caption> <div class="table_title_wide">Table 11-6. <a name="106872">Functions that Return Dates</a> </div> </caption> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="106876">Function</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="106878">Description</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106880">DATE</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106882">Returns todayâ??s date.</a></div> <div class="table_text"><a name="106883">If today is 12/25/1999, </a><span class="Syntax">DATE()</span> returns <span class="Syntax">{12/25/1999}</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106885">TODAY</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106887">Returns today's date.</a></div> <div class="table_text"><a name="106888">If today is 12/25/1999, </a><span class="Syntax">TODAY</span><span class="Syntax">()</span> returns <span class="Syntax">{12/25/1999}</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106890">DATEVAL</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106892">Converts a character string to a date.</a></div> <div class="table_text"><span class="Syntax"><a name="106893">DATEVAL('01/30/1989')</a></span> returns <span class="Syntax">{01/30/1989}</span>.</div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106895">CTOD</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="106897">Converts a character string to a date. An optional second </a>parameter specifies the format of the character string: 0 (the default) returns MM/DD/YY, 1 returns <br />DD/MM/YY, and 2 returns YY/MM/DD.</div> <div class="table_text"><span class="Syntax"><a name="106898">CTOD('01/30/1989')</a></span> returns <span class="Syntax">{01/30/1989}</span>.</div> <div class="table_text"><span class="Syntax"><a name="106899">CTOD('01/30/1989',1)</a></span> returns <span class="Syntax">{30/01/1989}</span>.</div> </td> </tr> </table> <div class="Body"><a name="106902">The following examples use some of the number and date functions.</a></div> <div class="Body"><a name="108711">Retrieve all employees that have been with the company at least 90 days:</a></div> <div class="syntax_first"><a name="108712">SELECT first_name, last_name FROM emp</a></div> <div class="syntax"><a name="108713"> </a> WHERE DATE() â?? hire_date >= 90</div> <div class="Body"><a name="108714">Retrieve all employees hired in January of this year or last year:</a></div> <div class="syntax_first"><a name="106907">SELECT first_name, last_name FROM emp </a></div> <div class="syntax"><a name="106908"> </a> WHERE MONTH(hire_date) = 1 </div> <div class="syntax"><a name="106909"> </a> AND (YEAR(hire_date) = YEAR(DATE())</div> <div class="syntax"><a name="106910"> </a> OR YEAR(hire_date) = YEAR(DATE()) â?? 1)</div> <script type="text/javascript" language="JavaScript1.2"> <!-- // Clear related topics // WWHClearRelatedTopics(); document.writeln(WWHRelatedTopicsInlineHTML()); // --> </script> </blockquote> <script type="text/javascript" language="JavaScript1.2"> <!-- document.write(WWHRelatedTopicsDivTag() + WWHPopupDivTag() + WWHALinksDivTag()); // --> </script> </body> </html>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de