Re: SQL - how to create NOT NULL fields in a SELECT stmt

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 */
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzhctabl.htm

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