RE: NOT NULL DEFAULT -- MIDRANGE-L

Looking at Elvis' 2nd solution...

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);

You have to specify the USER and CURRENT_TIMESTAMP again as your insert statement doesn't specify any
fields, thus you are saying you plan to provide values for all of them.

The field defaults and/or NULL values are only used when a value isn't going to be provides. So if
you specified the fields to be inserted into, you could leave off the last two and then the defaults
you specified in the alter table would be effective.

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

HTH,

Charles Wilt
--
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307
wiltc@xxxxxxxxxx

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of Elvis Budimlic
Sent: Friday, June 20, 2008 2:52 PM
To: 'Midrange Systems Technical Discussion'
Subject: 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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.