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

I avoided the NULL fields by using the following code - the CHAR and the VALUE end up giving a fixed-length, no nulls allowed 1 byte field.


chgvar &sqlCmd ('select zoned(client_n,6,0) as client_n ,' +
*tcat ' char(value(' +
*tcat ' max(case when client_act = ''S'' then ''S'' else '' '' end),'' ''))' +
*tcat ' as Settle_Act,' +



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Wednesday, March 28, 2012 5:33 PM
To: midrange-l@xxxxxxxxxxxx
Subject: 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