On 28-Mar-2012 11:38 , Stone, Joel wrote:

The CREATE TABLE AS will generate the Record Format from the SELECT query that is code; similar to a CREATE VIEW. By default, expressions involving a null capable column will be generated in the internal query as "Allows the null value" [ALWNULL in DDS], and reflected as such in the RcdFmt created for the TABLE; as with a VIEW. While the resulting data for any expression can be prevented from actually being represented as the database NULL value [e.g. IFNULL() or COALESCE()], the column attributes can not be adjusted within that variation of the CREATE TABLE syntax [i.e. using AS] which does not enable /data-type/ specification for which a NOT NULL clause could be included.

_CREATE TABLE_ /* see syntax */

Oddly, the parenthetical column-list capability is not shown\alluded by that syntax diagram, even though accepted\honored. For example either of the following can establish column-names:

CREATE TABLE table-name (column-list) AS (select ... )

CREATE TABLE table-name AS (select expr1 AS column-name, ... )

FWiW, I do not believe any of the /copy-options/ could assist to preclude the null capability, for the /as-result-table/ syntax.

The typical CREATE TABLE syntax using /column-definition/ or the CREATE TABLE LIKE to reference an existing table [as an effective REFFILE] can be used to pre-create the TABLE with the desired column attributes [aka Record Format]. Then the INSERT INTO statement can be used instead of the CREATE TABLE AS to place the data in the previously created TABLE.

Regards, Chuck

This thread ...


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

This mailing list archive is Copyright 1997-2020 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].