RE: NOT NULL DEFAULT

There are a couple of ways I can think of handling this off the top of my
head.

One that I'd prefer is to run your select statement during the CREATE TABLE
run. Something like:

CREATE TABLE to-file AS
(select F.* from from-file F
where F.h3ipco = '61' and
F.h3kfdt = 1030123 and
F.h3c9tm = 202631 and
F.h3ksnr = 1)
WITH DATA RCDFMT rcdfmt-name;

Then run your ALTER TABLE to add columns, and you ought to be done.

Another solution that's more inline with what you already have is to repeat
the defaults:

insert into to-file
(select F.* , USER,CURRENT_TIMESTAMP from from-file F
where F.h3ipco = '61' and
F.h3kfdt = 1030123 and
F.h3c9tm = 202631 and
F.h3ksnr = 1);

There may be other solutions, but that's all I can think of right now.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: SQL: NOT NULL DEFAULT

Hi folks,

I'm not absolutely sure about the creation of a table with regards to
whether I should specify NOT NULL or not and how to take advantage of the
DEFAULT.

Basically, I want to be able to do the following:

I have to-file which is a copy of from-file plus two fields, user &
timestamp, created by:
/* Create table. */
CREATE TABLE to-file LIKE from-file
RCDFMT rcdfmt-name ;
ALTER TABLE to-file ADD COLUMN H3_CREATED_BY FOR COLUMN H3CRTUSER
VARCHAR(18) NOT NULL DEFAULT USER;
ALTER TABLE to-file ADD COLUMN H3_CREATED_TIMESTAMP FOR COLUMN
H3CRTTIMST TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

Now I want to copy records from from-file to to-file using:

insert into to-file
(select F.* , DEFAULT , DEFAULT from from-file F
where F.h3ipco = '61' and
F.h3kfdt = 1030123 and
F.h3c9tm = 202631 and
F.h3ksnr = 1);

This does not work, giving me "Column DEFAULT not in specified tables."

The following does work though:

insert into to-file
(select F.* , 'KNCZIAGG' , '2008-06-20-13.21.00.000000' from from-file F
where F.h3ipco = '61' and
F.h3kfdt = 1030123 and
F.h3c9tm = 202631 and
F.h3ksnr = 1);

Do I need/want NOT NULL in my CREATE TABLE statement?
How can I take advantage of DEFAULT in my INSERT statement?

Thanks.

Yours truly,

Glenn Gundermann