× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.