AW: NOT NULL DEFAULT

Hi,

That's not what you want to hear, but instead of using DEFAULT you can
directly use the special registers USER and CURRENT_TIMESTAMP in your insert
statement.
insert into tofile
(select F.* , User, Current_Timestamp from fromfile F
where F.h3ipco = '61' and
F.h3kfdt = 1030123 and
F.h3c9tm = 202631 and
F.h3ksnr = 1);

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Glenn Gundermann
Gesendet: Friday, June 20, 2008 20:01
An: midrange-l@xxxxxxxxxxxx
Betreff: 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
ggundermann@xxxxxx
Cell: 416-317-3144