Gundermann, Glenn wrote:
It's been a long day so I'm hoping someone can help me figure out
what I'm doing wrong:
create table library/newfile as
(select field1, field2, char(35) as newfield, field3 from oldfile)
definition only
for some reason, this creates newfield with a length of 11
instead of 35.
I'm on V5R4.
As Dennis already clarified, the definition of NEWFIELD in the
given SELECT is the literal number 35 being cast as a CHAR data type
with an implicitly defined length. That is, the _expression_ that
defines NEWFIELD, is the scalar "cast function" CHAR() with the
literal\constant value of 35 as the only argument for that function.
The length of the character result is determined by the SQL,
according to its casting data-typing [and in some cases "promotion
of data type"] rules. In this scenario the literal 35 is implicitly
cast to INT for lack of a decimal separator and sufficiently few
digits so as not to exceed the maximum integer value. The CHAR
scalar function then asks to cast that integer to character, without
a length being specified [not even an option in CHAR()] effects a
CHAR(11) datatype and length attribute. The length of 11 was chosen
by the SQL to support the largest signed INTeger value of 2147483647
[which is 10 digits] plus one character for the sign. See "Integer
to Character" at:
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzscachar.htm
As Luis suggests, the typical manner to effect a newly described
column would be to use the SQL "CAST specification" which allows
specifying the data type, length [for numeric: scale & precision],
and CCSID attributes. However for a new field, rather than
specifying an actual value, what is typically more appropriate in
the database would be to assign "no value". A literal value [e.g.
blank\spaces] is often desirable to avoid, because whatever is the
chosen literal may appear in a query report or an application, to be
a legitimate value. So instead of casting a literal to the desired
type\length\ccsid, the database SQL NULL value would be cast to the
desired type\length\ccsid; i.e. instead of the first CAST below, you
may want the second:
CAST('' AS CHAR(35)) as NEWFIELD
CAST(NULL AS CHAR(35)) as NEWFIELD
In either case [i.e. expression as a literal or the NULL value],
specifying the CCSID for the column may be appropriate for the
specific application requirements; e.g.:
CAST(NULL AS CHAR(35) CCSID 1208) as NEWFIELD
Also FWiW, often it seems people are not aware that for a dynamic
statement with parameter markers, the CAST is both available and
often desirable. The CAST specification allows a parameter marker
as the expression, to be cast to specified typing; e.g.:
CAST(? AS TIME)
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzcast.htm
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzcastdt.htm
Regards, Chuck
midrange.com
