155 lines
9.5 KiB
HTML
155 lines
9.5 KiB
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 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="VALUES INTO statement, VALUES INTO,
|
||
|
|
SQL statements, in VALUES INTO statement, expression, NULL, row-fullselect,
|
||
|
|
INTO clause, variable, *CNULRQD precompiler option,
|
||
|
|
*NOCNULRQD precompiler option" />
|
||
|
|
<title>VALUES INTO</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="valinto"></a>
|
||
|
|
<h2 id="valinto"><a href="rbafzmst02.htm#ToC_1391">VALUES INTO</a></h2><a id="idx3300" name="idx3300"></a><a id="idx3301" name="idx3301"></a>
|
||
|
|
<p>The VALUES INTO statement produces a result table consisting
|
||
|
|
of at most one row and assigns the values in that row to variables.</p>
|
||
|
|
<a name="wq1835"></a>
|
||
|
|
<h3 id="wq1835"><a href="rbafzmst02.htm#ToC_1392">Invocation</a></h3>
|
||
|
|
<p>This statement can be embedded in an application program. It
|
||
|
|
is an executable statement that can be dynamically prepared, but cannot be
|
||
|
|
issued interactively. It must not be specified in Java™.</p>
|
||
|
|
<a name="wq1836"></a>
|
||
|
|
<h3 id="wq1836"><a href="rbafzmst02.htm#ToC_1393">Authorization</a></h3>
|
||
|
|
<p>If a <span class="italic">row-fullselect</span> is specified, see <a href="rbafzmstsubsel.htm#subsel">Queries</a> for an explanation of the authorization required for each
|
||
|
|
subselect.</p>
|
||
|
|
<a name="wq1837"></a>
|
||
|
|
<h3 id="wq1837"><a href="rbafzmst02.htm#ToC_1394">Syntax</a></h3>
|
||
|
|
<a href="rbafzmstvalinto.htm#synsvinto"><img src="c.gif" alt="Click to skip syntax diagram" /></a>
|
||
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn402.htm"
|
||
|
|
border="0" /></span><a href="#skipsyn-401"><img src="c.gif" alt="Skip visual syntax diagram"
|
||
|
|
border="0" /></a> .-,--------.
|
||
|
|
V |
|
||
|
|
>>-VALUES--+-+-<span class="italic">expression</span>-+---------------+--INTO----<span><span class="italic">variable</span></span>-+-><
|
||
|
|
| '-NULL-------' |
|
||
|
|
| .-,--------------. |
|
||
|
|
| V | |
|
||
|
|
'-(--+---+-<span class="italic">expression</span>-+-+-+--)-'
|
||
|
|
| '-NULL-------' |
|
||
|
|
'-<span><span class="italic">row-fullselect</span></span>-----'
|
||
|
|
|
||
|
|
</pre>
|
||
|
|
<a name="skipsyn-401" id="skipsyn-401"></a>
|
||
|
|
<a name="synsvinto"></a>
|
||
|
|
<h3 id="synsvinto"><a href="rbafzmst02.htm#ToC_1395">Description</a></h3>
|
||
|
|
<dl class="parml">
|
||
|
|
<dt class="bold">VALUES </dt>
|
||
|
|
<dd>Introduces a single row consisting of one of more columns.
|
||
|
|
<dl class="parml">
|
||
|
|
<dt class="bold"><var class="pv">expression</var> </dt><a id="idx3302" name="idx3302"></a>
|
||
|
|
<dd>Specifies the new value of the variable. The <var class="pv">expression</var> is
|
||
|
|
any expression of the type described in <a href="rbafzmstch2expr.htm#ch2expr">Expressions</a>.
|
||
|
|
It must not include a column name. Host structures are not supported.
|
||
|
|
</dd>
|
||
|
|
<dt class="bold">NULL</dt><a id="idx3303" name="idx3303"></a>
|
||
|
|
<dd>Specifies that the new value for the variable is the null value.
|
||
|
|
</dd>
|
||
|
|
<dt class="bold"><var class="pv">row-fullselect</var></dt><a id="idx3304" name="idx3304"></a>
|
||
|
|
<dd>A fullselect that returns a single result row. The result column values
|
||
|
|
are assigned to each corresponding <var class="pv">variable</var>. If the result
|
||
|
|
of the fullselect is no rows, then null values are assigned. An error is returned
|
||
|
|
if there is more than one row in the result.
|
||
|
|
</dd>
|
||
|
|
</dl>
|
||
|
|
</dd>
|
||
|
|
<dt class="bold">INTO <var class="pv">variable</var>,... </dt><a id="idx3305" name="idx3305"></a><a id="idx3306" name="idx3306"></a>
|
||
|
|
<dd>Identifies one or more host structures or variables that must be declared
|
||
|
|
in the program in accordance with the rules for declaring host structures
|
||
|
|
and variables. In the operational form of INTO, a reference to a host structure
|
||
|
|
is replaced by a reference to each of its variables. The first value specified
|
||
|
|
is assigned to the first variable, the second value to the second variable,
|
||
|
|
and so on.
|
||
|
|
</dd>
|
||
|
|
</dl>
|
||
|
|
<a name="wq1838"></a>
|
||
|
|
<h3 id="wq1838"><a href="rbafzmst02.htm#ToC_1396">Notes</a></h3>
|
||
|
|
<p><span class="bold">Variable assignment: </span> Each assignment
|
||
|
|
to a variable is performed according to the retrieval assignment rules described
|
||
|
|
in <a href="rbafzmstch2bas.htm#ch2bas">Assignments and comparisons</a>. If the number of variables is less than the number
|
||
|
|
of values in the row, an SQL warning (SQLSTATE 01503) is returned (and the
|
||
|
|
SQLWARN3 field of the SQLCA is set to 'W'). Note that there is no
|
||
|
|
warning if there are more variables than the number of result columns. If
|
||
|
|
a value is null, an indicator variable must be provided for that value.</p>
|
||
|
|
<p>If the specified variable is character and is not large enough to contain
|
||
|
|
the result, a warning (SQLSTATE 01004) is returned (and 'W' is assigned
|
||
|
|
to SQLWARN1 in the SQLCA). The actual length of the result may be returned
|
||
|
|
in the indicator variable associated with the variable, if an indicator variable
|
||
|
|
is provided. For further information, see <a href="rbafzmstch2refvar.htm#ch2refvar">References to variables</a>.</p>
|
||
|
|
<p>If an assignment error occurs, the value is not assigned to the variable,
|
||
|
|
and no more values are assigned to variables. Any values that have already
|
||
|
|
been assigned to variables remain assigned.</p>
|
||
|
|
<p>If the specified variable is a C NUL-terminated host variable and is not
|
||
|
|
large enough to contain the result and the NUL-terminator:</p>
|
||
|
|
<ul>
|
||
|
|
<li>If the *CNULRQD option is specified on the CRTSQLCI or CRTSQLCPPI command
|
||
|
|
(or CNULRQD(*YES) on the SET OPTION statement), the following occurs:<a id="idx3307" name="idx3307"></a>
|
||
|
|
<ul>
|
||
|
|
<li>The result is truncated.</li>
|
||
|
|
<li>The last character is the NUL-terminator.</li>
|
||
|
|
<li>The value 'W' is assigned to SQLWARN1 in the SQLCA.</li></ul></li>
|
||
|
|
<li>If the *NOCNULRQD option on the CRTSQLCI or CRTSQLCPPI command (or CNULRQD(*NO)
|
||
|
|
on the SET OPTION statement) is specified, the following occurs:<a id="idx3308" name="idx3308"></a>
|
||
|
|
<ul>
|
||
|
|
<li>The NUL-terminator is not returned.</li>
|
||
|
|
<li>The value 'N' is assigned to SQLWARN1 in the SQLCA.</li></ul></li></ul>
|
||
|
|
<p><span class="bold">Result column evaluation considerations:</span> If an error
|
||
|
|
occurs while evaluating a result column in the expression list of a VALUES
|
||
|
|
INTO statement as the result of an arithmetic expression (such as division
|
||
|
|
by zero, or overflow) or a numeric or character conversion error, the result
|
||
|
|
is the null value. As in any other case of a null value, an indicator variable
|
||
|
|
must be provided. The value of the variable is undefined. In this case, however,
|
||
|
|
the indicator variable is set to the value of -2. Processing of the statement
|
||
|
|
continues and a warning is returned. If an indicator variable is not provided,
|
||
|
|
an error is returned and no more values are assigned to variables. It is
|
||
|
|
possible that some values have already been assigned to variables and will
|
||
|
|
remain assigned when the error is returned.</p>
|
||
|
|
<p>When a datetime value is returned, the length of the variable must be large
|
||
|
|
enough to store the complete value. Otherwise, depending on how much of the
|
||
|
|
value would have to be truncated, a warning or error is returned. See <a href="rbafzmstch2bas.htm#dta">Datetime assignments</a> for details.</p>
|
||
|
|
<a name="wq1839"></a>
|
||
|
|
<h3 id="wq1839"><a href="rbafzmst02.htm#ToC_1397">Examples</a></h3>
|
||
|
|
<p><span class="italic">Example 1:</span> Assign the value of the CURRENT PATH
|
||
|
|
special register to host variable HV1. </p>
|
||
|
|
<a name="rgrval1"></a>
|
||
|
|
<pre id="rgrval1" class="xmp"> EXEC SQL <span class="bold">VALUES CURRENT PATH
|
||
|
|
INTO</span> :HV1;</pre>
|
||
|
|
<p><span class="italic">Example 2:</span> Assume that LOB locator LOB1 is associated
|
||
|
|
with a CLOB value. Assign a portion of the CLOB value to host variable DETAILS
|
||
|
|
using the LOB locator, and assign CURRENT TIMESTAMP to the host variable TIMETRACK. </p>
|
||
|
|
<pre class="xmp"> EXEC SQL <span class="bold">VALUES (SUBSTR(</span>:LOB1,1,35<span class="bold">), CURRENT TIMESTAMP)
|
||
|
|
INTO</span> :DETAILS, :TIMETRACK;</pre>
|
||
|
|
<hr /><br />
|
||
|
|
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmstvaluestmt.htm">Previous Page</a> | <a href="rbafzmstwvrr.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>
|