324 lines
20 KiB
HTML
324 lines
20 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="constants, SQL (Structured Query Language),
|
|
literals, integer, integer constants, floating point, decimal,
|
|
character data string, character string, character, constant, string,
|
|
' (apostrophe), string delimiter, hexadecimal constants, hexadecimal,
|
|
graphic string, graphic, graphic constant, UTF-16 graphic constant, UTF-16,
|
|
UCS-2 graphic constant, UCS-2, binary data string, binary string, binary,
|
|
datetime, decimal point, default date format, date and time,
|
|
*APOSTSQL precompiler option, *QUOTESQL precompiler option,
|
|
*APOST precompiler option, *QUOTE precompiler option" />
|
|
<title>Constants</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="ch2cons"></a>
|
|
<h2 id="ch2cons"><a href="rbafzmst02.htm#ToC_184">Constants</a></h2><a id="idx497" name="idx497"></a><a id="idx498" name="idx498"></a>
|
|
<p>A <span class="italic">constant</span> (also called a <span class="italic">literal</span>) specifies a value. Constants are classified as string constants or
|
|
numeric constants. String constants are further classified as character or
|
|
graphic. Numeric constants are further classified as integer, floating point,
|
|
or decimal.</p>
|
|
<p>All constants have the attribute NOT NULL. A negative sign in a numeric
|
|
constant with a value of zero is ignored.</p>
|
|
<a name="wq270"></a>
|
|
<h3 id="wq270"><a href="rbafzmst02.htm#ToC_185">Integer constants</a></h3><a id="idx499" name="idx499"></a><a id="idx500" name="idx500"></a>
|
|
<p>An <span class="italic">integer constant</span> specifies an integer as a signed
|
|
or unsigned number with a maximum of 19 digits that does not include a decimal
|
|
point. The data type of an integer constant is large integer if its value
|
|
is within the range of a large integer. The data type of an integer constant
|
|
is big integer if its value is outside the range of a large integer, but within
|
|
the range of a big integer. A constant that is defined outside the range of
|
|
big integer values is considered a decimal constant.</p>
|
|
<a name="wq271"></a>
|
|
<h4 id="wq271">Examples</h4>
|
|
<pre class="xmp">64 -15 +100 32767 720176 12345678901</pre>
|
|
<p>In syntax diagrams, the term <span class="italic">integer</span> is used for
|
|
a large integer constant that must not include a sign.</p>
|
|
<a name="wq272"></a>
|
|
<h3 id="wq272"><a href="rbafzmst02.htm#ToC_187">Floating-point constants</a></h3><a id="idx501" name="idx501"></a><a id="idx502" name="idx502"></a>
|
|
<p>A <span class="italic">floating-point constant</span> specifies a double-precision
|
|
floating-point number as two numbers separated by an E. The first number can
|
|
include a sign and a decimal point; the second number can include a sign but
|
|
not a decimal point. The value of the constant is the product of the first
|
|
number and the power of 10 specified by the second number; it must be within
|
|
the range of floating-point numbers. The number of characters in the constant
|
|
must not exceed 24. Excluding leading zeros, the number of digits in the first
|
|
number must not exceed 17 and the number of digits in the second must not
|
|
exceed 3.</p>
|
|
<a name="wq273"></a>
|
|
<h4 id="wq273">Examples</h4>
|
|
<pre class="xmp">15E1 2.E5 2.2E-1 +5.E+2</pre>
|
|
<a name="wq274"></a>
|
|
<h3 id="wq274"><a href="rbafzmst02.htm#ToC_189">Decimal constants</a></h3><a id="idx503" name="idx503"></a><a id="idx504" name="idx504"></a>
|
|
<p>A <span class="italic">decimal constant</span> specifies a decimal number as
|
|
a signed or unsigned number that consists of no more than 63 digits. The constant
|
|
must either: </p>
|
|
<ul>
|
|
<li>Include a decimal point, or</li>
|
|
<li>Be larger than 2147483647 or smaller than -2147483647</li></ul><p class="indatacontent"> The precision is the total number of digits (including leading and trailing
|
|
zeros); the scale is the number of digits to the right of the decimal point
|
|
(including trailing zeros).</p>
|
|
<p>If the precision of the decimal constant is greater than the largest decimal
|
|
precision and the scale is not greater than the largest decimal precision,
|
|
then leading zeroes to the left of the decimal point are eliminated to reduce
|
|
the precision to the largest decimal precision.</p>
|
|
<a name="wq275"></a>
|
|
<h4 id="wq275">Examples</h4>
|
|
<pre class="xmp">25.5 1000. -15. +37589.3333333333 12345678901</pre>
|
|
<a name="wq276"></a>
|
|
<h3 id="wq276"><a href="rbafzmst02.htm#ToC_191">Character-string constants</a></h3><a id="idx505" name="idx505"></a><a id="idx506" name="idx506"></a><a id="idx507" name="idx507"></a><a id="idx508" name="idx508"></a><a id="idx509" name="idx509"></a><a id="idx510" name="idx510"></a><a id="idx511" name="idx511"></a><a id="idx512" name="idx512"></a>
|
|
<p>A <span class="italic">character-string constant</span> specifies a varying-length
|
|
character string. The two forms of character-string constant follow: </p>
|
|
<ul>
|
|
<li>A sequence of characters that starts and ends with a string delimiter.
|
|
The number of bytes between the string delimiters cannot be greater than 32740.
|
|
Two consecutive string delimiters are used to represent one string delimiter
|
|
within the character string. Two consecutive string delimiters that are not
|
|
contained within a string represent the empty string.</li>
|
|
<li>An X followed by a sequence of characters that starts and ends
|
|
with a string delimiter. The characters between the string delimiters must
|
|
be an even number of hexadecimal digits. Blanks between the string delimiters
|
|
are ignored. The number of hexadecimal digits must not exceed 32762. A hexadecimal
|
|
digit is a digit or any of the letters A through F (uppercase or lowercase).
|
|
Under the conventions of hexadecimal notation, each pair of hexadecimal digits
|
|
represents a character. This form of string constant allows you to specify
|
|
characters that do not have a keyboard representation.</li></ul>
|
|
<p>Character-string constants can contain mixed data. If the job CCSID supports
|
|
mixed data, a character-string constant is classified as mixed data if it
|
|
includes a DBCS substring. In all other cases, a character-string constant
|
|
is classified as SBCS data.</p>
|
|
<p>The CCSID assigned to the constant is the CCSID of the source containing
|
|
the constant unless the source is encoded in a foreign encoding scheme (such
|
|
as ASCII). The data in the variable is converted from the foreign encoding
|
|
scheme to the default CCSID of the current server. In this case, the CCSID
|
|
assigned to the constant is the default CCSID of the current server.</p>
|
|
<p>The CCSID of the source is determined by the application requester. The
|
|
CCSID of the source is: </p>
|
|
<ul>
|
|
<li>For STRSQL, the default CCSID of the application requester</li>
|
|
<li>For the RUNSQLSTM or STRREXPRC commands, the CCSID of the specified source
|
|
file</li>
|
|
<li>For CRTSQLxxx:
|
|
<ul>
|
|
<li>For static SQL, the CCSID of the source is the CCSID of the source file
|
|
used on the CRTSQLxxx command.</li>
|
|
<li>For dynamic SQL, the CCSID of the source is the CCSID of the variable
|
|
specified on the PREPARE statement, or if a string constant is specified on
|
|
the PREPARE statement, the default CCSID of the current server.</li></ul></li></ul>
|
|
<p>Character-string constants are used to represent constant datetime values
|
|
in assignments and comparisons. For more information see <a href="rbafzmstch2data.htm#dtstrng">String representations of datetime values</a>.</p>
|
|
<a name="wq277"></a>
|
|
<h4 id="wq277">Examples</h4>
|
|
<pre class="xmp"> 'Peggy' '14.12.1990' '32' 'DON''T CHANGE' '' X'FFFF'</pre>
|
|
<a name="wq278"></a>
|
|
<h3 id="wq278"><a href="rbafzmst02.htm#ToC_193">Graphic-string constants</a></h3>
|
|
<a name="wq279"></a>
|
|
<h4 id="wq279">DBCS graphic-string constants</h4><a id="idx513" name="idx513"></a><a id="idx514" name="idx514"></a><a id="idx515" name="idx515"></a><a id="idx516" name="idx516"></a>
|
|
<p>A <span class="italic">graphic-string constant</span> is a varying-length graphic
|
|
string. The length of the specified string cannot be greater than 16370. The
|
|
three forms of DBCS graphic-string constants are:</p>
|
|
<a name="wq280"></a>
|
|
<div class="fignone" id="wq280">
|
|
<div class="mmobj">
|
|
<img src="rv3f000.gif" alt="The three forms of DBCS graphic-string constants. Graphic described in text." /></div></div>
|
|
<p>In the normal form, the SQL delimiters and the G or the N are SBCS characters.
|
|
The SBCS ' is the EBCDIC apostrophe, X'7D'.</p>
|
|
<p>In the PL/I form, the apostrophes and the G are DBCS characters. Two consecutive
|
|
DBCS string delimiters are used to represent one string delimiter within the
|
|
string. Note that this PL/I form is only valid for static statements embedded
|
|
in PL/I programs.</p>
|
|
<p>A hexadecimal DBCS graphic constant is also supported. The form of the
|
|
hexadecimal DBCS graphic constant is:</p>
|
|
<p>GX'ssss'</p>
|
|
<p>In the constant, <span class="bold">ssss</span> represents a string
|
|
from 0 to 32760 hexadecimal digits. The number of characters between the string
|
|
delimiters must be an even multiple of 4. Blanks between the string delimiters
|
|
are ignored. Each group of 4 digits represents a single DBCS graphic character.
|
|
The hexadecimal for shift-in and shift-out ('0E'X and '0F'X) are not included
|
|
in the string.</p>
|
|
<p>The CCSID assigned to constants is the DBCS CCSID associated with the CCSID
|
|
of the source unless the source is encoded in a foreign encoding scheme (such
|
|
as ASCII). In this case, the CCSID assigned to the constant is the DBCS CCSID
|
|
associated with the default CCSID of the current server when the SQL statement
|
|
containing the constant is prepared. If there is no DBCS CCSID associated
|
|
with the CCSID of the source, the CCSID is 65535.</p>
|
|
<p>For information on associated DBCS CCSIDs, see the <a href=" ../nls/rbagsuseccsiddesign.htm">Globalization DBCS CCSIDs</a> topic in the iSeries Information Center. For information on the
|
|
CCSID of the source, see Character String Constants.</p>
|
|
<a name="wq281"></a>
|
|
<h4 id="wq281">UTF-16 graphic-string constants</h4><a id="idx517" name="idx517"></a><a id="idx518" name="idx518"></a><a id="idx519" name="idx519"></a><a id="idx520" name="idx520"></a>
|
|
<p>A hexadecimal UTF-16 (or UCS-2) graphic constant is supported. The form
|
|
of the hexadecimal UTF-16 graphic constant is:</p>
|
|
<p>UX'ssss'</p>
|
|
<p>In the constant, <span class="bold">ssss</span> represents a string
|
|
from 0 to 32760 hexadecimal digits. The number of characters between the string
|
|
delimiters must be an even multiple of 4. Blanks between the string delimiters
|
|
are ignored. Each group of 4 or more digits represents a single UTF-16 graphic
|
|
character.</p>
|
|
<p>The CCSID of a UTF-16 constant is 1200.</p>
|
|
<a name="wq282"></a>
|
|
<h3 id="wq282"><a href="rbafzmst02.htm#ToC_196">Binary-string constants</a></h3><a id="idx521" name="idx521"></a><a id="idx522" name="idx522"></a><a id="idx523" name="idx523"></a><a id="idx524" name="idx524"></a><a id="idx525" name="idx525"></a><a id="idx526" name="idx526"></a><a id="idx527" name="idx527"></a><a id="idx528" name="idx528"></a>
|
|
<p>A <span class="italic">binary-string constant</span> specifies a varying-length
|
|
binary string. The form of a binary-string constant follows: </p>
|
|
<ul>
|
|
<li>An X followed by a sequence of characters that starts and ends
|
|
with a string delimiter. The characters between the string delimiters must
|
|
be an even number of hexadecimal digits. Blanks between the string delimiters
|
|
are ignored. The number of hexadecimal digits must not exceed 32740. A hexadecimal
|
|
digit is a digit or any of the letters A through F (uppercase or lowercase).</li></ul>
|
|
<p>The CCSID assigned to the constant is 65535.</p>
|
|
<p>Note that the syntax of a binary string constant is identical to the second
|
|
form of a character constant. A constant of this form is only treated as a
|
|
binary string constant if:</p>
|
|
<ul>
|
|
<li>The SET OPTION statement was specified with the binary string option (<tt class="xph">SQLCURRULE = *STD</tt>),</li>
|
|
<li>the SQLCURRULE(*STD) parameter was specified on the CRTSQLxxx
|
|
or RUNSQLSTM command, or</li>
|
|
<li>the SQL rules option was specified on the Change Session Attributes
|
|
panel of Interactive SQL.</li></ul>
|
|
<a name="wq283"></a>
|
|
<h4 id="wq283">Example</h4>
|
|
<pre class="xmp"> X'FFFF'</pre>
|
|
<a name="wq284"></a>
|
|
<h3 id="wq284"><a href="rbafzmst02.htm#ToC_198">Datetime constants</a></h3><a id="idx529" name="idx529"></a><a id="idx530" name="idx530"></a>
|
|
<p>A <span class="italic">datetime constant</span> specifies a date, time, or
|
|
timestamp. Typically, character-string constants are used to represent constant
|
|
datetime values in assignments and comparisons. However, the ANSI/ISO SQL
|
|
standard form of a datetime constant can be used to specifically denote the
|
|
constant as a <span class="italic">datetime constant</span> instead of a character-string
|
|
constant. For more information see <a href="rbafzmstch2data.htm#dtstrng">String representations of datetime values</a>.</p>
|
|
<a name="wq285"></a>
|
|
<h4 id="wq285">Example</h4>
|
|
<pre class="xmp"> DATE '2003-09-03'</pre>
|
|
<a name="datsep"></a>
|
|
<h3 id="datsep"><a href="rbafzmst02.htm#ToC_200">Decimal point</a></h3><a id="idx531" name="idx531"></a>
|
|
<p>The <span class="italic">default decimal point</span> can be specified:</p>
|
|
<ul>
|
|
<li>To interpret numeric constants</li>
|
|
<li>To determine the decimal point character to use when casting a character
|
|
string to a number (for example, in the DECIMAL, DOUBLE_PRECISION, FLOAT,
|
|
and REAL scalar functions and the CAST specification)</li>
|
|
<li>to determine the decimal point character to use in the result when casting
|
|
a number to a string (for example, in the CHAR, VARCHAR, CLOB, GRAPHIC, and
|
|
VARGRAPHIC scalar functions and the CAST specification)</li></ul>
|
|
<p>The default decimal point can be specified through the following interfaces:
|
|
<a id="idx532" name="idx532"></a><a id="idx533" name="idx533"></a></p>
|
|
<a name="dftcol1b"></a>
|
|
<table id="dftcol1b" width="100%" summary="" border="1" frame="border" rules="rows">
|
|
<caption>Table 22. Default Decimal Point Interfaces</caption>
|
|
<thead valign="bottom">
|
|
<tr>
|
|
<th id="wq286" width="40%" align="left" valign="bottom">SQL Interface</th>
|
|
<th id="wq287" width="60%" align="left" valign="bottom">Specification</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody valign="top">
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq286">Embedded SQL</td>
|
|
<td align="left" valign="top" headers="wq287">The *JOB, *PERIOD, *COMMA, or *SYSVAL value
|
|
in the OPTION parameter is specified on the Create SQL Program (CRTSQLxxx)
|
|
commands. The SET OPTION statement can also be used to specify the DECMPT
|
|
parameter within the source of a program containing embedded SQL.
|
|
<div class="lines"><br />
|
|
</div>(For
|
|
more information about CRTSQLxxx commands, see the <a href="../rzajp/rzajpkickoff.htm">Embedded
|
|
SQL Programming</a> book.)</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq286">Interactive SQL and Run SQL Statements</td>
|
|
<td align="left" valign="top" headers="wq287">The DECPNT parameter on the Start SQL (STRSQL)
|
|
command or by changing the session attributes. The DECMPT parameter on the
|
|
Run SQL Statements (RUNSQLSTM) command.
|
|
<div class="lines"><br />
|
|
</div>(For more information
|
|
about STRSQL and RUNSQLSTM commands, see the <a href="../sqlp/rbafykickoff.htm">SQL Programming</a> book.)</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq286">Call Level Interface (CLI) on the server</td>
|
|
<td align="left" valign="top" headers="wq287">SQL_ATTR_DATE_FMT and SQL_ATTR_DATE_SEP environment
|
|
or connection variables
|
|
<div class="lines"><br />
|
|
</div>(For more information about CLI, see
|
|
the <a href="../cli/rzadpkickoff.htm">SQL Call Level Interfaces (ODBC)</a> book.)</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq286">JDBC or SQLJ on the server using IBM® Developer Kit for Java</td>
|
|
<td align="left" valign="top" headers="wq287">Decimal Separator conneciton property
|
|
<div class="lines"><br />
|
|
</div>(For
|
|
more information about JDBC and SQLJ, see the <a href="../rzaha/whatitis.htm">IBM Developer Kit
|
|
for Java™</a> topic in the iSeries Information Center.)</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq286">ODBC on a client using the iSeries Access Family ODBC Driver</td>
|
|
<td align="left" valign="top" headers="wq287">Decimal Separator in the Advanced Server
|
|
Options in ODBC Setup
|
|
<div class="lines"><br />
|
|
</div>(For more information about ODBC, see
|
|
the <a href="../rzahg/rzahgicia.htm">iSeries™ Access</a> category in the iSeries Information Center.)</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq286">JDBC on a client using the IBM Toolbox for Java</td>
|
|
<td align="left" valign="top" headers="wq287">Format in JDBC Setup
|
|
<div class="lines"><br />
|
|
</div>(For
|
|
more information about ODBC, see the <a href="../rzahg/rzahgicia.htm">iSeries Access</a> category
|
|
in the iSeries Information Center.)
|
|
<div class="lines"><br />
|
|
</div>(For more information about the IBM Toolbox for Java,
|
|
see <a href="../rzahh/page1.htm">IBM Toolbox for Java</a> topic in the iSeries Information Center .)</td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
<p>If the comma is the decimal point, the following rules apply: </p>
|
|
<ul>
|
|
<li>A period will also be allowed as a decimal point.</li>
|
|
<li>A comma intended as a separator of numeric constants in a list must be
|
|
followed by a space.</li>
|
|
<li>A comma intended as a decimal point must not be followed by a space.</li></ul>
|
|
<p>Thus, to specify a decimal constant without a fractional part, the trailing
|
|
comma must be followed by a non-blank character. The non-blank character can
|
|
be a separator comma, as in: </p>
|
|
<pre class="xmp"> <span class="bold">VALUES(</span>9999999999,, 111<span class="bold">)</span></pre>
|
|
<a name="wq288"></a>
|
|
<h3 id="wq288"><a href="rbafzmst02.htm#ToC_201">Delimiters</a></h3><a id="idx534" name="idx534"></a><a id="idx535" name="idx535"></a><a id="idx536" name="idx536"></a><a id="idx537" name="idx537"></a>
|
|
<p>*APOST and *QUOTE are mutually exclusive COBOL precompiler options
|
|
that name the string delimiter within COBOL statements. *APOST names the
|
|
apostrophe (') as the string delimiter; *QUOTE names the quotation
|
|
mark ("). *APOSTSQL and *QUOTESQL are mutually exclusive COBOL precompiler
|
|
options that play a similar role for SQL statements embedded in COBOL programs. *APOSTSQL names
|
|
the apostrophe (') as the SQL string delimiter; with this option, the
|
|
quotation mark (") is the SQL escape character. *QUOTESQL names the quotation
|
|
mark as the SQL string delimiter; with this option, the apostrophe is the
|
|
SQL escape character. The values of *APOSTSQL and *QUOTESQL are respectively
|
|
the same as the values of *APOST and *QUOTE.</p>
|
|
<p>In host languages other than COBOL, the usages are fixed. The string delimiter
|
|
for the host language and for static SQL statements is the apostrophe (');
|
|
the SQL escape character is the quotation mark (").</p>
|
|
<hr /><br />
|
|
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmstuuall.htm">Previous Page</a> | <a href="rbafzmstspecreg.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>
|