Edit C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\odbc\7.0.1\help\reference\sqlsupport.12.28.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>Subqueries</title> <link rel="StyleSheet" href="css/sqlsupport.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="sqlsupport.12.01.html#94298">10 SQL Statements and Extensions for the Salesforce Driver</a> : Subqueries</div> <hr align="left" /> <blockquote> <div class="head_1"><a name="94298">Subqueries</a></div> <div class="Body"><a name="94299">A query is an operation that retrieves data from one or more tables or views. In this </a>reference, a top-level query is called a Select statement, and a query nested within a Select statement is called a subquery. </div> <div class="Body"><a name="94300">A subquery is a query expression that appears in the body of another expression such as a </a>Select, an Update, or a Delete statement. In the following example, the second Select statement is a subquery:</div> <div class="syntax_first"><a name="94301">SELECT * FROM emp WHERE deptno IN</a></div> <div class="syntax"><a name="94302"> (SELECT deptno FROM dept)</a></div> <div class="head_2"><a name="94304">IN Predicate</a></div> <div class="Body"><a name="158632">The In predicate specifies a set of values against which to compare a result set. If the </a>values are being compared against a subquery, only a single column result set is returned.</div> <div class="gutter_text_right"><a name="94305">Grammar</a></div> <div class="syntax_first"><span class="EquationVariables"><a name="94306">value</a></span> [NOT] IN (<span class="EquationVariables">value1</span>, <span class="EquationVariables">value2</span>,...)</div> <div class="syntax"><a name="94307">OR</a></div> <div class="syntax"><span class="EquationVariables"><a name="94308">value</a></span> [NOT] IN (<span class="EquationVariables">subquery</span>)</div> <div class="gutter_text_right"><a name="94309">Example</a></div> <div class="syntax_first"><a name="94310">SELECT * FROM emp WHERE deptno IN </a></div> <div class="syntax"><a name="94311"> (SELECT deptno FROM dept WHERE dname <> 'Sales')</a></div> <div class="head_2"><a name="94313">EXISTS Predicate</a></div> <div class="Body"><a name="94314">The Exists predicate is true only if the cardinality of the subquery is greater than 0; </a>otherwise, it is false.</div> <div class="gutter_text_right"><a name="94315">Grammar</a></div> <div class="syntax_first"><a name="94316">EXISTS (</a><span class="EquationVariables">subquery</span>)</div> <div class="gutter_text_right"><a name="94317">Example</a></div> <div class="syntax_first"><a name="94318">SELECT empno, ename, deptno FROM emp e WHERE EXISTS </a></div> <div class="syntax"><a name="94319"> (SELECT deptno FROM dept WHERE e.deptno = dept.deptno)</a></div> <div class="head_2"><a name="94321">UNIQUE Predicate</a></div> <div class="Body"><a name="94322">The Unique predicate is used to determine whether duplicate rows exist in a virtual table </a>(one returned from a subquery).</div> <div class="gutter_text_right"><a name="94323">Grammar</a></div> <div class="syntax_first"><a name="94324">UNIQUE (</a><span class="EquationVariables">subquery</span>)</div> <div class="gutter_text_right"><a name="94325">Example</a></div> <div class="syntax_first"><a name="94326">SELECT * FROM dept d WHERE UNIQUE </a></div> <div class="syntax"><a name="97096"> (SELECT deptno FROM emp e WHERE e.deptno = d.deptno)</a></div> <div class="head_2"><a name="94328">Correlated Subqueries</a></div> <div class="Body"><a name="94329">A correlated subquery is a subquery that references a column from a table referred to in the </a>parent statement. A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a Select, Update, or Delete statement.</div> <div class="Body"><a name="94330">A correlated subquery answers a multiple-part question in which the answer depends on the </a>value in each row processed by the parent statement. For example, you can use a correlated subquery to determine which employees earn more than the average salaries for their departments. In this case, the correlated subquery specifically computes the average salary for each department.</div> <div class="gutter_text_right"><a name="94331">Grammar</a></div> <div class="syntax_first"><a name="94332">SELECT </a><span class="EquationVariables">select_list</span> </div> <div class="syntax"><a name="94333"> FROM </a><span class="EquationVariables">table1 t_alias1</span> </div> <div class="syntax"><a name="94334"> WHERE </a><span class="EquationVariables">expr rel_operator</span> </div> <div class="syntax"><a name="94335"> (SELECT </a><span class="EquationVariables">column_list</span> </div> <div class="syntax"><a name="94336"> FROM </a><span class="EquationVariables">table2</span> <span class="EquationVariables">t_alias2</span> </div> <div class="syntax"><a name="94337"> WHERE </a><span class="EquationVariables">t_alias1.column</span> </div> <div class="syntax"><a name="94338"> </a><span class="EquationVariables">rel_operator</span> <span class="EquationVariables">t_alias2.column</span>) </div> <div class="syntax_first"><a name="94339">UPDATE </a><span class="EquationVariables">table1 t_alias1</span> </div> <div class="syntax"><a name="94340"> SET </a><span class="EquationVariables">column</span> = </div> <div class="syntax"><a name="94341"> (SELECT </a><span class="EquationVariables">expr</span> </div> <div class="syntax"><a name="94342"> FROM </a><span class="EquationVariables">table2 t_alias2</span> </div> <div class="syntax"><a name="94343"> WHERE </a><span class="EquationVariables">t_alias1.column</span> = <span class="EquationVariables">t_alias2.column</span>) </div> <div class="syntax_first"><a name="94344">DELETE FROM </a><span class="EquationVariables">table1 t_alias1</span> </div> <div class="syntax"><a name="94345"> WHERE </a><span class="EquationVariables">column rel_operator</span> </div> <div class="syntax"><a name="94346"> (SELECT </a><span class="EquationVariables">expr</span> </div> <div class="syntax"><a name="94347"> FROM </a><span class="EquationVariables">table2 t_alias2</span> </div> <div class="syntax"><a name="94348"> WHERE </a><span class="EquationVariables">t_alias1.column</span> = <span class="EquationVariables">t_alias2.column</span>) </div> <div class="Body"><a name="94349">NOTE: Correlated column names in correlated subqueries must be explicitly qualified with </a>the table name of the parent.</div> <div class="gutter_text_right"><a name="94351">Examples</a></div> <div class="hanging_label_head"><a name="94352">Example A</a></div> <div class="Body"><a name="94353">The following statement returns data about employees whose salaries exceed their </a>department average. This statement assigns an alias to <span class="Syntax">emp</span>, the table containing the salary information, and then uses the alias in a correlated subquery: </div> <div class="syntax_first"><a name="94354">SELECT deptno, ename, sal FROM emp x WHERE sal > </a></div> <div class="syntax"><a name="97101"> (SELECT AVG(sal) FROM emp WHERE x.deptno = deptno) </a></div> <div class="syntax"><a name="94359"> ORDER BY deptno </a></div> <div class="hanging_label_head"><a name="94360">Example B</a></div> <div class="Body"><a name="153020">This is an example of a correlated subquery that returns row values:</a></div> <div class="syntax_first"><a name="94361">SELECT * FROM dept "outer" WHERE 'manager' IN</a></div> <div class="syntax"><a name="94364"> (SELECT managername FROM emp </a></div> <div class="syntax"><a name="97116"> WHERE "outer".deptno = emp.deptno)</a></div> <div class="hanging_label_head"><a name="94367">Example C</a></div> <div class="Body"><a name="153024">This is an example of finding the department number (</a><span class="Syntax">deptno</span>) with multiple employees:</div> <div class="syntax_first"><a name="94368">SELECT * FROM dept main WHERE 1 <</a></div> <div class="syntax"><a name="94371"> (SELECT COUNT(*) FROM emp WHERE deptno = main.deptno)</a></div> <div class="hanging_label_head"><a name="94374">Example D</a></div> <div class="Body"><a name="153028">This is an example of correlating a table with itself:</a></div> <div class="syntax_first"><a name="94375">SELECT deptno, ename, sal FROM emp x WHERE sal ></a></div> <div class="syntax"><a name="94378"> (SELECT AVG(sal) FROM emp WHERE x.deptno = deptno)</a></div> <div class="Body"><a name="100166"> </a></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