MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » June 2008

SQL: NOT NULL DEFAULT



fixed

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





Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot 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 here. If you have questions about this, please contact