friedkiwi/ibm-information-center
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
master
ibm-information-center/dist/eclipse/plugins/i5OS.ic.db2_5.4.0.1/rbafzmstopcomb.htm

441 lines
20 KiB
HTML
Raw Permalink Blame History

<?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))' />
<title>Rules for result data types</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="opcomb"></a>
<h2 id="opcomb"><a href="rbafzmst02.htm#ToC_175">Rules for result data types</a></h2>
<p>The data types of a result are determined by rules which are applied to
the operands in an operation. This section explains those rules.</p>
<p>These rules apply to:</p>
<ul>
<li>Corresponding columns in UNION, UNION ALL, EXCEPT, or INTERSECT operations</li>
<li>Result expressions of a CASE expression</li>
<li>Arguments of the scalar functions COALESCE, IFNULL, MAX, MIN, and VALUE</li>
<li>Expression values of the IN list of an IN predicate</li></ul><p class="indatacontent"> For the result data type of expressions that involve the operators /,
*, + and -, see <a href="rbafzmstch2expr.htm#ch2expr">Expressions</a>. For the result data type of expressions
that involve the CONCAT operator, see <a href="rbafzmstch2expr.htm#wtco">With the concatenation operator</a>.</p>
<p>The data type of the result is determined by the data type of the operands.
The data types of the first two operands determine an intermediate result
data type, this data type and the data type of the next operand determine
a new intermediate result data type, and so on. The last intermediate result
data type and the data type of the last operand determine the data type of
the result. For each pair of data types, the result data type is determined
by the sequential application of the rules summarized in the tables that follow.</p>
<p>If neither operand column allows nulls, the result does not allow nulls.
Otherwise, the result allows nulls.</p>
<p>If the data type and attributes of any operand column are not the same
as those of the result, the operand column values are converted to conform
to the data type and attributes of the result. The conversion operation is
exactly the same as if the values were assigned to the result. For example, </p>
<ul>
<li>If one operand column is CHAR(10), and the other operand column is CHAR(5),
the result is CHAR(10), and the values derived from the CHAR(5) column are
padded on the right with five blanks.</li>
<li>If the whole part of a number cannot be preserved then an error is returned.</li></ul>
<a name="wq219"></a>
<h3 id="wq219"><a href="rbafzmst02.htm#ToC_176">Numeric operands</a></h3>
<p>Numeric types are compatible with other numeric and character-string and
graphic-string data types.</p>
<a name="wq220"></a>
<table id="wq220" width="100%" summary="" border="1" frame="hsides" rules="rows">
<thead valign="bottom">
<tr>
<th id="wq221" width="25%" align="left" valign="bottom">If one operand column is...</th>
<th id="wq222" width="25%" align="left" valign="bottom">And the other operand is...</th>
<th id="wq223" width="50%" align="left" valign="bottom">The data type of the result column is...</th>
</tr>
</thead>
<tbody valign="top">
<tr>
<td align="left" valign="top" headers="wq221">SMALLINT</td>
<td align="left" valign="top" headers="wq222">SMALLINT</td>
<td align="left" valign="top" headers="wq223">SMALLINT</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq221">SMALLINT</td>
<td align="left" valign="top" headers="wq222">String</td>
<td align="left" valign="top" headers="wq223">INTEGER</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq221">INTEGER</td>
<td align="left" valign="top" headers="wq222">SMALLINT</td>
<td align="left" valign="top" headers="wq223">INTEGER</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq221">INTEGER</td>
<td align="left" valign="top" headers="wq222">INTEGER</td>
<td align="left" valign="top" headers="wq223">INTEGER</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq221">INTEGER</td>
<td align="left" valign="top" headers="wq222">String</td>
<td align="left" valign="top" headers="wq223">INTEGER</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq221">BIGINT</td>
<td align="left" valign="top" headers="wq222">SMALLINT</td>
<td align="left" valign="top" headers="wq223">BIGINT</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq221">BIGINT</td>
<td align="left" valign="top" headers="wq222">INTEGER</td>
<td align="left" valign="top" headers="wq223">BIGINT</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq221">BIGINT</td>
<td align="left" valign="top" headers="wq222">BIGINT</td>
<td align="left" valign="top" headers="wq223">BIGINT</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq221">BIGINT</td>
<td align="left" valign="top" headers="wq222">String</td>
<td align="left" valign="top" headers="wq223">BIGINT</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq221">DECIMAL(w,x)</td>
<td align="left" valign="top" headers="wq222">SMALLINT</td>
<td align="left" valign="top" headers="wq223">
<div class="lines">DECIMAL(p,x) where<br />
p = min(mp, x+max(w-x,5))<br />
mp = 31 or 63 (See Note 1)<br />
</div></td>
</tr>
<tr>
<td align="left" valign="top" headers="wq221">DECIMAL(w,x)</td>
<td align="left" valign="top" headers="wq222">INTEGER</td>
<td align="left" valign="top" headers="wq223">
<div class="lines">DECIMAL(p,x) where <br />
p = min(mp, x+max(w-x,11))<br />
mp = 31 or 63 (See Note 1)<br />
</div></td>
</tr>
<tr>
<td align="left" valign="top" headers="wq221">DECIMAL(w,x)</td>
<td align="left" valign="top" headers="wq222">BIGINT</td>
<td align="left" valign="top" headers="wq223">
<div class="lines">DECIMAL(p,x) where <br />
p = min(mp, x+max(w-x,19))<br />
mp = 31 or 63 (See Note 1)<br />
</div></td>
</tr>
<tr>
<td align="left" valign="top" headers="wq221">DECIMAL(w,x)</td>
<td align="left" valign="top" headers="wq222">DECIMAL(y,z) or NUMERIC(y,z,)</td>
<td align="left" valign="top" headers="wq223">
<div class="lines">DECIMAL(p,s) where <br />
p = min(mp, max(x,z)+max(w-x,y-z)) <br />
s = max(x,z)<br />
mp = 31 or 63 (See Note 1)<br />
</div></td>
</tr>
<tr>
<td align="left" valign="top" headers="wq221">DECIMAL(w,x)</td>
<td align="left" valign="top" headers="wq222">String</td>
<td align="left" valign="top" headers="wq223">
<div class="lines">DECIMAL(w,x)<br />
</div></td>
</tr>
<tr>
<td align="left" valign="top" headers="wq221">NUMERIC(w,x)</td>
<td align="left" valign="top" headers="wq222">SMALLINT</td>
<td align="left" valign="top" headers="wq223">
<div class="lines">NUMERIC(p,x) where <br />
p = min(mp, x + max(w-x,5))<br />
mp = 31 or 63 (See Note 1)<br />
</div></td>
</tr>
<tr>
<td align="left" valign="top" headers="wq221">NUMERIC(w,x)</td>
<td align="left" valign="top" headers="wq222">INTEGER</td>
<td align="left" valign="top" headers="wq223">
<div class="lines">NUMERIC(p,x) where <br />
p = min(mp, x + max(w-x,11))<br />
mp = 31 or 63 (See Note 1)<br />
</div></td>
</tr>
<tr>
<td align="left" valign="top" headers="wq221">NUMERIC(w,x)</td>
<td align="left" valign="top" headers="wq222">BIGINT</td>
<td align="left" valign="top" headers="wq223">
<div class="lines">NUMERIC(p,x) where <br />
p = min(mp, x + max(w-x,19))<br />
mp = 31 or 63 (See Note 1)<br />
</div></td>
</tr>
<tr>
<td align="left" valign="top" headers="wq221">NUMERIC(w,x)</td>
<td align="left" valign="top" headers="wq222">NUMERIC(y,z)</td>
<td align="left" valign="top" headers="wq223">
<div class="lines">NUMERIC(p,s) where <br />
p = min(mp, max(x,z) + max(w-x, y-z)) <br />
s = max(x,z)<br />
mp = 31 or 63 (See Note 1)<br />
</div></td>
</tr>
<tr>
<td align="left" valign="top" headers="wq221">NUMERIC(w,x)</td>
<td align="left" valign="top" headers="wq222">String</td>
<td align="left" valign="top" headers="wq223">
<div class="lines">NUMERIC(w,x)<br />
</div></td>
</tr>
<tr>
<td align="left" valign="top" headers="wq221">NONZERO SCALE BINARY</td>
<td align="left" valign="top" headers="wq222">NONZERO SCALE BINARY</td>
<td align="left" valign="top" headers="wq223">
<div class="lines">NONZERO SCALE BINARY <br />
(If either operand is nonzero scale binary, <br />
both operands must be binary with the <br />
same scale.)<br />
</div></td>
</tr>
<tr>
<td align="left" valign="top" headers="wq221">REAL</td>
<td align="left" valign="top" headers="wq222">REAL</td>
<td align="left" valign="top" headers="wq223">REAL</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq221">REAL</td>
<td align="left" valign="top" headers="wq222">DECIMAL, NUMERIC, BIGINT, INTEGER, or SMALLINT</td>
<td align="left" valign="top" headers="wq223">DOUBLE</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq221">REAL</td>
<td align="left" valign="top" headers="wq222">String</td>
<td align="left" valign="top" headers="wq223">DOUBLE</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq221">DOUBLE</td>
<td align="left" valign="top" headers="wq222">any numeric type</td>
<td align="left" valign="top" headers="wq223">DOUBLE</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq221">DOUBLE</td>
<td align="left" valign="top" headers="wq222">String</td>
<td align="left" valign="top" headers="wq223">DOUBLE</td>
</tr>
<tr>
<td colspan="3" align="left" valign="top" headers="wq221 wq222 wq223">
<a name="wq224"></a>
<div class="notetitle" id="wq224">Notes:</div>
<div class="notebody">
<ol type="1">
<li>The value of mp is 63 if:
<ul>
<li>either w or y is greater than 31, or</li>
<li>a value of 63 was specified for the maximum precision on the DECRESULT
parameter of the CRTSQLxxx command, RUNSQLSTM command, or SET OPTION statement</li></ul>Otherwise, the value of mp is 31.</li></ol></div></td>
</tr>
</tbody>
</table>
<a name="wq226"></a>
<h3 id="wq226"><a href="rbafzmst02.htm#ToC_177">Character and graphic string operands</a></h3>
<p>Character and graphic strings are compatible with other character and graphic
strings when there is a defined conversion between their corresponding CCSIDs.</p>
<a name="wq227"></a>
<table id="wq227" width="100%" summary="" border="1" frame="hsides" rules="rows">
<thead valign="bottom">
<tr>
<th id="wq228" width="25%" align="left" valign="bottom">If one operand column is...</th>
<th id="wq229" width="25%" align="left" valign="bottom">And the other operand is...</th>
<th id="wq230" width="50%" align="left" valign="bottom">The data type of the result column is...</th>
</tr>
</thead>
<tbody valign="top">
<tr>
<td align="left" valign="top" headers="wq228">CHAR(x)</td>
<td align="left" valign="top" headers="wq229">CHAR(y)</td>
<td align="left" valign="top" headers="wq230">CHAR(z) where z = max(x,y)</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq228">GRAPHIC(x)</td>
<td align="left" valign="top" headers="wq229">GRAPHIC(y) or CHAR(y)</td>
<td align="left" valign="top" headers="wq230">GRAPHIC(z) where z = max(x,y)</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq228">VARCHAR(x)</td>
<td align="left" valign="top" headers="wq229">VARCHAR(y) or CHAR(y)</td>
<td align="left" valign="top" headers="wq230">VARCHAR(z) where z = max(x,y)</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq228">VARCHAR(x)</td>
<td align="left" valign="top" headers="wq229">GRAPHIC(y)</td>
<td align="left" valign="top" headers="wq230">VARGRAPHIC(z) where z = max(x,y)</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq228">VARGRAPHIC(x)</td>
<td align="left" valign="top" headers="wq229">VARGRAPHIC(y) or GRAPHIC(y) or VARCHAR(y)
or CHAR(y)</td>
<td align="left" valign="top" headers="wq230">VARGRAPHIC(z) where z = max(x,y)</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq228">CLOB(x)</td>
<td align="left" valign="top" headers="wq229">CLOB(y) or VARCHAR(y) or CHAR(y)</td>
<td align="left" valign="top" headers="wq230">CLOB(z) where z = max(x,y)</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq228">CLOB(x)</td>
<td align="left" valign="top" headers="wq229">GRAPHIC(y) or VARGRAPHIC(y)</td>
<td align="left" valign="top" headers="wq230">DBCLOB(z) where z = max(x,y)</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq228">DBCLOB(x)</td>
<td align="left" valign="top" headers="wq229">CHAR(y) or VARCHAR(y) or CLOB(y) or GRAPHIC(y)
or VARGRAPHIC(y) or DBCLOB(y)</td>
<td align="left" valign="top" headers="wq230">DBCLOB(z) where z = max(x,y)</td>
</tr>
</tbody>
</table>
<p>The CCSID of the result graphic string will be derived based on the <a href="rbafzmstuuall.htm#uuall">Conversion rules for operations that combine strings</a>.</p>
<a name="wq231"></a>
<h3 id="wq231"><a href="rbafzmst02.htm#ToC_178">Binary string operands</a></h3>
<p>Binary strings are compatible only with other binary strings. Other data
types can be treated as a binary-string data type by using the BINARY, VARBINARY,
or BLOB scalar functions to cast the data type to a binary string.</p>
<a name="wq232"></a>
<table id="wq232" width="100%" summary="" border="1" frame="hsides" rules="rows">
<thead valign="bottom">
<tr>
<th id="wq233" width="25%" align="left" valign="bottom">If one operand column is...</th>
<th id="wq234" width="25%" align="left" valign="bottom">And the other operand is...</th>
<th id="wq235" width="50%" align="left" valign="bottom">The data type of the result column is...</th>
</tr>
</thead>
<tbody valign="top">
<tr>
<td align="left" valign="top" headers="wq233">BINARY(x)</td>
<td align="left" valign="top" headers="wq234">BINARY(y)</td>
<td align="left" valign="top" headers="wq235">BINARY(z) where z = max(x,y)</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq233">VARBINARY(x)</td>
<td align="left" valign="top" headers="wq234">VARBINARY(y) or BINARY(y)</td>
<td align="left" valign="top" headers="wq235">VARBINARY(z) where z = max(x,y)</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq233">BLOB(x)</td>
<td align="left" valign="top" headers="wq234">BLOB(y) or VARBINARY(y) or BINARY(y)</td>
<td align="left" valign="top" headers="wq235">BLOB(z) where z = max(x,y)</td>
</tr>
</tbody>
</table>
<a name="wq236"></a>
<h3 id="wq236"><a href="rbafzmst02.htm#ToC_179">Datetime operands</a></h3>
<p>A DATE type is compatible with another DATE type or any character string
expression that contains a valid string representation of a date. A string
representation must not be a CLOB. The data type of the result is DATE.</p>
<p>A TIME type is compatible with another TIME type or any character string
expression that contains a valid string representation of a time. A string
representation must not be a CLOB. The data type of the result is TIME.</p>
<p>A TIMESTAMP type is compatible with another TIMESTAMP type or any character
string expression that contains a valid string representation of a timestamp.
A string representation must not be a CLOB. The data type of the result is
TIMESTAMP.</p>
<a name="wq237"></a>
<table id="wq237" width="100%" summary="" border="1" frame="hsides" rules="rows">
<thead valign="bottom">
<tr>
<th id="wq238" width="25%" align="left" valign="bottom">If one operand column is...</th>
<th id="wq239" width="25%" align="left" valign="bottom">And the other operand is...</th>
<th id="wq240" width="50%" align="left" valign="bottom">The data type of the result column is...</th>
</tr>
</thead>
<tbody valign="top">
<tr>
<td align="left" valign="top" headers="wq238">DATE</td>
<td valign="top" headers="wq239">DATE, CHAR(y), or VARCHAR(y)</td>
<td align="left" valign="top" headers="wq240">DATE</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq238">TIME</td>
<td align="left" valign="top" headers="wq239">TIME, CHAR(y), or VARCHAR(y)</td>
<td align="left" valign="top" headers="wq240">TIME</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq238">TIMESTAMP</td>
<td align="left" valign="top" headers="wq239">TIMESTAMP, CHAR(y), or VARCHAR(y)</td>
<td align="left" valign="top" headers="wq240">TIMESTAMP</td>
</tr>
</tbody>
</table>
<a name="wq241"></a>
<h3 id="wq241"><a href="rbafzmst02.htm#ToC_180">DataLink operands</a></h3>
<p>A DataLink is compatible with another DataLink. However, DataLinks with
NO LINK CONTROL are only compatible with other DataLinks with NO LINK CONTROL;
DataLinks with FILE LINK CONTROL READ PERMISSION FS are only compatible with
other DataLinks with FILE LINK CONTROL READ PERMISSION FS; and DataLinks with
FILE LINK CONTROL READ PERMISSION DB are only compatible with other DataLinks
with FILE LINK CONTROL READ PERMISSION DB. The data type of the result is
DATALINK. The length of the result DATALINK is the largest length of all the
data types.</p>
<a name="wq242"></a>
<table id="wq242" width="100%" summary="" border="1" frame="hsides" rules="rows">
<thead valign="bottom">
<tr>
<th id="wq243" width="25%" align="left" valign="bottom">If one operand column is...</th>
<th id="wq244" width="25%" align="left" valign="bottom">And the other operand is...</th>
<th id="wq245" width="50%" align="left" valign="bottom">The data type of the result column is...</th>
</tr>
</thead>
<tbody valign="top">
<tr>
<td align="left" valign="top" headers="wq243">DATALINK(x)</td>
<td align="left" valign="top" headers="wq244">DATALINK(y)</td>
<td align="left" valign="top" headers="wq245">DATALINK(z) where z = max(x,y)</td>
</tr>
</tbody>
</table>
<a name="wq246"></a>
<h3 id="wq246"><a href="rbafzmst02.htm#ToC_181">ROWID operands</a></h3>
<p>A ROWID is compatible with another ROWID. The data type of the result is
ROWID.</p>
<a name="wq247"></a>
<h3 id="wq247"><a href="rbafzmst02.htm#ToC_182">Distinct type operands</a></h3>
<p>A user-defined distinct type is compatible only with the same user-defined
distinct type. The data type of the result is the user-defined distinct type.</p>
<a name="wq248"></a>
<table id="wq248" width="100%" summary="" border="1" frame="hsides" rules="rows">
<thead valign="bottom">
<tr>
<th id="wq249" width="25%" align="left" valign="bottom">If one operand column is...</th>
<th id="wq250" width="25%" align="left" valign="bottom">And the other operand is...</th>
<th id="wq251" width="50%" align="left" valign="bottom">The data type of the result column is...</th>
</tr>
</thead>
<tbody valign="top">
<tr>
<td align="left" valign="top" headers="wq249">Distinct Type</td>
<td align="left" valign="top" headers="wq250">Distinct Type</td>
<td align="left" valign="top" headers="wq251">Distinct Type</td>
</tr>
</tbody>
</table>
<hr /><br />
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmstch2bas.htm">Previous Page</a> | <a href="rbafzmstuuall.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>
Reference in New Issue View Git Blame Copy Permalink