MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » June 2008

Re: SQL: NOT NULL DEFAULT



fixed

DEFAULT is allowed in the VALUES for INSERT INTO. For a value in a full-select, specify a literal, special register, or function. So for the second example that works, that could be done instead of with literals, using:

<code>
insert into to-file
(select F.* , USER , CURRENT TIMESTAMP
from from-file F
where ...
)
</code>

Effectively the same as:

<code>
create table fromfile (a char, b int)
;
create table tofile like fromfile rcdfmt rcdfmtname
;
alter table tofile
add column createdby varchar(18) not null with default USER
add column createdat timestamp not null with default CURRENT TIMESTAMP
;
insert into tofile (a, b) -- specify column list for insert
(select F.* from fromfile F -- F.* is column list (a, b)
where ... )
;
</code>

When the columns are omitted on the insert, the default will be established for each row inserted in those omitted columns.

Regards, Chuck

Glenn Gundermann wrote:

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?





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