On 29-Mar-2012 12:37 , Stone, Joel wrote:
SQL create a new table by joining two existing PFs - how can I keep
attributes of 2nd join file? ESPECIALLY the "not null" attribute?
One option is to not use a type of JOIN which dictates null
capability in the /secondary/ [for lack of a better term] file.
When building a TABLE definition from existing columns in a CREATE
TABLE AS, choosing columns as effective Reference Fields [RefFld, as
with DDS], omitting join specifications and specifying the WITH NO DATA
clause is preferable.
Must I place a VALUE(custName, ' ') around each 2nd file field to
force NOT NULLs?
Given the last value in VALUE, COALESCE, or IFNULL [the former are
synonyms, and the latter only allows one other value] is a literal or
another NOT NULL expression, then that will suffice for all but the
joined-on columns which dictate null capability [as seen on v5r3;
presumably that is the expected behavior, but I did not check the docs].
Another option [even less desirable IMO] is to ALTER TABLE after the
CREATE TABLE AS, to effect SET NOT NULL on the same list of columns that
would be subject of IFNULL:
alter table newtable
/* alter column custidnew set not null -- if included */
alter column custname set not null
alter column custaddr set not null
I am trying to join OLDFILE with the CUST file, and add a few of the
fields from the CUST file WHILE keeping the NOT NULL attribute of
the CUST fields.
The columns of the secondary file for a LEFT OUTER JOIN are
implicitly _null capable_ because the SQL generates NULL for unmatched
rows, irrespective of DEFAULT specification for generated rows.
Example re-written and modified for functionality, and presented as a
script to include creation of the secondary table to explicitly show NOT
NULL originally, is inserted here; still assumed that file OLDFILE has
no null-capable columns:
<code>
create table mylib/custfile
( custID integer not null
, custAddr varchar(70) not null
, custName varchar(70) not null )
; -- like OLDFILE, CUSTFILE has no null-capable columns
create table mylib/NEWTABLE as
( select OLDFILE.*
, custName, custAddr
from mylib/OLDFILE left outer join mylib/CUSTFILE
on oldfile.custID = custfile.custID
) with no data
; -- custName and custAddr will both be AlwNull
SELECT sys_cname, nulls FROM syscolumns
WHERE dbname='MYLIB' and sys_tname='NEWTABLE' and NULLS='Y'
; -- yields CUSTNAME and CUSTADDR as null-capable
drop table mylib/NEWTABLE
;
create table mylib/NEWTABLE as
( select OLDFILE.*
, ifnull(custName,'') as custName
, ifnull(custAddr,'') as custAddr
from mylib/OLDFILE left outer join mylib/CUSTFILE
on oldfile.custID = custfile.custID
) with no data
; -- custID and custName will both have NOT NULL
SELECT sys_cname, nulls FROM syscolumns
WHERE dbname='MYLIB' and sys_tname='NEWTABLE' and NULLS='Y'
; -- yields no rows; i.e. no columns are null capable
drop table mylib/NEWTABLE
;
create table mylib/NEWTABLE as
( select OLDFILE.*
, ifnull(custfile.custID,0) as custIDnew
, ifnull(custName,'') as custName
, ifnull(custAddr,'') as custAddr
from mylib/OLDFILE left outer join mylib/CUSTFILE
on oldfile.custID = custfile.custID
) with no data
; -- custIDnew has Allow Null irrespective of IFNULL
SELECT sys_cname, nulls FROM syscolumns
WHERE dbname='MYLIB' and sys_tname='NEWTABLE' and NULLS='Y'
; -- yields CUSTIDNEW as the only null-capable column
alter table newtable alter column custidnew set not null
;
SELECT sys_cname, nulls FROM syscolumns
WHERE dbname='MYLIB' and sys_tname='NEWTABLE' and NULLS='Y'
; -- yields no rows; i.e. no columns are null capable
drop table mylib/NEWTABLE
;
create table mylib/NEWTABLE as
( select OLDFILE.*
, custfile.custID as custIDnew
, custName as custName
, custAddr as custAddr
from mylib/OLDFILE INNER JOIN mylib/CUSTFILE
on oldfile.custID = custfile.custID
) with no data
; -- Allow Null not required of INNER JOIN secondary fields
SELECT sys_cname, nulls FROM syscolumns
WHERE dbname='MYLIB' and sys_tname='NEWTABLE' and NULLS='Y'
; -- yields no rows; i.e. no columns are null capable
drop table mylib/NEWTABLE
;
</code>
Note the last CREATE TABLE using INNER JOIN for which no default rows
must be generated, so no columns gain nullability.
Regards, Chuck
midrange.com
