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

This thread ...

Replies:

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

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