RE: SQL: NOT NULL DEFAULT

You misunderstood Chuck's post....

To omit the columns from the insert you must specify the columns you are going to insert.

By not explicitly specify columns, you are implicitly saying you will provide a value for all the
columns.

This isn't valid syntax, but maybe it will help get the point across...
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)

Vs.

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)


Note that in v6r1, there's a new column attribute IMPLICITLY HIDDEN. Which enables the behavior you
are trying to get.

/* Create table. v6r1 */
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
IMPLICITLY HIDDEN
DEFAULT USER;

ALTER TABLE to-file
ADD COLUMN H3_CREATED_TIMESTAMP
FOR COLUMN H3CRTTIMST TIMESTAMP
NOT NULL
IMPLICITLY HIDDEN
DEFAULT CURRENT_TIMESTAMP;

/* Works at v6r1 */
insert into to-file
(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 Glenn Gundermann
Sent: Monday, June 23, 2008 12:58 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL: NOT NULL DEFAULT

Hi Chuck,

When I omit the user and time stamp columns, I get a message saying the
number of values don't match.

Tks, Glenn


<snip>

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

Regards, Chuck

--
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.