<?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 xmlns="http://www.w3.org/1999/xhtml" lang="en-US" xml:lang="en-us"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <meta name="dc.language" scheme="rfc1766" content="en-us" /> <!-- All rights reserved. Licensed Materials Property of IBM --> <!-- US Government Users Restricted Rights --> <!-- Use, duplication or disclosure restricted by --> <!-- GSA ADP Schedule Contract with IBM Corp. --> <meta name="dc.date" scheme="iso8601" content="2005-09-19" /> <meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" /> <meta name="security" content="public" /> <meta name="Robots" content="index,follow"/> <meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' /> <meta name="keywords" content="CREATE VIEW statement, CREATE VIEW, SQL statements, creating, view, VIEW clause, RECURSIVE clause, recursive, in CREATE VIEW statement, view-name, column-name, system column name, FOR COLUMN clause, system-column-name, AS clause, used in CREATE VIEW statement, fullselect, WITH CHECK OPTION clause, CHECK OPTION clause, WITH CASCADED CHECK OPTION clause, CASCADED CHECK OPTION clause, WITH LOCAL CHECK OPTION clause, LOCAL CHECK OPTION clause, deletable, updatable, insertable, read-only" /> <title>CREATE VIEW</title> <link rel="stylesheet" type="text/css" href="ibmidwb.css" /> <link rel="stylesheet" type="text/css" href="ic.css" /> </head> <body> <a id="Top_Of_Page" name="Top_Of_Page"></a><!-- Java sync-link --> <script language = "Javascript" src = "../rzahg/synch.js" type="text/javascript"></script> <a name="hcview"></a> <h2 id="hcview"><a href="rbafzmst02.htm#ToC_956">CREATE VIEW</a></h2><a id="idx2172" name="idx2172"></a><a id="idx2173" name="idx2173"></a><a id="idx2174" name="idx2174"></a><a id="idx2175" name="idx2175"></a> <a name="cview"></a> <p id="cview">The CREATE VIEW statement creates a view on one or more tables or views at the current server.</p> <a name="wq1365"></a> <h3 id="wq1365"><a href="rbafzmst02.htm#ToC_957">Invocation</a></h3> <p>This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.</p> <a name="wq1366"></a> <h3 id="wq1366"><a href="rbafzmst02.htm#ToC_958">Authorization</a></h3> <p>The privileges held by the authorization ID of the statement must include at least one of the following:</p> <ul> <li>The privilege to create in the schema. For more information, see <a href="rbafzmstauthown.htm#createin">Privileges necessary to create in a schema</a>.</li> <li>Administrative authority</li></ul> <p>The privileges held by the authorization ID of the statement must include at least one of the following: </p> <ul> <li>The following system authorities: <ul> <li>*USE to the Create Logical File (CRTLF) CL command</li> <li>*CHANGE to the data dictionary if the library into which the view is created is an SQL schema with a data dictionary</li></ul></li> <li>Administrative authority</li></ul> <p>The privileges held by the authorization ID of the statement must also include at least one of the following: </p> <ul> <li>For each table and view referenced directly through the fullselect, or indirectly through views referenced in the fullselect: <ul> <li>The SELECT privilege on the table or view, and</li> <li>The system authority *EXECUTE on the library containing the table or view</li></ul></li> <li>Administrative authority</li></ul> <p>For information on the system authorities corresponding to SQL privileges, see <a href="rbafzmstgnt.htm#eqtablet">Corresponding System Authorities When Checking Privileges to a Table or View</a> and <a href="rbafzmstgntudtp.htm#eqtabled">Corresponding System Authorities When Checking Privileges to a Distinct Type</a>.</p> <a name="wq1367"></a> <h3 id="wq1367"><a href="rbafzmst02.htm#ToC_959">Syntax</a></h3> <a href="rbafzmsthcview.htm#synscrtview"><img src="c.gif" alt="Click to skip syntax diagram" /></a> <pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn313.htm" border="0" /></span><a href="#skipsyn-312"><img src="c.gif" alt="Skip visual syntax diagram" border="0" /></a>>>-CREATE VIEW--+-----------+--VIEW--<span class="italic">view-name</span>------------------> '-RECURSIVE-' >--+----------------------------------------------------------------+--> | .-,----------------------------------------------------. | | V | | '-(----<span class="italic">column-name</span>--+-------------------------------------+-+--)-' | .-COLUMN-. | '-FOR--+--------+--<span class="italic">system-column-name</span>-' >--AS--+-----------------------------------+--<span class="italic">fullselect</span>--------> | .-,-----------------------. | | V | | '-WITH----<span class="italic">common-table-expression</span>-+-' >--+----------------------------------+------------------------>< | .-CASCADED-. | '-WITH--+----------+--CHECK OPTION-' '-LOCAL----' </pre> <a name="skipsyn-312" id="skipsyn-312"></a> <a name="synscrtview"></a> <h3 id="synscrtview"><a href="rbafzmst02.htm#ToC_960">Description</a></h3> <dl class="parml"> <dt class="bold">RECURSIVE</dt><a id="idx2176" name="idx2176"></a><a id="idx2177" name="idx2177"></a><a id="idx2178" name="idx2178"></a> <dd>Indicates that the view is potentially recursive. <p>If a <var class="pv">fullselect</var> of the view contains a reference to the view itself in a FROM clause, the view is a <var class="pv">recursive view</var>. Views using recursion are useful in supporting applications such as bill of materials (BOM), reservation systems, and network planning.</p> <p>The restrictions that apply to a recursive <var class="pv">view</var> are similar to those for a recursive common table expression:</p> <ul> <li>A list of <var class="pv">column-names</var> must be specified following the <var class="pv">view-name</var> unless the result columns of the fullselect are already named.</li> <li>The UNION ALL set operator must be specified.</li> <li>The first fullselect of the first union (the initialization fullselect) must not include a reference to the <var class="pv">view</var> itself in any FROM clause.</li> <li>Each fullselect that is part of the recursion cycle must not include any aggregate functions, GROUP BY clauses, or HAVING clauses.</li> <li>The FROM clauses of each fullselect can include at most one reference to the view that is part of a recursion cycle.</li> <li>The table being defined in the <var class="pv">common-table-expression</var> cannot be referenced in a subquery of a fullselect that defines the <var class="pv">common-table-expression</var>.</li> <li>LEFT OUTER JOIN is not allowed if the <var class="pv">common-table-expression</var> is the right operand. A RIGHT OUTER JOIN is not allowed if the <var class="pv">common-table-expression</var> is the left operand.</li></ul> <p>If a column name of the view is referred to in the iterative fullselect, the attributes of the result columns are determined using the rules for result columns. For more information see <a href="rbafzmstopcomb.htm#opcomb">Rules for result data types</a>.</p> <p>Recursive views are not allowed if the query specifies:</p> <ul> <li>lateral correlation,</li> <li>a sort sequence,</li> <li>an operation that requires CCSID conversion,</li> <li>a UTF-8 or UTF-16 argument in a CHARACTER_LENGTH, POSITION, or SUBSTRING scalar function,</li> <li>a distributed table,</li> <li>a table with a read trigger, or</li> <li>a logical file built over multiple physical file members.</li></ul> </dd> <dt class="bold"><var class="pv">view-name</var> </dt><a id="idx2179" name="idx2179"></a> <dd>Names the view. The name, including the implicit or explicit qualifier, must not be the same as an alias, file, index, table, or view that already exists at the current server. <p>If SQL names were specified, the view will be created in the schema specified by the implicit or explicit qualifier.</p> <p>If system names were specified, the view will be created in the schema that is specified by the qualifier. If not qualified, the view name will be created in the same schema as the first table specified on the first FROM clause (including FROM clauses in any common table expressions or nested table expression).</p> <p>If a view name is not a valid system name, DB2 UDB for iSeries SQL will generate a system name. For information on the rules for generating the name, see <a href="rbafzmsthctabl.htm#namrul">Rules for Table Name Generation</a>.</p> </dd> <dt class="bold"><var class="pv">(column-name, ... )</var> </dt><a id="idx2180" name="idx2180"></a> <dd>Names the columns in the view. If a list of column names is specified, it must consist of as many names as there are columns in the result table of the fullselect. Each <var class="pv">column-name</var> and <var class="pv">system-column-name</var> must be unique and unqualified. If a list of column names is not specified, the columns of the view inherit the names of the columns and system names of the columns of the result table of the fullselect. <a id="idx2181" name="idx2181"></a> <p>A list of column names (and system column names) must be specified if the result table of the subselect has duplicate column names, duplicate system column names, or an unnamed column. For more information about unnamed columns, see <a href="rbafzmstsubselect.htm#nrcmj">Names of result columns</a>.</p> </dd> <dt class="bold">FOR COLUMN <var class="pv">system-column-name</var> </dt><a id="idx2182" name="idx2182"></a><a id="idx2183" name="idx2183"></a> <dd>Provides an i5/OS name for the column. Do not use the same name for more than one column of the view or for a column-name of the view. <a id="idx2184" name="idx2184"></a> <p>If the system-column-name is not specified, and the column-name is not a valid system-column-name, a system column name is generated. For more information about how system column names are generated, see <a href="rbafzmsthctabl.htm#cnamrul">Rules for Column Name Generation</a>.</p> </dd> <dt class="bold">AS <var class="pv">fullselect</var> </dt><a id="idx2185" name="idx2185"></a><a id="idx2186" name="idx2186"></a> <dd>Defines the view. At any time, the view consists of the rows that would result if the fullselect were executed. <p><var class="pv">fullselect</var> must not reference variables.</p> <p>The maximum number of columns allowed in a view is 8000. The column name lengths and the length of the WHERE clause also reduce this number. The maximum number of base tables allowed in the view is 256.</p> <p>For an explanation of <var class="pv">fullselect</var>, see <a href="rbafzmstmark.htm#mark">fullselect</a>.</p> <p> <var class="pv">common-table-expression</var> defines a common table expression for use with the <var class="pv">fullselect</var> that follows. For more information see <a href="rbafzmstintsel.htm#comtexp">common-table-expression</a>.</p> </dd> </dl> <dl class="parml"> <dt class="bold">WITH CASCADED CHECK OPTION or WITH LOCAL CHECK OPTION</dt><a id="idx2187" name="idx2187"></a><a id="idx2188" name="idx2188"></a><a id="idx2189" name="idx2189"></a><a id="idx2190" name="idx2190"></a> <dd>Specifies that every row that is inserted or updated through the view must conform to the definition of the view. A row that does not conform to the definition of the view is a row that cannot be retrieved using that view. <p>CHECK OPTION must not be specified if:</p> <ul> <li>the view is read-only</li> <li>the definition of the view includes a subquery</li> <li>the definition of the view contains a non-deterministic function</li> <li>the definition of the view contains a special register</li> <li>the view references another view and that view has an INSTEAD OF trigger</li> <li>the view is recursive</li></ul><p class="indatacontent"> If CHECK OPTION is specified for an updatable view that does not allow inserts, then the check option applies to updates only.</p> <p>If CHECK OPTION is omitted, the definition of the view is not used in the checking of any insert or update operations that use the view. Some checking might still occur during insert or update operations if the view is directly or indirectly dependent on another view that includes a CHECK OPTION. Because the definition of the view is not used, rows that do not conform to the definition of the view might be inserted or updated through the view.</p> <dl class="parml"> <dt class="bold">CASCADED</dt> <dd>The WITH CASCADED CHECK OPTION on a view V is inherited by any updatable view that is directly or indirectly dependent on V. Thus, if an updatable view is defined on V, the check option on V also applies to that view, even if WITH CHECK OPTION is not specified on that view. For example, consider the following updatable views: <p></p> <pre class="xmp"><span class="bold">CREATE VIEW</span> V1 <span class="bold">AS SELECT</span> COL1 <span class="bold">FROM</span> T1 <span class="bold">WHERE</span> COL1 > 10 <span class="bold">CREATE VIEW</span> V2 <span class="bold">AS SELECT</span> COL1 <span class="bold">FROM</span> V1 <span class="bold">WITH CHECK OPTION</span> <span class="bold">CREATE VIEW</span> V3 <span class="bold">AS SELECT</span> COL1 <span class="bold">FROM</span> V2 <span class="bold">WHERE</span> COL1 < 100</pre> <a name="wq1368"></a> <table id="wq1368" width="100%" summary="" border="1" frame="hsides" rules="rows"> <thead valign="bottom"> <tr> <th id="wq1369" width="40%" align="left">SQL statement</th> <th id="wq1370" width="60%" align="left">Description of result</th> </tr> </thead> <tbody valign="top"> <tr> <td headers="wq1369">INSERT INTO V1 VALUES(5)</td> <td headers="wq1370">Succeeds because V1 does not have a CHECK OPTION clause and it is not dependent on any other view that has a CHECK OPTION clause.</td> </tr> <tr> <td headers="wq1369">INSERT INTO V2 VALUES(5)</td> <td headers="wq1370">Results in an error because the inserted row does not conform to the search condition of V1 which is implicitly part of the definition of V2.</td> </tr> <tr> <td headers="wq1369">INSERT INTO V3 VALUES(5)</td> <td headers="wq1370">Results in an error because V3 is dependent on V2 which has a CHECK OPTION clause and the inserted row does not conform to the definition of V2.</td> </tr> <tr> <td headers="wq1369">INSERT INTO V3 VALUES(200)</td> <td headers="wq1370">Succeeds even though it does not conform to the definition of V3 (V3 does not have the view CHECK OPTION clause specified); it does conform to the definition of V2 (which does have the view CHECK OPTION clause specified).</td> </tr> </tbody> </table> </dd> <dt class="bold">LOCAL</dt><a id="idx2191" name="idx2191"></a><a id="idx2192" name="idx2192"></a> <dd>WITH LOCAL CHECK OPTION is identical to WITH CASCADED CHECK OPTION except that it is still possible to update a row so that it no longer conforms to the definition of the view when the view is defined with the WITH LOCAL CHECK OPTION. This can only happen when the view is directly or indirectly dependent on a view that was defined without either WITH CASCADED CHECK OPTION or WITH LOCAL CHECK OPTION clauses. <p>WITH LOCAL CHECK OPTION specifies that the search conditions of the following underlying views are checked when a row is inserted or updated: </p> <ul> <li>views that specify WITH LOCAL CHECK OPTION</li> <li>views that specify WITH CASCADED CHECK OPTION</li> <li>all underlying views of a view that specifies WITH CASCADED CHECK OPTION</li></ul><p class="indatacontent"> In contrast, WITH CASCADED CHECK OPTION specifies that the search conditions of all underlying views are checked when a row is inserted or updated.</p> </dd> </dl> </dd> </dl> <p>The difference between CASCADED and LOCAL is best shown by example. Consider the following updatable views where x and y represent either LOCAL or CASCADED: </p> <pre class="xmp"> V1 defined on table T0 V2 defined on V1 WITH x CHECK OPTION V3 defined on V2 V4 defined on V3 WITH y CHECK OPTION V5 defined on V4</pre> <p>The following table describes which views search conditions are checked during an INSERT or UPDATE operation:</p> <a name="thd1"></a> <table id="thd1" width="100%" summary="" border="1" frame="border" rules="all"> <caption>Table 53. Views whose search conditions are checked during INSERT and UPDATE</caption> <thead valign="bottom"> <tr> <th id="wq1371" width="20%" align="left" valign="bottom">View used in INSERT or UPDATE</th> <th id="wq1372" width="20%" align="left" valign="bottom">x = LOCAL <p>y = LOCAL</p></th> <th id="wq1373" width="20%" align="left" valign="bottom">x = CASCADED <p>y = CASCADED</p></th> <th id="wq1374" width="20%" align="left" valign="bottom">x = LOCAL <p>y = CASCADED</p></th> <th id="wq1375" width="20%" align="left" valign="bottom">x = CASCADED <p>y = LOCAL</p></th> </tr> </thead> <tbody valign="top"> <tr> <td align="left" valign="top" headers="wq1371">V1</td> <td align="left" valign="top" headers="wq1372">none</td> <td align="left" valign="top" headers="wq1373">none</td> <td align="left" valign="top" headers="wq1374">none</td> <td align="left" valign="top" headers="wq1375">none</td> </tr> <tr> <td align="left" valign="top" headers="wq1371">V2</td> <td align="left" valign="top" headers="wq1372">V2</td> <td align="left" valign="top" headers="wq1373">V2 V1</td> <td align="left" valign="top" headers="wq1374">V2</td> <td align="left" valign="top" headers="wq1375">V2 V1</td> </tr> <tr> <td align="left" valign="top" headers="wq1371">V3</td> <td align="left" valign="top" headers="wq1372">V2</td> <td align="left" valign="top" headers="wq1373">V2 V1</td> <td align="left" valign="top" headers="wq1374">V2</td> <td align="left" valign="top" headers="wq1375">V2 V1</td> </tr> <tr> <td align="left" valign="top" headers="wq1371">V4</td> <td align="left" valign="top" headers="wq1372">V4 V2</td> <td align="left" valign="top" headers="wq1373">V4 V3 V2 V1</td> <td align="left" valign="top" headers="wq1374">V4 V3 V2 V1</td> <td align="left" valign="top" headers="wq1375">V4 V2 V1</td> </tr> <tr> <td align="left" valign="top" headers="wq1371">V5</td> <td align="left" valign="top" headers="wq1372">V4 V2</td> <td align="left" valign="top" headers="wq1373">V4 V3 V2 V1</td> <td align="left" valign="top" headers="wq1374">V4 V3 V2 V1</td> <td align="left" valign="top" headers="wq1375">V4 V2 V1</td> </tr> </tbody> </table> <a name="cvrdon"></a> <h3 id="cvrdon"><a href="rbafzmst02.htm#ToC_961">Notes</a></h3> <p><span class="bold">View ownership:</span> If SQL names were specified:</p> <ul> <li>If a user profile with the same name as the schema into which the view is created exists, the <span class="italic">owner</span> of the view is that user profile.</li> <li>Otherwise, the <span class="italic">owner</span> of the view is the user profile or group user profile of the job executing the statement.</li></ul> <p>If system names were specified, the <span class="italic">owner</span> of the view is the user profile or group user profile of the job executing the statement.</p> <p><span class="bold">View authority:</span> If SQL names are used, views are created with the system authority of *EXCLUDE on *PUBLIC. If system names are used, views are created with the authority to *PUBLIC as determined by the create authority (CRTAUT) parameter of the schema.</p> <p>If the owner of the view is a member of a group profile (GRPPRF keyword) and group authority is specified (GRPAUT keyword), that group profile will also have authority to the view.</p> <p>The owner always acquires the SELECT privilege on the view and the authorization to drop the view. The SELECT privilege can be granted to others only if the owner also has the authority to grant the SELECT privilege on every table or view identified in the fullselect.</p> <p>The owner can also acquire the INSERT, UPDATE, and DELETE privileges on the view. If the view is not read-only, then the same privileges will be acquired on the new view as the owner has on the table or view identified in the first FROM clause of the fullselect. These privileges can be granted only if the privileges from which they are derived can also be granted.</p> <p><span class="bold">Deletable views:</span> A view is <span class="italic">deletable</span> if an INSTEAD OF trigger for the delete operation has been defined for the view, or if all of the following are true:<a id="idx2193" name="idx2193"></a></p> <ul> <li>the outer fullselect identifies only one base table or deletable view.</li> <li>the outer fullselect does not include a GROUP BY clause or HAVING clause.</li> <li>the outer fullselect does not include aggregate functions in the select list.</li> <li>the outer fullselect does not include a UNION, UNION ALL, EXCEPT, or INTERSECT operator.</li> <li>the outer fullselect does not include the DISTINCT clause.</li></ul> <p><span class="bold">Updatable views:</span> A view is <span class="italic">updatable</span> if an INSTEAD OF trigger for the update operation has been defined for the view, or if all of the following are true: <a id="idx2194" name="idx2194"></a></p> <ul> <li>the view is deletable (independent of an INSTEAD OF trigger for delete),</li> <li>at least one column of the view is updatable.</li></ul> <p>A column of a view is <var class="pv">updatable</var> if an INSTEAD OF trigger for the update operation has been defined for the view, or if the corresponding result column of the <var class="pv">subselect</var> is derived solely from a column of a table or an updatable column of another view (that is, it is not derived from an expression that contains an operator, scalar function, constant, or a column that itself is derived from such expressions).</p> <p><span class="bold">Insertable views:</span> A view is <span class="italic">insertable</span> if an INSTEAD OF trigger for the insert operation has been defined for the view, or if at least one column of the view is updatable (independent of an INSTEAD OF trigger for update).<a id="idx2195" name="idx2195"></a></p> <p><span class="bold">Read-only views:</span> A view is <span class="italic">read-only</span> if it is not deletable.</p> <p>A read-only view cannot be the object of an INSERT, UPDATE, or DELETE statement.<a id="idx2196" name="idx2196"></a></p> <p><span class="bold">Unqualified table names:</span> If the CREATE VIEW statement refers to an unqualified table name, the following rules are applied to determine which table is actually being referenced:</p> <ul> <li>If the unqualified name corresponds to one or more common table expression <var class="pv">table-identifiers</var> that are specified in the <var class="pv">select-statement</var>, the name identifies the common table expression that is in the innermost scope.</li> <li>Otherwise, the name identifies a persistent table, a temporary table, or a view.</li></ul> <p><span class="bold">Sort sequence:</span> The view is created with the sort sequence in effect at the time the CREATE VIEW statement is executed. The sort sequence of the view applies to all comparisons involving SBCS data and mixed data in the view fullselect. When the view is included in a query, an intermediate result table is generated from the view fullselect. The sort sequence in effect when the query is executed applies to any selection specified in the query.</p> <p><span class="bold">View attributes:</span> Views are created as nonkeyed logical files. When a view is created, the file wait time and record wait time attributes are set to the default that is specified on the WAITFILE and WAITRCD keywords of the Create Logical File (CRTLF) command.</p> <p>The date and time format used for date and time result columns is ISO.</p> <p>A view created over a distributed table is created on all of the systems across which the table is distributed. If a view is created over more than one distributed table, and those tables are not distributed using the same nodegroup, then the view is created only on the system that performs the CREATE VIEW statement. For more information about distributed tables, see the <a href="../dbmult/rzaf3kickoff.htm">DB2® Multisystem</a> book.</p> <p><span class="bold">Identity columns:</span> A column of a view is considered an identity column if the element of the corresponding column in the fullselect of the view definition is the name of an identity column of a table, or the name of a column of a view which directly or indirectly maps to the name of an identity column of a base table. In all other cases, the columns of a view will not get the identity property. For example:</p> <ul> <li>the select-list of the view definition includes multiple instances of the name of an identity column (that is, selecting the same column more than once)</li> <li>the view definition involves a join</li> <li>a column in the view definition includes an expression that refers to an identity column</li> <li>the view definition includes a UNION or INTERSECT</li></ul> <a name="wq1376"></a> <h3 id="wq1376"><a href="rbafzmst02.htm#ToC_962">Examples</a></h3> <p><span class="italic">Example 1:</span> Create a view named MA_PROJ over the PROJECT table that contains only those rows with a project number (PROJNO) starting with the letters 'MA'. </p> <pre class="xmp"><span class="bold">CREATE VIEW</span> MA_PROJ <span class="bold">AS SELECT * FROM</span> PROJECT <span class="bold">WHERE SUBSTR(</span>PROJNO, 1, 2<span class="bold">)</span> = 'MA'</pre> <p><span class="italic">Example 2:</span> Create a view as in example 1, but select only the columns for project number (PROJNO), project name (PROJNAME) and employee in charge of the project (RESPEMP). </p> <pre class="xmp"><span class="bold">CREATE VIEW</span> MA_PROJ2 <span class="bold">AS SELECT</span> PROJNO, PROJNAME, RESPEMP <span class="bold">FROM</span> PROJECT <span class="bold">WHERE SUBSTR(</span>PROJNO, 1, 2<span class="bold">)</span> = 'MA'</pre> <p><span class="italic">Example 3:</span> Create a view as in example 2, but, in the view, call the column for the employee in charge of the project IN_CHARGE. </p> <pre class="xmp"><span class="bold">CREATE VIEW</span> MA_PROJ <span class="bold">(</span>PROJNO, PROJNAME, IN_CHARGE<span class="bold">)</span> <span class="bold">AS SELECT</span> PROJNO, PROJNAME, RESPEMP <span class="bold">FROM</span> PROJECT <span class="bold">WHERE SUBSTR(</span>PROJNO, 1, 2<span class="bold">)</span> = 'MA'</pre> <a name="wq1377"></a> <div class="notetitle" id="wq1377">Note:</div> <div class="notebody">Even though you are changing only one of the column names, the names of all three columns in the view must be listed in the parentheses that follow MA_PROJ.</div> <p><span class="italic">Example 4:</span> Create a view named PRJ_LEADER that contains the first four columns (PROJNO, PROJNAME, DEPTNO, RESPEMP) from the PROJECT table together with the last name (LASTNAME) of the person who is responsible for the project (RESPEMP). Obtain the name from the EMPLOYEE table by matching EMPNO in EMPLOYEE to RESEMP in PROJECT. </p> <pre class="xmp"><span class="bold">CREATE VIEW</span> PRJ_LEADER <span class="bold">AS SELECT</span> PROJNO, PROJNAME, DEPTNO, RESPEMP, LASTNAME <span class="bold">FROM</span> PROJECT, EMPLOYEE <span class="bold">WHERE</span> RESPEMP = EMPNO</pre> <p><span class="italic">Example 5:</span> Create a view as in example 4, but in addition to the columns PROJNO, PROJNAME, DEPTNO, RESEMP and LASTNAME, show the total pay (SALARY + BONUS +COMM) of the employee who is responsible. Also select only those projects with mean staffing (PRSTAFF) greater than one. </p> <pre class="xmp"><span class="bold">CREATE VIEW</span> PRJ_LEADER <span class="bold">(</span>PROJNO, PROJNAME, DEPTNO, RESPEMP, LASTNAME, TOTAL_PAY<span class="bold">)</span> <span class="bold">AS SELECT</span> PROJNO, PROJNAME, DEPTNO, RESPEMP, LASTNAME, SALARY+BONUS+COMM <span class="bold">FROM</span> PROJECT, EMPLOYEE <span class="bold">WHERE</span> RESPEMP = EMPNO <span class="bold">AND</span> PRSTAFF > 1</pre> <p><span class="italic">Example 6:</span> Create a recursive view that returns a similar result as a common table expression, see <a href="rbafzmstintsel.htm#singlelvlexp">Single level explosion</a>. </p> <pre class="xmp"> <span class="bold">CREATE RECURSIVE VIEW RPL (</span>PART, SUBPART, QUANTITY<span class="bold">) AS SELECT</span> ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY <span class="bold">FROM</span> PARTLIST ROOT <span class="bold">WHERE</span> ROOT.PART = '01' <span class="bold">UNION ALL SELECT</span> CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY <span class="bold">FROM</span> RPL PARENT, PARTLIST CHILD <span class="bold">WHERE</span> PARENT.SUBPART = CHILD.PART <span class="bold">SELECT DISTINCT</span> * <span class="bold">FROM</span>RPL <span class="bold">ORDER BY</span> PART, SUBPART, QUANTITY </pre><a id="idx2197" name="idx2197"></a><a id="idx2198" name="idx2198"></a> <hr /><br /> [ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmsthctrigger.htm">Previous Page</a> | <a href="rbafzdeallocatedescr.htm">Next Page</a> | <a href="rbafzmst02.htm#wq1">Contents</a> | <a href="rbafzmstindex.htm#index">Index</a> ] <a id="Bot_Of_Page" name="Bot_Of_Page"></a> </body> </html>