1130 lines
64 KiB
HTML
1130 lines
64 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="assignments and comparisons,
|
|
SQL (Structured Query Language), description, operation, basic operations in SQL,
|
|
assignment operation, assignment, comparison operation, data types,
|
|
compatibility, rules, compatibility rules, comparison, null value in SQL,
|
|
assignments, numeric, truncation of numbers, numbers,
|
|
conversion of numbers in SQL, scale of data, scale and precision,
|
|
conversion of numbers, string, strings, binary string, binary strings,
|
|
character string, character strings, graphic string, graphic strings,
|
|
*NOCNULRQD precompiler option, truncated during assignment,
|
|
double-byte character, not truncated by assignments, shift-in character,
|
|
in string assignments, mixed data, *CNULRQD precompiler option,
|
|
conversion rule for comparisons, conversion rules,
|
|
DBCS (double-byte character set), double-byte character set (DBCS),
|
|
date and time values, date and time, DATE, TIME, TIMESTAMP, DataLink, Row ID,
|
|
distinct type, LOB Locators, comparisons, comparisons in SQL,
|
|
character data string, distinct type values" />
|
|
<title>Assignments and comparisons</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="ch2bas"></a>
|
|
<h2 id="ch2bas"><a href="rbafzmst02.htm#ToC_139">Assignments and comparisons</a></h2><a id="idx427" name="idx427"></a><a id="idx428" name="idx428"></a><a id="idx429" name="idx429"></a><a id="idx430" name="idx430"></a><a id="idx431" name="idx431"></a><a id="idx432" name="idx432"></a><a id="idx433" name="idx433"></a><a id="idx434" name="idx434"></a><a id="idx435" name="idx435"></a><a id="idx436" name="idx436"></a><a id="idx437" name="idx437"></a><a id="idx438" name="idx438"></a>
|
|
<p>The basic operations of SQL are assignment and comparison. Assignment operations
|
|
are performed during the execution of CALL, INSERT, UPDATE, FETCH, SELECT,
|
|
SET variable, and VALUES INTO statements. Comparison operations are performed
|
|
during the execution of statements that include predicates and other language
|
|
elements such as MAX, MIN, DISTINCT, GROUP BY, and ORDER BY.</p>
|
|
<p>The basic rule for both operations is that the data type of the operands
|
|
involved must be compatible. The compatibility rule also applies to UNION,
|
|
EXCEPT, INTERSECT, concatenation, CASE expressions, and the CONCAT, VALUE,
|
|
COALESCE, IFNULL, MIN, and MAX scalar functions. The compatibility matrix
|
|
is as follows:</p>
|
|
<a name="wq147"></a>
|
|
<table id="wq147" width="100%" summary="" border="1" frame="hsides" rules="rows">
|
|
<caption>Table 15. Data Type Compatibility</caption>
|
|
<thead valign="bottom">
|
|
<tr>
|
|
<th id="wq148" width="20%" align="left" valign="bottom">
|
|
<div class="lines"> <br />
|
|
<br />
|
|
<br />
|
|
Operands<br />
|
|
<br />
|
|
<br />
|
|
<br />
|
|
</div></th>
|
|
<th id="wq149" width="6%" align="left" valign="middle">
|
|
<div class="lines"> Binary Integer<br />
|
|
</div></th>
|
|
<th id="wq150" width="6%" align="left" valign="middle">
|
|
<div class="lines"> Decimal Number <br />
|
|
</div></th>
|
|
<th id="wq151" width="6%" align="left" valign="middle">
|
|
<div class="lines"> Floating Point<br />
|
|
</div></th>
|
|
<th id="wq152" width="6%" align="left" valign="middle">
|
|
<div class="lines"> Character String<br />
|
|
</div></th>
|
|
<th id="wq153" width="6%" align="left" valign="middle">
|
|
<div class="lines"> Graphic String<br />
|
|
</div></th>
|
|
<th id="wq154" width="6%" align="left" valign="middle">
|
|
<div class="lines"> Binary String<br />
|
|
</div></th>
|
|
<th id="wq155" width="6%" align="left" valign="middle">
|
|
<div class="lines"> Date<br />
|
|
</div></th>
|
|
<th id="wq156" width="6%" align="left" valign="middle">
|
|
<div class="lines"> Time<br />
|
|
</div></th>
|
|
<th id="wq157" width="6%" align="left" valign="middle">
|
|
<div class="lines"> Timestamp<br />
|
|
</div></th>
|
|
<th id="wq158" width="6%" align="left" valign="middle">
|
|
<div class="lines"> DataLink<br />
|
|
</div></th>
|
|
<th id="wq159" width="6%" align="left" valign="middle">
|
|
<div class="lines"> Row ID<br />
|
|
</div></th>
|
|
<th id="wq160" width="6%" align="left" valign="middle">
|
|
<div class="lines"> Distinct Type<br />
|
|
</div></th>
|
|
</tr>
|
|
</thead>
|
|
<tbody valign="top">
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq148">Binary Integer</td>
|
|
<td align="center" valign="top" headers="wq149">Yes</td>
|
|
<td align="center" valign="top" headers="wq150">Yes</td>
|
|
<td align="center" valign="top" headers="wq151">Yes</td>
|
|
<td align="center" valign="top" headers="wq152">Yes</td>
|
|
<td align="center" valign="top" headers="wq153">1</td>
|
|
<td align="center" valign="top" headers="wq154">No</td>
|
|
<td align="center" valign="top" headers="wq155">No</td>
|
|
<td align="center" valign="top" headers="wq156">No</td>
|
|
<td align="center" valign="top" headers="wq157">No</td>
|
|
<td align="center" valign="top" headers="wq158">No</td>
|
|
<td align="center" valign="top" headers="wq159">No</td>
|
|
<td align="center" valign="top" headers="wq160">4</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq148">Decimal Number <sup>5</sup></td>
|
|
<td align="center" valign="top" headers="wq149">Yes</td>
|
|
<td align="center" valign="top" headers="wq150">Yes</td>
|
|
<td align="center" valign="top" headers="wq151">Yes</td>
|
|
<td align="center" valign="top" headers="wq152">Yes</td>
|
|
<td align="center" valign="top" headers="wq153">1</td>
|
|
<td align="center" valign="top" headers="wq154">No</td>
|
|
<td align="center" valign="top" headers="wq155">No</td>
|
|
<td align="center" valign="top" headers="wq156">No</td>
|
|
<td align="center" valign="top" headers="wq157">No</td>
|
|
<td align="center" valign="top" headers="wq158">No</td>
|
|
<td align="center" valign="top" headers="wq159">No</td>
|
|
<td align="center" valign="top" headers="wq160">4</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq148">Floating Point</td>
|
|
<td align="center" valign="top" headers="wq149">Yes</td>
|
|
<td align="center" valign="top" headers="wq150">Yes</td>
|
|
<td align="center" valign="top" headers="wq151">Yes</td>
|
|
<td align="center" valign="top" headers="wq152">Yes</td>
|
|
<td align="center" valign="top" headers="wq153">1</td>
|
|
<td align="center" valign="top" headers="wq154">No</td>
|
|
<td align="center" valign="top" headers="wq155">No</td>
|
|
<td align="center" valign="top" headers="wq156">No</td>
|
|
<td align="center" valign="top" headers="wq157">No</td>
|
|
<td align="center" valign="top" headers="wq158">No</td>
|
|
<td align="center" valign="top" headers="wq159">No</td>
|
|
<td align="center" valign="top" headers="wq160">4</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq148">Character String</td>
|
|
<td align="center" valign="top" headers="wq149">Yes</td>
|
|
<td align="center" valign="top" headers="wq150">Yes</td>
|
|
<td align="center" valign="top" headers="wq151">Yes</td>
|
|
<td align="center" valign="top" headers="wq152">Yes</td>
|
|
<td align="center" valign="top" headers="wq153">1</td>
|
|
<td align="center" valign="top" headers="wq154">2</td>
|
|
<td align="center" valign="top" headers="wq155">3</td>
|
|
<td align="center" valign="top" headers="wq156">3</td>
|
|
<td align="center" valign="top" headers="wq157">3</td>
|
|
<td align="center" valign="top" headers="wq158">No</td>
|
|
<td align="center" valign="top" headers="wq159">No</td>
|
|
<td align="center" valign="top" headers="wq160">4</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq148">Graphic String</td>
|
|
<td align="center" valign="top" headers="wq149">1</td>
|
|
<td align="center" valign="top" headers="wq150">1</td>
|
|
<td align="center" valign="top" headers="wq151">1</td>
|
|
<td align="center" valign="top" headers="wq152">1</td>
|
|
<td align="center" valign="top" headers="wq153">Yes</td>
|
|
<td align="center" valign="top" headers="wq154">No</td>
|
|
<td align="center" valign="top" headers="wq155">1 3</td>
|
|
<td align="center" valign="top" headers="wq156">1 3</td>
|
|
<td align="center" valign="top" headers="wq157">1 3</td>
|
|
<td align="center" valign="top" headers="wq158">No</td>
|
|
<td align="center" valign="top" headers="wq159">No</td>
|
|
<td align="center" valign="top" headers="wq160">4</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq148">Binary String</td>
|
|
<td align="center" valign="top" headers="wq149">No</td>
|
|
<td align="center" valign="top" headers="wq150">No</td>
|
|
<td align="center" valign="top" headers="wq151">No</td>
|
|
<td align="center" valign="top" headers="wq152">2</td>
|
|
<td align="center" valign="top" headers="wq153">No</td>
|
|
<td align="center" valign="top" headers="wq154">Yes</td>
|
|
<td align="center" valign="top" headers="wq155">No</td>
|
|
<td align="center" valign="top" headers="wq156">No</td>
|
|
<td align="center" valign="top" headers="wq157">No</td>
|
|
<td align="center" valign="top" headers="wq158">No</td>
|
|
<td align="center" valign="top" headers="wq159">No</td>
|
|
<td align="center" valign="top" headers="wq160">4</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq148">Date</td>
|
|
<td align="center" valign="top" headers="wq149">No</td>
|
|
<td align="center" valign="top" headers="wq150">No</td>
|
|
<td align="center" valign="top" headers="wq151">No</td>
|
|
<td align="center" valign="top" headers="wq152">3</td>
|
|
<td align="center" valign="top" headers="wq153">1 3</td>
|
|
<td align="center" valign="top" headers="wq154">No</td>
|
|
<td align="center" valign="top" headers="wq155">Yes</td>
|
|
<td align="center" valign="top" headers="wq156">No</td>
|
|
<td align="center" valign="top" headers="wq157">No</td>
|
|
<td align="center" valign="top" headers="wq158">No</td>
|
|
<td align="center" valign="top" headers="wq159">No</td>
|
|
<td align="center" valign="top" headers="wq160">4</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq148">Time</td>
|
|
<td align="center" valign="top" headers="wq149">No</td>
|
|
<td align="center" valign="top" headers="wq150">No</td>
|
|
<td align="center" valign="top" headers="wq151">No</td>
|
|
<td align="center" valign="top" headers="wq152">3</td>
|
|
<td align="center" valign="top" headers="wq153">1 3</td>
|
|
<td align="center" valign="top" headers="wq154">No</td>
|
|
<td align="center" valign="top" headers="wq155">No</td>
|
|
<td align="center" valign="top" headers="wq156">Yes</td>
|
|
<td align="center" valign="top" headers="wq157">No</td>
|
|
<td align="center" valign="top" headers="wq158">No</td>
|
|
<td align="center" valign="top" headers="wq159">No</td>
|
|
<td align="center" valign="top" headers="wq160">4</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq148">Timestamp</td>
|
|
<td align="center" valign="top" headers="wq149">No</td>
|
|
<td align="center" valign="top" headers="wq150">No</td>
|
|
<td align="center" valign="top" headers="wq151">No</td>
|
|
<td align="center" valign="top" headers="wq152">3</td>
|
|
<td align="center" valign="top" headers="wq153">1 3</td>
|
|
<td align="center" valign="top" headers="wq154">No</td>
|
|
<td align="center" valign="top" headers="wq155">No</td>
|
|
<td align="center" valign="top" headers="wq156">No</td>
|
|
<td align="center" valign="top" headers="wq157">Yes</td>
|
|
<td align="center" valign="top" headers="wq158">No</td>
|
|
<td align="center" valign="top" headers="wq159">No</td>
|
|
<td align="center" valign="top" headers="wq160">4</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq148">DataLink</td>
|
|
<td align="center" valign="top" headers="wq149">No</td>
|
|
<td align="center" valign="top" headers="wq150">No</td>
|
|
<td align="center" valign="top" headers="wq151">No</td>
|
|
<td align="center" valign="top" headers="wq152">No</td>
|
|
<td align="center" valign="top" headers="wq153">No</td>
|
|
<td align="center" valign="top" headers="wq154">No</td>
|
|
<td align="center" valign="top" headers="wq155">No</td>
|
|
<td align="center" valign="top" headers="wq156">No</td>
|
|
<td align="center" valign="top" headers="wq157">No</td>
|
|
<td align="center" valign="top" headers="wq158">6</td>
|
|
<td align="center" valign="top" headers="wq159">No</td>
|
|
<td align="center" valign="top" headers="wq160">4</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq148">Row ID</td>
|
|
<td align="center" valign="top" headers="wq149">No</td>
|
|
<td align="center" valign="top" headers="wq150">No</td>
|
|
<td align="center" valign="top" headers="wq151">No</td>
|
|
<td align="center" valign="top" headers="wq152">No</td>
|
|
<td align="center" valign="top" headers="wq153">No</td>
|
|
<td align="center" valign="top" headers="wq154">No</td>
|
|
<td align="center" valign="top" headers="wq155">No</td>
|
|
<td align="center" valign="top" headers="wq156">No</td>
|
|
<td align="center" valign="top" headers="wq157">No</td>
|
|
<td align="center" valign="top" headers="wq158">No</td>
|
|
<td align="center" valign="top" headers="wq159">7</td>
|
|
<td align="center" valign="top" headers="wq160">4</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq148">Distinct Type</td>
|
|
<td align="center" valign="top" headers="wq149">4</td>
|
|
<td align="center" valign="top" headers="wq150">4</td>
|
|
<td align="center" valign="top" headers="wq151">4</td>
|
|
<td align="center" valign="top" headers="wq152">4</td>
|
|
<td align="center" valign="top" headers="wq153">4</td>
|
|
<td align="center" valign="top" headers="wq154">4</td>
|
|
<td align="center" valign="top" headers="wq155">4</td>
|
|
<td align="center" valign="top" headers="wq156">4</td>
|
|
<td align="center" valign="top" headers="wq157">4</td>
|
|
<td align="center" valign="top" headers="wq158">4</td>
|
|
<td align="center" valign="top" headers="wq159">4</td>
|
|
<td align="center" valign="top" headers="wq160">4</td>
|
|
</tr>
|
|
<tr>
|
|
<td colspan="13" align="left" valign="top" headers="wq148 wq149 wq150 wq151 wq152 wq153 wq154 wq155 wq156 wq157 wq158 wq159 wq160">
|
|
<a name="wq161"></a>
|
|
<div class="notetitle" id="wq161">Notes:</div>
|
|
<div class="notebody">
|
|
<ol type="1">
|
|
<li>Only UCS-2 and UTF-16 strings are compatible.</li>
|
|
<li>All character strings, even those with FOR BIT DATA, are not compatible
|
|
with binary strings except during assignments to or from variables or parameter
|
|
markers. In this case, FOR BIT DATA character strings and binary strings are
|
|
considered compatible and any padding is performed based on the data type
|
|
of the target. For example, when assigning a FOR BIT DATA column value to
|
|
a fixed-length binary variable, any necessary padding uses a pad byte of X'00'.</li>
|
|
<li>The datetime values and strings are not compatible in concatenation or
|
|
in the CONCAT scalar function.</li>
|
|
<li>A value with a distinct type is comparable only to a value that is defined
|
|
with the same distinct type. In general, the database manager supports assignments between
|
|
a distinct type value and its source data type. For additional information,
|
|
see <a href="rbafzmstch2bas.htm#udtasgn">Distinct type assignments</a>.</li>
|
|
<li>Decimal refers to both packed and zoned decimal.</li>
|
|
<li>A DataLink operand can only be assigned to another DataLink operand and
|
|
cannot be compared to any data type.</li>
|
|
<li>A ROWID operand can only be assigned to another ROWID operand and cannot
|
|
be compared to any data type.</li></ol></div></td>
|
|
</tr>
|
|
</tbody>
|
|
</table><a id="idx439" name="idx439"></a>
|
|
<p>A basic rule for assignment operations is that a null value cannot be assigned
|
|
to: </p>
|
|
<ul>
|
|
<li>a column that cannot contain null values</li>
|
|
<li>a host variable that does not have an associated indicator variable</li>
|
|
<li>a Java™ host variable that is a primitive type.</li></ul><p class="indatacontent"> See <a href="rbafzmstch2refvar.htm#ch2host">References to host variables</a> for a discussion of indicator variables.</p>
|
|
<p>For any comparison that involves null values, see the description of the
|
|
comparison operation for information about the specific handling of null
|
|
values.</p>
|
|
<a name="numass"></a>
|
|
<h3 id="numass"><a href="rbafzmst02.htm#ToC_140">Numeric assignments</a></h3><a id="idx440" name="idx440"></a><a id="idx441" name="idx441"></a><a id="idx442" name="idx442"></a>
|
|
<p>The basic rule for numeric assignments is that the whole part of a decimal
|
|
or integer number cannot be truncated. If necessary, the fractional part of
|
|
a decimal number is truncated.</p>
|
|
<p>An error occurs if: </p>
|
|
<ul>
|
|
<li>Truncation of the whole part of the number occurs on assignment to a column
|
|
or a parameter of a function or procedure</li>
|
|
<li>Truncation of the whole part of the number occurs on assignment to a host
|
|
variable that does not have an indicator variable</li></ul>
|
|
<p>A warning occurs if: </p>
|
|
<ul class="simple">
|
|
<li>Truncation of the whole part of the number occurs on assignment to a host
|
|
variable with an indicator variable. In this case, the number is not assigned
|
|
to the host variable and the indicator variable is set to negative 2.</li></ul>
|
|
<a name="wq163"></a>
|
|
<div class="notetitle" id="wq163">Note:</div>
|
|
<div class="notebody">Decimal refers to both packed and zoned decimal.</div>
|
|
<p></p>
|
|
<a name="wq164"></a>
|
|
<div class="notetitle" id="wq164">Note:</div>
|
|
<div class="notebody">When fetching decimal data from a file that was <span class="italic">not</span> created by an SQL CREATE TABLE statement, a decimal field may contain
|
|
data that is not valid. In this case, the data will be returned as stored,
|
|
without any warning or error message being issued. A table that is created
|
|
by the SQL CREATE TABLE statement does not allow decimal data that is not
|
|
valid.</div>
|
|
<a name="wq165"></a>
|
|
<h4 id="wq165">Decimal or integer to floating-point</h4>
|
|
<p>Floating-point numbers are approximations of real numbers. Hence, when
|
|
a decimal or integer number is assigned to a floating-point column or variable,
|
|
the result may not be identical to the original number.</p>
|
|
<p>The approximation is more accurate if the receiving column or variable
|
|
is defined as double precision (64 bits) rather than single precision (32
|
|
bits).</p>
|
|
<a name="wq166"></a>
|
|
<h4 id="wq166">Floating-point or decimal to integer</h4>
|
|
<p>When a floating-point or decimal number is assigned to a binary integer
|
|
column or variable, the number is converted, if necessary, to the precision
|
|
and the scale of the target. If the scale of the target is zero, the fractional
|
|
part of the number is lost. The necessary number of leading zeros is added
|
|
or eliminated, and the necessary number of trailing zeros in the fractional
|
|
part of the number is added or eliminated.</p>
|
|
<a name="wq167"></a>
|
|
<h4 id="wq167">Decimal to decimal</h4><a id="idx443" name="idx443"></a><a id="idx444" name="idx444"></a>
|
|
<p>When a decimal number is assigned to a decimal column or variable, the
|
|
number is converted, if necessary, to the precision and the scale of the target.
|
|
The necessary number of leading zeros is added or eliminated, and the necessary
|
|
number of trailing zeros in the fractional part of the number is added or
|
|
eliminated.</p>
|
|
<a name="wq168"></a>
|
|
<h4 id="wq168">Integer to decimal</h4>
|
|
<p>When an integer is assigned to a decimal column or variable, the number
|
|
is converted first to a temporary decimal number and then, if necessary, to
|
|
the precision and scale of the target. If the scale of the integer is zero,
|
|
the precision of the temporary decimal number is 5,0 for a small integer,
|
|
11,0 for a large integer, or 19,0 for a big integer.</p>
|
|
<a name="wq169"></a>
|
|
<h4 id="wq169">Floating-point to decimal</h4>
|
|
<p>When a floating-point number is assigned to a decimal column or variable,
|
|
the number is first converted to a temporary decimal number of precision 63
|
|
and then, if necessary, truncated to the precision and scale of the target.
|
|
In this conversion, the number is rounded (using floating-point arithmetic)
|
|
to a precision of 63 decimal digits. As a result, a number less than 0.5*10<sup>-63</sup> is reduced to 0. The scale is given the largest
|
|
possible value that allows the whole part of the number to be represented
|
|
without loss of significance.</p>
|
|
<a name="wq170"></a>
|
|
<h4 id="wq170">Assignments to COBOL and RPG integers</h4>
|
|
<p>Assignment to COBOL and RPG small or large integer host variables takes
|
|
into account any scale specified for the host variable. However, assignment
|
|
to integer host variables uses the full size of the integer. Thus, the value
|
|
placed in the COBOL data item or RPG field may be larger than the maximum
|
|
precision specified for the host variable.</p>
|
|
<p>In COBOL, for example, if COL1 contains a value of 12345, the statements: </p>
|
|
<pre class="xmp"> 01 A PIC S9999 BINARY.
|
|
EXEC SQL <span class="bold">SELECT</span> COL1
|
|
<span class="bold">INTO</span> :A
|
|
<span class="bold">FROM</span> TABLEX
|
|
END-EXEC.</pre><p class="indatacontent"> result in the value 12345 being placed in <tt class="xph">A</tt>,
|
|
even though <tt class="xph">A</tt> has been defined with only 4 digits.</p>
|
|
<p>Notice that the following COBOL statement: </p>
|
|
<pre class="xmp"> MOVE 12345 TO A.</pre><p class="indatacontent"> results
|
|
in 2345 being placed in <tt class="xph">A</tt>. <a id="idx445" name="idx445"></a></p>
|
|
<a name="wq171"></a>
|
|
<h4 id="wq171">Strings to numeric</h4>
|
|
<p>When a string is assigned to a numeric data type, it is converted to the
|
|
target numeric data type using the rules for a CAST specification. For more
|
|
information, see <a href="rbafzmstdatetimearith.htm#cast">CAST specification</a>.</p>
|
|
<a name="charas"></a>
|
|
<h3 id="charas"><a href="rbafzmst02.htm#ToC_148">String assignments</a></h3><a id="idx446" name="idx446"></a><a id="idx447" name="idx447"></a>
|
|
<p>There are two types of string assignments:</p>
|
|
<ul>
|
|
<li><span class="italic">Storage assignment</span> is when a value is assigned
|
|
to a column or a parameter of a function or stored procedure.</li>
|
|
<li><span class="italic">Retrieval assignment</span> is when a value is assigned
|
|
to a variable.</li></ul>
|
|
<a name="wq172"></a>
|
|
<h4 id="wq172">Binary string assignments</h4><a id="idx448" name="idx448"></a><a id="idx449" name="idx449"></a>
|
|
<a name="wq173"></a>
|
|
<h5 id="wq173">Storage assignment</h5>
|
|
<p>The basic rule is that the length of a string assigned to a column or parameter
|
|
of a function or procedure must not be greater than the length attribute of
|
|
the column or parameter. If the string is longer than the length attribute
|
|
of that column or parameter, a error is returned. Trailing hexadecimal zeroes
|
|
(X'00') are normally included in the length of the string. For storage assignments,
|
|
however, trailing hexadecimal zeroes are not included in the length of the
|
|
string.</p>
|
|
<p>When a string is assigned to a fixed-length binary-string column or parameter
|
|
and the length of the string is less than the length attribute of the target,
|
|
the string is padded on the right with the necessary number of hexadecimal
|
|
zeroes.</p>
|
|
<a name="reta"></a>
|
|
<h5 id="reta">Retrieval assignment</h5><a id="idx450" name="idx450"></a>
|
|
<p>The length of a string assigned to a variable can be greater than the length
|
|
attribute of the variable. When a string is assigned to a variable and the
|
|
string is longer than the length attribute of the variable, the string is
|
|
truncated on the right by the necessary number of bytes. When this occurs,
|
|
an SQLSTATE of '01004' is assigned to the RETURNED_SQLSTATE condition area
|
|
item in the SQL Diagnostics Area (or the value 'W' is assigned to
|
|
the SQLWARN1 field of the SQLCA).</p>
|
|
<p>When a string is assigned to a fixed-length binary-string variable and
|
|
the length of the string is less than the length attribute of the target,
|
|
the string is padded on the right with the necessary number of hexadecimal
|
|
zeroes.</p>
|
|
<p>When a string of length <span class="italic">n</span> is assigned to a varying-length
|
|
string variable with a maximum length greater than <span class="italic">n</span>,
|
|
the bytes after the <span class="italic">n</span>th byte of the variable are undefined.</p>
|
|
<a name="wq174"></a>
|
|
<h4 id="wq174">Character and graphic string assignments</h4><a id="idx451" name="idx451"></a><a id="idx452" name="idx452"></a><a id="idx453" name="idx453"></a><a id="idx454" name="idx454"></a>
|
|
<p>The following rules apply when the assignment target is a string. When
|
|
a datetime data type is involved, see <a href="rbafzmstch2bas.htm#dta">Datetime assignments</a>. For the special
|
|
considerations that apply when a distinct type is involved in an assignment,
|
|
especially to a variable, see <a href="rbafzmstch2bas.htm#udtasgn">Distinct type assignments</a>.</p>
|
|
<a name="wq175"></a>
|
|
<h5 id="wq175">Numeric to strings</h5>
|
|
<p>When a number is assigned to a string data type, it is converted to the
|
|
target string data type using the rules for a CAST specification. For more
|
|
information, see <a href="rbafzmstdatetimearith.htm#cast">CAST specification</a>.</p>
|
|
<a name="wq176"></a>
|
|
<h5 id="wq176">Storage assignment</h5>
|
|
<p>The basic rule is that the length of a string assigned to a column or parameter
|
|
of a function or procedure must not be greater than the length attribute of
|
|
the column or parameter. If the string is longer than the length attribute
|
|
of that column or parameter, an error is returned. Trailing blanks are normally
|
|
included in the length of the string. For storage assignments, however, trailing
|
|
blanks are not included in the length of the string.</p>
|
|
<p>When
|
|
a string is assigned to a fixed-length string column or parameter and the
|
|
length of the string is less than the length attribute of the target, the
|
|
string is padded on the right with the necessary number of single-byte, double-byte,
|
|
or UTF-16 or UCS-2 blanks.<sup class="fn"><a href="rbafzmstch2bas.htm#fn13">22</a></sup> The pad character is always
|
|
a blank, even for bit data.</p>
|
|
<a name="wq177"></a>
|
|
<h5 id="wq177">Retrieval assignment</h5><a id="idx455" name="idx455"></a>
|
|
<p>The length of a string assigned to a variable can be greater than the length
|
|
attribute of the variable. When a string is assigned to a variable and the
|
|
string is longer than the length attribute of the variable, the string is
|
|
truncated on the right by the necessary number of characters. When this occurs,
|
|
an SQLSTATE of '01004' is assigned to the RETURNED_SQLSTATE condition area
|
|
item in the SQL Diagnostics Area (or the value 'W' is assigned to
|
|
the SQLWARN1 field of the SQLCA). Furthermore, if an indicator variable is
|
|
provided, it is set to the original length of the string. If only the NUL-terminator
|
|
is truncated for a C NUL-terminated host variable and the *NOCNULRQD option
|
|
was specified on the CRTSQLCI or CRTSQLCPPI command (or CNULRQD(*NO) on the
|
|
SET OPTION statement), an SQLSTATE of '01004' is assigned to the RETURNED_SQLSTATE
|
|
condition area item in the SQL Diagnostics Area (or the value of 'N'
|
|
is assigned to the SQLWARN1 field of the SQLCA) and a NUL is not placed in
|
|
the variable.</p>
|
|
<p>When a string is assigned to a fixed-length variable and the length of
|
|
the string is less than the length attribute of the target, the string is
|
|
padded on the right with the necessary number of single-byte, double-byte,
|
|
or UTF-16 or UCS-2 blanks.<sup class="fn"><a href="rbafzmstch2bas.htm#fn13">22</a></sup> The pad character is always
|
|
a blank, even for bit data.</p>
|
|
<p>When a string of length <span class="italic">n</span> is assigned to a varying-length
|
|
string variable with a maximum length greater than <span class="italic">n</span>,
|
|
the characters after the <span class="italic">n</span>th character of the variable
|
|
are undefined.</p>
|
|
<a name="wq178"></a>
|
|
<h5 id="wq178">Assignments to mixed strings</h5><a id="idx456" name="idx456"></a><a id="idx457" name="idx457"></a><a id="idx458" name="idx458"></a>
|
|
<p>If a string contains mixed data, the assignment rules may require truncation
|
|
within a sequence of double-byte codes. To prevent the loss of the shift-in
|
|
character that ends the double-byte sequence, additional characters may be
|
|
truncated from the end of the string, and a shift-in character added. In the
|
|
truncated result, there is always an even number of bytes between each shift-out
|
|
character and its matching shift-in character.</p>
|
|
<a name="wq179"></a>
|
|
<h5 id="wq179">Assignments to C NUL-terminated strings</h5><a id="idx459" name="idx459"></a><a id="idx460" name="idx460"></a>
|
|
<p>When a string of length <span class="italic">n</span> is assigned to a C NUL-terminated
|
|
string variable with a length greater than <span class="italic">n+1</span>: </p>
|
|
<ul>
|
|
<li>If the *CNULRQD option was specified on the CRTSQLCI or CRTSQLCPPI command
|
|
(or CNULRQD(*YES) on the SET OPTION statement), the string is padded on the
|
|
right with x-n-1 blanks where x is the length of the variable. The padded
|
|
string is then assigned to the variable and the NUL-terminator is placed in
|
|
the next character position.</li>
|
|
<li>If the *NOCNULRQD precompiler option was specified on the CRTSQLCI or
|
|
CRTSQLCPPI command (or CNULRQD(*NO) on the SET OPTION statement), the string
|
|
is not padded on the right. The string is assigned to the variable and the
|
|
NUL-terminator is placed in the next character position.</li></ul>
|
|
<a name="craj"></a>
|
|
<h5 id="craj">Conversion rules for assignments</h5><a id="idx461" name="idx461"></a><a id="idx462" name="idx462"></a>
|
|
<p>A string assigned to a column or variable is first converted, if necessary,
|
|
to the coded character set of the target. Character conversion is necessary
|
|
only if all of the following are true: </p>
|
|
<ul>
|
|
<li>The CCSIDs are different.</li>
|
|
<li>Neither CCSID is 65535.</li>
|
|
<li>The string is neither null nor empty.</li>
|
|
<li>The CCSID Conversion Selection Table indicates that conversion is necessary.</li></ul>
|
|
<p>An error occurs if: </p>
|
|
<ul>
|
|
<li>The CCSID Conversion Selection Table is used but does not contain any
|
|
information about the pair of CCSIDs.</li>
|
|
<li>A character of the string cannot be converted, and the operation is assignment
|
|
to a column or assignment to a host variable without an indicator variable.
|
|
For example, a double-byte character (DBCS) cannot be converted to a column
|
|
or host variable with a single-byte character (SBCS) CCSID.</li></ul>
|
|
<p>A warning occurs if: </p>
|
|
<ul>
|
|
<li>A character of the string is converted to the substitution character.</li>
|
|
<li>A character of the string cannot be converted, and the operation is assignment
|
|
to a host variable with an indicator variable. For example, a DBCS character
|
|
cannot be converted to a host variable with an SBCS CCSID. In this case, the
|
|
string is not assigned to the host variable and the indicator variable is
|
|
set to -2.</li></ul><a id="idx463" name="idx463"></a><a id="idx464" name="idx464"></a><a id="idx465" name="idx465"></a><a id="idx466" name="idx466"></a>
|
|
<p></p>
|
|
<a name="dta"></a>
|
|
<h3 id="dta"><a href="rbafzmst02.htm#ToC_159">Datetime assignments</a></h3><a id="idx467" name="idx467"></a><a id="idx468" name="idx468"></a>
|
|
<p>A value assigned to a DATE column must be a date or a valid string representation
|
|
of a date. A date can only be assigned to a DATE column, a string column,
|
|
a string variable, or a date variable. A value assigned to a TIME column must
|
|
be a time or a valid string representation of a time. A time can only be assigned
|
|
to a TIME column, a string column, a string variable, or a time variable.
|
|
A value assigned to a TIMESTAMP column must be a timestamp or a valid string
|
|
representation of a timestamp. A timestamp can only be assigned to a TIMESTAMP
|
|
column, a string column, a string variable, or a timestamp variable.</p>
|
|
<p>When a datetime value is assigned to a string variable or column, it is
|
|
converted to its string representation. Leading zeros are not omitted from
|
|
any part of the date, time, or timestamp. The required length of the target
|
|
varies depending on the format of the string representation. If the length
|
|
of the target is greater than required, it is padded on the right with blanks.
|
|
If the length of the target is less than required, the result depends on the
|
|
type of datetime value involved and on the type of target. </p>
|
|
<ul>
|
|
<li>If the target is a string column, truncation is not allowed. The following
|
|
rules apply:
|
|
<p><span class="bold">DATE</span> </p>
|
|
<ul class="simple">
|
|
<li>The length attribute of the column must be at least 10 if the date format
|
|
is *ISO, USA, *EUR, or *JIS. If the date format is *YMD, *MDY, or *DMY, the
|
|
length attribute of the column must be at least 8. If the date format is *JUL,
|
|
the length of the variable must be at least 6.</li></ul>
|
|
<p><span class="bold">TIME</span> </p>
|
|
<ul class="simple">
|
|
<li>The length attribute of the column must be at least 8.</li></ul>
|
|
<p><span class="bold">TIMESTAMP</span> </p>
|
|
<ul class="simple">
|
|
<li>The length attribute of the column must be at least 26.</li></ul></li>
|
|
<li>When the target is a variable, the following rules apply:
|
|
<p><span class="bold">DATE</span> <a id="idx469" name="idx469"></a></p>
|
|
<ul class="simple">
|
|
<li>The length of the variable must be at least 10 if the date format is *ISO,
|
|
*USA, *EUR, or *JIS. If the date format is *YMD, *MDY, or *DMY, the length
|
|
of the variable must be at least 8. If the date format is *JUL, the length
|
|
of the variable must be at least 6.</li></ul>
|
|
<p><span class="bold">TIME</span> <a id="idx470" name="idx470"></a></p>
|
|
<ul>
|
|
<li>If the *USA format is used, the length of the variable must not be less
|
|
than 8. This format does not include seconds.</li>
|
|
<li>If the *ISO, *EUR, *JIS, or *HMS time format is used, the length of the
|
|
variable must not be less than 5. If the length is 5, 6, or 7, the seconds
|
|
part of the time is omitted from the result, and SQLWARN1 is set to 'W'.
|
|
In this case, the seconds part of the time is assigned to the indicator variable
|
|
if one is provided, and, if the length is 6 or 7, blank padding occurs so
|
|
that the value is a valid string representation of a time.</li></ul>
|
|
<p><span class="bold">TIMESTAMP</span> <a id="idx471" name="idx471"></a><a id="idx472" name="idx472"></a></p>
|
|
<ul class="simple">
|
|
<li>The length of the variable must not be less than 19. If the length is
|
|
between 19 and 25, the timestamp is truncated like a string, causing the omission
|
|
of one or more digits of the microsecond part. If the length is 20, the trailing
|
|
decimal point is replaced by a blank so that the value is a valid string representation
|
|
of a timestamp.</li></ul></li></ul>
|
|
<a name="wq180"></a>
|
|
<h3 id="wq180"><a href="rbafzmst02.htm#ToC_160">DataLink assignments</a></h3><a id="idx473" name="idx473"></a><a id="idx474" name="idx474"></a>
|
|
<p>The assignment of a value to a DataLink column results in the establishment
|
|
of a link to a file unless the linkage attributes of the value are empty or
|
|
the column is defined with NO LINK CONTROL. In cases where a linked value
|
|
already exists in the column, that file is unlinked. Assigning a null value
|
|
where a linked value already exists also unlinks the file associated with
|
|
the old value.</p>
|
|
<p>If the application provides the same data location as already exists in
|
|
the column, the link is retained. There are two reasons that this might be
|
|
done: </p>
|
|
<ul>
|
|
<li>the comment is being changed</li>
|
|
<li>if the table is placed in link pending state, the links in the table can
|
|
be reinstated by providing linkage attributes identical to the ones in the
|
|
column.</li></ul>
|
|
<p>A DataLink value may be assigned to a column by using the DLVALUE scalar
|
|
function. The DLVALUE scalar function creates a new DataLink value which can
|
|
then be assigned a column. Unless the value contains only a comment or the
|
|
URL is exactly the same, the act of assignment will link the file.</p>
|
|
<p>When assigning a value to a DataLink column, the following error conditions
|
|
can occur: </p>
|
|
<ul>
|
|
<li>Data Location (URL) format is invalid</li>
|
|
<li>File server is not registered with this database</li>
|
|
<li>Invalid link type specified</li>
|
|
<li>Invalid length of comment or URL
|
|
<p>Note that the size of a URL parameter
|
|
or function result is the same on both input or output and is bound by the
|
|
length of the DataLink column. However, in some cases the URL value returned
|
|
has an access token attached. In situations where this is possible, the output
|
|
location must have sufficient storage space for the access token and the length
|
|
of the DataLink column. Hence, the actual length of the comment and URL in
|
|
its fully expanded form provided on input should be restricted to accommodate
|
|
the output storage space. If the restricted length is exceeded, this error
|
|
is raised.</p></li></ul>
|
|
<p>When the assignment is also creating a link, the following errors can occur: </p>
|
|
<ul>
|
|
<li>File server not currently available.</li>
|
|
<li>File does not exist.</li>
|
|
<li>Referenced file cannot be accessed for linking.</li>
|
|
<li>File already linked to another column.
|
|
<p>Note that this error will be
|
|
raised even if the link is to a different relational database.</p></li></ul>
|
|
<p>In addition, when the assignment removes an existing link, the following
|
|
errors can occur: </p>
|
|
<ul>
|
|
<li>File server not currently available.</li>
|
|
<li>File with referential integrity control is not in a correct state according
|
|
to the DB2® DataLinks File Manager.</li></ul>
|
|
<p>A DataLink value may be retrieved from the database through the use of
|
|
scalar functions (such as DLLINKTYPE and DLURLPATH). The results of these
|
|
scalar functions can then be assigned to variables.</p>
|
|
<p>Note that usually no attempt is made to access the file server at retrieval
|
|
time. <sup class="fn"><a id="wq181" name="wq181" href="rbafzmstch2bas.htm#wq182">23</a></sup>It is therefore possible that subsequent attempts to access the file
|
|
server through file system commands might fail.</p>
|
|
<p>A warning may be returned when retrieving a DataLink value because the
|
|
table is in link pending state.</p>
|
|
<a name="wq183"></a>
|
|
<h3 id="wq183"><a href="rbafzmst02.htm#ToC_161">Row ID assignments</a></h3><a id="idx475" name="idx475"></a><a id="idx476" name="idx476"></a>
|
|
<p>A row ID value can only be assigned to a column, parameter, or variable
|
|
with a row ID data type. For the value of the ROWID column, the column must
|
|
be defined as GENERATED BY DEFAULT or OVERRIDING SYSTEM VALUE must be specified.
|
|
A unique constraint is implicitly added to every table that has a ROWID column
|
|
that guarantees that every ROWID value is unique. The value that is specified
|
|
for the column must be a valid row ID value that was previously generated
|
|
by DB2 UDB for z/OS or DB2 UDB for iSeries.</p>
|
|
<a name="udtasgn"></a>
|
|
<h3 id="udtasgn"><a href="rbafzmst02.htm#ToC_162">Distinct type assignments</a></h3><a id="idx477" name="idx477"></a><a id="idx478" name="idx478"></a>
|
|
<p>The rules that apply to the assignments of distinct types to variables are different
|
|
than the rules for all other assignments that involve distinct types.</p>
|
|
<a name="wq184"></a>
|
|
<h4 id="wq184">Assignments to variables</h4>
|
|
<p>The assignment of a distinct type to a variable is based on the source
|
|
data type of the distinct type. Therefore, the value of a distinct type is
|
|
assignable to a variable only if the source data type of the distinct type
|
|
is assignable to the variable.</p>
|
|
<p><var class="pv">Example:</var> Assume that distinct type AGE was created with the following
|
|
SQL statement and column STU_AGE in table STUDENTS was defined with that distinct
|
|
type. Using the CL_SCHED table, select all the classes (CLASS_CODE) that start
|
|
(STARTING) later today. Today's classes have a value of 3 in the DAY column. </p>
|
|
<pre class="xmp"> <span class="bold">CREATE DISTINCT TYPE</span> AGE <span class="bold">AS SMALLINT WITH COMPARISONS</span></pre>
|
|
<p> When the statement is executed, the following cast functions are also
|
|
generated: </p>
|
|
<pre class="xmp"> AGE <span class="bold">(SMALLINT) RETURNS</span> AGE
|
|
AGE <span class="bold">(INTEGER) RETURNS</span> AGE
|
|
SMALLINT (AGE) <span class="bold">RETURNS SMALLINT</span></pre>
|
|
<p>Next, assume that column STU_AGE was defined in table STUDENTS with distinct
|
|
type AGE. Now, consider this valid assignment of a student's age to host
|
|
variable HV_AGE, which has an INTEGER data type: </p>
|
|
<pre class="xmp"> <span class="bold">SELECT </span> STU_AGE <span class="bold">INTO</span> :HV_AGE <span class="bold">FROM</span> STUDENTS <span class="bold">WHERE</span> STU_NUMBER = 200</pre>
|
|
<p>The distinct type value is assignable to the host variable HV_AGE because
|
|
the source data type of the distinct type (SMALLINT) is assignable to the
|
|
host variable (INTEGER). If distinct type AGE had been sourced on a character
|
|
data type such as CHAR(5), the above assignment would be invalid because a
|
|
character type cannot be assigned to an integer type.</p>
|
|
<a name="wq185"></a>
|
|
<h4 id="wq185">Assignments other than to variables</h4>
|
|
<p>A distinct type can be either the source or target of an assignment. Assignment
|
|
is based on whether the data type of the value to be assigned is castable
|
|
to the data type of the target. <a href="rbafzmstcastdt.htm#castdt">Casting between data types</a> shows which casts are
|
|
supported when a distinct type is involved. Therefore, a distinct type value
|
|
can be assigned to any target other than a variable when:</p>
|
|
<ul>
|
|
<li>The target of the assignment has the same distinct type, or</li>
|
|
<li>The distinct type is castable to the data type of the target</li></ul>
|
|
<p>Any value can be assigned to a distinct type when: </p>
|
|
<ul>
|
|
<li>The value to be assigned has the same distinct type as the target, or</li>
|
|
<li>The data type of the assigned value is castable to the target distinct
|
|
type</li></ul>
|
|
<p><var class="pv">Example:</var> Assume that the source data type for distinct type AGE
|
|
is SMALLINT: </p>
|
|
<pre class="xmp"> <span class="bold">CREATE DISTINCT TYPE</span> AGE <span class="bold">AS SMALLINT WITH COMPARISONS</span></pre>
|
|
<p>Next, assume that two tables TABLE1 and TABLE2 were created with four identical
|
|
column descriptions:</p>
|
|
<pre class="xmp"> AGECOL AGE
|
|
SMINTCOL SMALLINT
|
|
INTCOL INTEGER
|
|
DECCOL DEC(6,2) </pre>
|
|
<p>Using the following SQL statement and substituting various values for X
|
|
and Y to insert values into various columns of TABLE1 from TABLE2, <a href="rbafzmstch2bas.htm#assdtt1">Table 16</a> shows
|
|
whether the assignments are valid. </p>
|
|
<pre class="xmp"> <span class="bold">INSERT INTO </span> TABLE1 (Y) <span class="bold">SELECT</span> X <span class="bold">FROM</span> TABLE2</pre>
|
|
<a name="assdtt1"></a>
|
|
<table id="assdtt1" width="100%" summary="" border="1" frame="border" rules="rows">
|
|
<caption>Table 16. Assessment of various assignments (for example on INSERT)</caption>
|
|
<thead valign="bottom">
|
|
<tr>
|
|
<th id="wq186" width="19%" align="left" valign="bottom">TABLE2.X</th>
|
|
<th id="wq187" width="19%" align="left" valign="bottom">TABLE1.Y</th>
|
|
<th id="wq188" width="19%" align="left" valign="bottom">Valid</th>
|
|
<th id="wq189" width="42%" align="left" valign="bottom">Reason</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody valign="top">
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq186">AGECOL</td>
|
|
<td align="left" valign="top" headers="wq187">AGECOL</td>
|
|
<td align="left" valign="top" headers="wq188">Yes</td>
|
|
<td align="left" valign="top" headers="wq189">Source and target are same distinct type</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq186">SMINTCOL</td>
|
|
<td align="left" valign="top" headers="wq187">AGECOL</td>
|
|
<td align="left" valign="top" headers="wq188">Yes</td>
|
|
<td align="left" valign="top" headers="wq189">SMALLINT can be cast to AGE (because AGE's
|
|
source type is SMALLINT)</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq186">INTCOL</td>
|
|
<td align="left" valign="top" headers="wq187">AGECOL</td>
|
|
<td align="left" valign="top" headers="wq188">Yes</td>
|
|
<td align="left" valign="top" headers="wq189">INTEGER can be cast to AGE (because AGE's
|
|
source type is SMALLINT)</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq186">DECCOL</td>
|
|
<td align="left" valign="top" headers="wq187">AGECOL</td>
|
|
<td align="left" valign="top" headers="wq188">No</td>
|
|
<td align="left" valign="top" headers="wq189">DECIMAL cannot be cast to AGE</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq186">AGECOL</td>
|
|
<td align="left" valign="top" headers="wq187">SMINTCOL</td>
|
|
<td align="left" valign="top" headers="wq188">Yes</td>
|
|
<td align="left" valign="top" headers="wq189">AGE can be cast to its source type SMALLINT</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq186">AGECOL</td>
|
|
<td align="left" valign="top" headers="wq187">INTCOL</td>
|
|
<td align="left" valign="top" headers="wq188">No</td>
|
|
<td align="left" valign="top" headers="wq189">AGE cannot be cast to INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq186">AGECOL</td>
|
|
<td align="left" valign="top" headers="wq187">DECCOL</td>
|
|
<td align="left" valign="top" headers="wq188">No</td>
|
|
<td align="left" valign="top" headers="wq189">AGE cannot be cast to DECIMAL</td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
<a name="wq190"></a>
|
|
<h3 id="wq190"><a href="rbafzmst02.htm#ToC_165">Assignments to LOB locators</a></h3><a id="idx479" name="idx479"></a><a id="idx480" name="idx480"></a>
|
|
<p>When a LOB locator is used, it can refer to any string data. If a LOB locator
|
|
is used for the first fetch of a cursor and the cursor is on a remote server,
|
|
LOB locators must be used for all subsequent fetches unless the *NOOPTLOB
|
|
precompile option is used.</p>
|
|
<a name="wq191"></a>
|
|
<h3 id="wq191"><a href="rbafzmst02.htm#ToC_166">Numeric comparisons</a></h3><a id="idx481" name="idx481"></a><a id="idx482" name="idx482"></a><a id="idx483" name="idx483"></a><a id="idx484" name="idx484"></a>
|
|
<p>Numbers are compared algebraically; that is, with regard to sign. For example,
|
|
–2 is less than +1.</p>
|
|
<p>If one number is an integer and the other number is decimal, the comparison
|
|
is made with a temporary copy of the integer that has been converted to decimal.</p>
|
|
<p>When decimal or nonzero scale binary numbers with different scales are
|
|
compared, the comparison is made with a temporary copy of one of the numbers
|
|
that has been extended with trailing zeros so that its fractional part has
|
|
the same number of digits as the other number.</p>
|
|
<p>If one number is floating point and the other is integer, decimal, or single-precision
|
|
floating point, the comparison is made with a temporary copy of the second
|
|
number converted to a double-precision floating-point number. However, if
|
|
a single-precision floating-point column is compared to a constant and the
|
|
constant can be represented by a single-precision floating-point number, the
|
|
comparison is made with a single-precision form of the constant.</p>
|
|
<p>Two floating-point numbers are equal only if the bit configurations of
|
|
their normalized forms are identical.</p>
|
|
<p>When string and numeric data types are compared, the string is converted
|
|
to the numeric data type and must contain a valid string representation of
|
|
a number.</p>
|
|
<a name="wq192"></a>
|
|
<h3 id="wq192"><a href="rbafzmst02.htm#ToC_167">String comparisons</a></h3><a id="idx485" name="idx485"></a><a id="idx486" name="idx486"></a>
|
|
<a name="wq193"></a>
|
|
<h4 id="wq193">Binary string comparisons</h4>
|
|
<p>Binary string comparisons always use a sort sequence of *HEX and the corresponding
|
|
bytes of each string are compared. Additionally, two binary strings are equal
|
|
only if the lengths of the two strings are identical. If the strings are equal
|
|
up to the length of the shorter string length, the shorter string is considered
|
|
less than the longer string even when the remaining bytes in the longer string
|
|
are hexadecimal zeros. Note that binary strings cannot be compared to character
|
|
strings unless the character string is cast to a binary string.</p>
|
|
<a name="wq194"></a>
|
|
<h4 id="wq194">Character and graphic string comparisons</h4>
|
|
<p>Character and UTF-16 or UCS-2 graphic string comparisons use the sort sequence
|
|
in effect when the statement is executed for all SBCS data and the single-byte
|
|
portion of mixed data. If the sort sequence is *HEX, the corresponding bytes
|
|
of each string are compared. For all other sort sequences, the corresponding
|
|
bytes of the weighted value of each string are compared.</p>
|
|
<p>If the strings have different lengths, a temporary copy of the shorter
|
|
string is padded on the right with blanks before comparison. The padding makes
|
|
each string the same length. The pad character is always a blank, regardless
|
|
of the sort sequence. For bit data, the pad character is also a blank. For
|
|
DBCS graphic data, the pad character is a DBCS blank (x'4040'). For UTF-16
|
|
or UCS-2 graphic data, the pad character is a UTF-16 blank. <sup class="fn"><a id="wq195" name="wq195" href="rbafzmstch2bas.htm#wq196">24</a></sup></p>
|
|
<p>Two strings are equal if any of the following are true: </p>
|
|
<ul>
|
|
<li>Both strings are empty.</li>
|
|
<li>A *HEX sort sequence is used and all corresponding bytes are equal.</li>
|
|
<li>A sort sequence other than *HEX is used and all corresponding bytes of
|
|
the weighted value are equal.</li></ul><p class="indatacontent"> An empty string is equal to a blank string. The relationship between
|
|
two unequal strings is determined by a comparison of the first pair of unequal
|
|
bytes (or bytes of the weighted value) from the left end of the string. This
|
|
comparison is made according to the sort sequence in effect when the statement
|
|
is executed.</p>
|
|
<p>In an application that will run in multiple environments, the same sort
|
|
sequence (which depends on the CCSIDs of the environments) must be used to
|
|
ensure identical results. The following table illustrates the differences
|
|
between EBCDIC, ASCII, and the DB2 UDB LUW default sort sequence for United States
|
|
English by showing a list that is sorted according to each one.</p>
|
|
<a name="csdtabl"></a>
|
|
<table id="csdtabl" width="100%" summary="" border="0" frame="void" rules="none">
|
|
<caption>Table 17. Sort Sequence Differences</caption>
|
|
<thead valign="bottom">
|
|
<tr>
|
|
<th id="wq197" width="33%" align="left">ASCII and Unicode</th>
|
|
<th id="wq198" width="33%" align="left">EBCDIC</th>
|
|
<th id="wq199" width="33%" align="left">DB2 UDB LUW Default</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody valign="top">
|
|
<tr>
|
|
<td headers="wq197"><tt class="xph">0000</tt></td>
|
|
<td headers="wq198"><tt class="xph">@@@@</tt></td>
|
|
<td headers="wq199"><tt class="xph">0000</tt></td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq197"><tt class="xph">9999</tt></td>
|
|
<td headers="wq198"><tt class="xph">co-op</tt></td>
|
|
<td headers="wq199"><tt class="xph">9999</tt></td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq197"><tt class="xph">@@@@</tt></td>
|
|
<td headers="wq198"><tt class="xph">coop</tt></td>
|
|
<td headers="wq199"><tt class="xph">@@@@</tt></td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq197"><tt class="xph">COOP</tt></td>
|
|
<td headers="wq198"><tt class="xph">piano forte</tt></td>
|
|
<td headers="wq199"><tt class="xph">co-op</tt></td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq197"><tt class="xph">PIANO-FORTE</tt></td>
|
|
<td headers="wq198"><tt class="xph">piano-forte</tt></td>
|
|
<td headers="wq199"><tt class="xph">COOP</tt></td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq197"><tt class="xph">co-op</tt></td>
|
|
<td headers="wq198"><tt class="xph">COOP</tt></td>
|
|
<td headers="wq199"><tt class="xph">coop</tt></td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq197"><tt class="xph">coop</tt></td>
|
|
<td headers="wq198"><tt class="xph">PIANO-FORTE</tt></td>
|
|
<td headers="wq199"><tt class="xph">piano forte</tt></td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq197"><tt class="xph">piano forte</tt></td>
|
|
<td headers="wq198"><tt class="xph">0000</tt></td>
|
|
<td headers="wq199"><tt class="xph">PIANO-FORTE</tt></td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq197"><tt class="xph">piano-forte</tt></td>
|
|
<td headers="wq198"><tt class="xph">9999</tt></td>
|
|
<td headers="wq199"><tt class="xph">piano-forte</tt></td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
<p>Two varying-length strings with different lengths are equal if they differ
|
|
only in the number of trailing blanks. In operations that select one value
|
|
from a set of such values, the value selected is arbitrary. The operations
|
|
that can involve such an arbitrary selection are DISTINCT, MAX, MIN, UNION,
|
|
EXCEPT, INTERSECT, and references to a grouping column. See the description
|
|
of GROUP BY for further information about the arbitrary selection involved
|
|
in references to a grouping column.</p>
|
|
<a name="crcj"></a>
|
|
<h5 id="crcj">Conversion rules for comparison</h5><a id="idx487" name="idx487"></a>
|
|
<p>When two strings are compared, one of the strings is first converted, if
|
|
necessary, to the coded character set of the other string. Character conversion
|
|
is necessary only if all of the following are true: </p>
|
|
<ul>
|
|
<li>The CCSIDs of the two strings are different.</li>
|
|
<li>Neither CCSID is 65535.</li>
|
|
<li>The string selected for conversion is neither null nor empty.</li>
|
|
<li>The CCSID Conversion Selection Table (<a href="rbafzmstccseta.htm#conccsid">Coded character sets and CCSIDs</a>) indicates
|
|
that conversion is necessary.</li></ul>
|
|
<p>If two strings with different encoding schemes are compared,
|
|
any necessary conversion applies to the string as follows:</p>
|
|
<a name="wq200"></a>
|
|
<table id="wq200" width="100%" summary="" border="1" frame="border" rules="all">
|
|
<caption>Table 18. Selecting the Encoding Scheme for Character Conversion</caption>
|
|
<thead valign="bottom">
|
|
<tr>
|
|
<th id="wq201" width="33%" rowspan="2" align="left" valign="bottom">First Operand</th>
|
|
<th id="wq202" colspan="4" align="center" valign="bottom">Second
|
|
Operand</th>
|
|
</tr>
|
|
<tr>
|
|
<th id="wq203" width="16%" align="left" valign="bottom">SBCS Data</th>
|
|
<th id="wq204" width="16%" align="left" valign="bottom">DBCS Data</th>
|
|
<th id="wq205" width="16%" align="left" valign="bottom">Mixed Data</th>
|
|
<th id="wq206" width="16%" align="left" valign="bottom">UTF-16 or UCS-2 Data</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody valign="top">
|
|
<tr>
|
|
<td align="center" valign="top" headers="wq201">SBCS Data</td>
|
|
<td align="center" valign="top" headers="wq202 wq203">see below</td>
|
|
<td align="center" valign="top" headers="wq202 wq204">second</td>
|
|
<td align="center" valign="top" headers="wq202 wq205">second</td>
|
|
<td align="center" valign="top" headers="wq202 wq206">second</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="center" valign="top" headers="wq201">DBCS Data</td>
|
|
<td align="center" valign="top" headers="wq202 wq203">first</td>
|
|
<td align="center" valign="top" headers="wq202 wq204">see below</td>
|
|
<td align="center" valign="top" headers="wq202 wq205">second</td>
|
|
<td align="center" valign="top" headers="wq202 wq206">second</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="center" valign="top" headers="wq201">Mixed Data</td>
|
|
<td align="center" valign="top" headers="wq202 wq203">first</td>
|
|
<td align="center" valign="top" headers="wq202 wq204">first</td>
|
|
<td align="center" valign="top" headers="wq202 wq205">see below</td>
|
|
<td align="center" valign="top" headers="wq202 wq206">second</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="center" valign="top" headers="wq201">UTF-16 or UCS-2 Data</td>
|
|
<td align="center" valign="top" headers="wq202 wq203">first</td>
|
|
<td align="center" valign="top" headers="wq202 wq204">first</td>
|
|
<td align="center" valign="top" headers="wq202 wq205">first</td>
|
|
<td align="center" valign="top" headers="wq202 wq206">see below</td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
<p>Otherwise, the string selected for conversion depends on the type of each
|
|
operand. The following table shows which operand is selected for conversion,
|
|
given the operand types:</p>
|
|
<a name="wq207"></a>
|
|
<table id="wq207" width="100%" summary="" border="1" frame="border" rules="all">
|
|
<caption>Table 19. Selecting the Operand for Character Conversion</caption>
|
|
<thead valign="bottom">
|
|
<tr>
|
|
<th id="wq208" width="28%" rowspan="2" align="left" valign="bottom">First Operand</th>
|
|
<th id="wq209" colspan="5" align="center" valign="bottom">Second
|
|
Operand</th>
|
|
</tr>
|
|
<tr>
|
|
<th id="wq210" width="14%" align="left" valign="bottom">Column Value</th>
|
|
<th id="wq211" width="14%" align="left" valign="bottom">Derived Value</th>
|
|
<th id="wq212" width="14%" align="left" valign="bottom">Special Register</th>
|
|
<th id="wq213" width="14%" align="left" valign="bottom">Constant</th>
|
|
<th id="wq214" width="14%" align="left" valign="bottom">Variable</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody valign="top">
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq208">Column Value</td>
|
|
<td align="left" valign="top" headers="wq209 wq210">second</td>
|
|
<td align="left" valign="top" headers="wq209 wq211">second</td>
|
|
<td align="left" valign="top" headers="wq209 wq212">second</td>
|
|
<td align="left" valign="top" headers="wq209 wq213">second</td>
|
|
<td align="left" valign="top" headers="wq209 wq214">second</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq208">Derived Value</td>
|
|
<td align="left" valign="top" headers="wq209 wq210">first</td>
|
|
<td align="left" valign="top" headers="wq209 wq211">second</td>
|
|
<td align="left" valign="top" headers="wq209 wq212">second</td>
|
|
<td align="left" valign="top" headers="wq209 wq213">second</td>
|
|
<td align="left" valign="top" headers="wq209 wq214">second</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq208">Special Register</td>
|
|
<td align="left" valign="top" headers="wq209 wq210">first</td>
|
|
<td align="left" valign="top" headers="wq209 wq211">first</td>
|
|
<td align="left" valign="top" headers="wq209 wq212">second</td>
|
|
<td align="left" valign="top" headers="wq209 wq213">second</td>
|
|
<td align="left" valign="top" headers="wq209 wq214">second</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq208">Constant</td>
|
|
<td align="left" valign="top" headers="wq209 wq210">first</td>
|
|
<td align="left" valign="top" headers="wq209 wq211">first</td>
|
|
<td align="left" valign="top" headers="wq209 wq212">first</td>
|
|
<td align="left" valign="top" headers="wq209 wq213">second</td>
|
|
<td align="left" valign="top" headers="wq209 wq214">second</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq208">Variable</td>
|
|
<td align="left" valign="top" headers="wq209 wq210">first</td>
|
|
<td align="left" valign="top" headers="wq209 wq211">first</td>
|
|
<td align="left" valign="top" headers="wq209 wq212">first</td>
|
|
<td align="left" valign="top" headers="wq209 wq213">first</td>
|
|
<td align="left" valign="top" headers="wq209 wq214">second</td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
<p>A variable that contains data in a foreign encoding scheme is always effectively
|
|
converted to the native encoding scheme before it is used in any operation.
|
|
The above rules are based on the assumption that this conversion has already
|
|
occurred.</p>
|
|
<p>An error is returned if a character of the string cannot be converted or
|
|
the CCSID Conversion Selection Table (<a href="rbafzmstccseta.htm#conccsid">Coded character sets and CCSIDs</a>) is used but
|
|
does not contain any information about the pair of CCSIDs. A warning occurs
|
|
if a character of the string is converted to the substitution character.</p>
|
|
<a name="wq215"></a>
|
|
<h3 id="wq215"><a href="rbafzmst02.htm#ToC_171">Datetime comparisons</a></h3><a id="idx488" name="idx488"></a><a id="idx489" name="idx489"></a>
|
|
<p>A DATE, TIME, or TIMESTAMP value can be compared either with another value
|
|
of the same data type or with a string representation of that data type. All
|
|
comparisons are chronological, which means the farther a point in time is
|
|
from January 1, 0001, the <span class="italic">greater</span> the value of that
|
|
point in time.</p>
|
|
<p>Comparisons involving TIME values and string representations of time values
|
|
always include seconds. If the string representation omits seconds, zero seconds
|
|
are implied. The time 24:00:00 compares greater than the time 00:00:00.</p>
|
|
<p>Comparisons involving TIMESTAMP values are chronological without regard
|
|
to representations that might be considered equivalent. Thus, the following
|
|
predicate is true: </p>
|
|
<a name="r3tmstp"></a>
|
|
<pre id="r3tmstp" class="xmp"> <span class="bold">TIMESTAMP</span>('1990-02-23-00.00.00') > '1990-02-22-24.00.00'</pre>
|
|
<a name="wq216"></a>
|
|
<h3 id="wq216"><a href="rbafzmst02.htm#ToC_172">DataLink comparisons</a></h3><a id="idx490" name="idx490"></a><a id="idx491" name="idx491"></a>
|
|
<p>A DATALINK operand cannot be directly compared to any data type. The DLCOMMENT,
|
|
DLLINKTYPE, DLURLCOMPLETE, DLURLPATH, DLURLPATHONLY, DLURLSCHEME, and DLURLSERVER
|
|
scalar functions can be used to extract character string values from a datalink
|
|
which can then be compared to other strings.</p>
|
|
<a name="wq217"></a>
|
|
<h3 id="wq217"><a href="rbafzmst02.htm#ToC_173">Row ID comparisons</a></h3><a id="idx492" name="idx492"></a><a id="idx493" name="idx493"></a>
|
|
<p>A ROWID operand cannot be directly compared to any data type. To compare
|
|
the bit representation of a ROWID, first cast the ROWID to a character string.</p>
|
|
<a name="wq218"></a>
|
|
<h3 id="wq218"><a href="rbafzmst02.htm#ToC_174">Distinct type comparisons</a></h3><a id="idx494" name="idx494"></a><a id="idx495" name="idx495"></a>
|
|
<p>A value with a distinct type can be compared only to another value with
|
|
exactly the same distinct type.</p>
|
|
<p>For example, assume that distinct type YOUTH and table CAMP_DB2_ROSTER
|
|
table were created with the following SQL statements: </p>
|
|
<pre class="xmp"> <span class="bold">CREATE DISTINCT TYPE</span> YOUTH <span class="bold">AS INTEGER WITH COMPARISONS</span>
|
|
|
|
<span class="bold">CREATE TABLE</span> CAMP_DB2_ROSTER
|
|
<span class="bold">(</span> NAME <span class="bold">VARCHAR(</span>20<span class="bold">),</span>
|
|
ATTENDEE_NUMBER <span class="bold">INTEGER NOT NULL,</span>
|
|
AGE YOUTH<span class="bold">,</span>
|
|
HIGH_SCHOOL_LEVEL YOUTH<span class="bold">)</span></pre>
|
|
<p>The following comparison is valid because AGE and HIGH_SCHOOL_LEVEL have
|
|
the same distinct type:</p>
|
|
<pre class="xmp"> <span class="bold">SELECT * FROM</span> CAMP_DB2_ROSTER
|
|
<span class="bold">WHERE</span> AGE <span class="bold">></span> HIGH_SCHOOL_LEVEL </pre>
|
|
<p>The following comparison is not valid:</p>
|
|
<pre class="xmp"> <span class="bold">SELECT * FROM</span> CAMP_DB2_ROSTER ***INCORRECT***
|
|
<span class="bold">WHERE</span> AGE <span class="bold">></span> ATTENDEE_NUMBER</pre>
|
|
<p>However, AGE can be compared to ATTENDEE_NUMBER by using a cast function
|
|
or CAST specification to cast between the distinct type and the source type.
|
|
All of the following comparisons are valid: </p>
|
|
<pre class="xmp"> <span class="bold">SELECT * FROM</span> CAMP_DB2_ROSTER
|
|
<span class="bold">WHERE</span> AGE <span class="bold">></span> YOUTH<span class="bold">(</span>ATTENDEE_NUMBER<span class="bold">)</span>
|
|
|
|
<span class="bold">SELECT * FROM</span> CAMP_DB2_ROSTER
|
|
<span class="bold">WHERE</span> AGE <span class="bold">> CAST(</span> ATTENDEE_NUMBER <span class="bold">AS</span> YOUTH<span class="bold">)</span>
|
|
|
|
<span class="bold">SELECT * FROM</span> CAMP_DB2_ROSTER
|
|
<span class="bold">WHERE INTEGER(</span>AGE<span class="bold">) ></span> ATTENDEE_NUMBER
|
|
|
|
<span class="bold">SELECT * FROM</span> CAMP_DB2_ROSTER
|
|
<span class="bold">WHERE CAST(</span>AGE <span class="bold">AS INTEGER) ></span> ATTENDEE_NUMBER
|
|
</pre><a id="idx496" name="idx496"></a>
|
|
<hr /><div class="fnnum"><a id="fn13" name="fn13">22</a>.</div>
|
|
<div class="fntext">UTF-16 or UCS-2 defines a blank character at code point X'0020'
|
|
and X'3000'. The database manager pads with the blank at code point X'0020'.
|
|
The database manager pads UTF-8 with a blank at code point X'20'</div><div class="fnnum"><a id="wq182" name="wq182" href="rbafzmstch2bas.htm#wq181">23</a>.</div>
|
|
<div class="fntext">It may be necessary to access the file server to determine the prefix
|
|
name associated with a path. This can be changed at the file server when the
|
|
mount point of a file system is moved. First access of a file on a server
|
|
will cause the required values to be retrieved from the file server and cached
|
|
at the database server for the subsequent retrieval of DataLink values for
|
|
that file server. An error is returned if the file server cannot be accessed.</div><div class="fnnum"><a id="wq196" name="wq196" href="rbafzmstch2bas.htm#wq195">24</a>.</div>
|
|
<div class="fntext">UTF-16 defines
|
|
a blank character at code point X'0020' and X'3000'. The database manager
|
|
pads with the blank at code point X'0020'.</div>
|
|
<br />
|
|
<hr /><br />
|
|
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmstcastdt.htm">Previous Page</a> | <a href="rbafzmstopcomb.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>
|