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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

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

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