Did you specify default values for your columns (Not NULL Default 0) or are
the columns only defined with NOT NULL?
If there are no default values defined, add a default value:
alter table MySchema/Mytable alter column MyColumn set default DefaultValue;
After default values are set, you can insert only the columns values you
want for the rest default values are inserted.
Example:
create table Myschema/mytable
(MyInt integer Not NULL default 0,
MyChar char(10) Not NULL default '',
MySmallInt smallint Not NULL default 10,
MyVarChar VarChar(10) Not NULL default '',
MyDate date not NULL default Current_Date);
When executing the following SQL statement, the first column will be set to
20 while for all other fields default values are inserted.
insert into mytable (myint) values(20);;
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 Stone, Joel
Gesendet: Monday, 30.7 2012 23:24
An: 'Midrange Systems Technical Discussion'
Betreff: RE: SQL: insert records, but intialize most fields to blanks &
zeros
I cant seem to get this to work. The file was created (long ago) with DDL
with no default values.
I may be receiving error msgs because the fields don't allow NULL. The only
way I can get to work is to explicitly name each field, but this would
create time bombs down the road when someone added a field to the end of the
record.
I can do workaround by doing a CREATE TABLE and then CPYF *MAP *DROP, but
this seems not so clean.
Any ideas how to get this to work with fields defined as NOT NULL and not
naming each field?
insert into bzmdcdtl (
select rrn(a) as XCM_RRN
from mdcdtl a join memodc b on a.ADBTCRD = b.ADBTCRD
where a.ADBTCRD = '2278179')
Message . . . . : Statement contains wrong number of values.
Cause . . . . . : The following conditions may exist:
-- The number of values is not the same as the number of object columns
in
this INSERT or UPDATE statement.
-- One or more of the object columns specified in the INSERT statement
were created as NOT NULL, and the statement specified DEFAULT as the value
to be inserted.
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Friday, July 27, 2012 5:04 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL: insert records, but intialize most fields to blanks &
zeros
Joel
My reply, to look at the docs, would have shown you the column list others
are telling you about - all this is in the documentation.
Columns not in the column list of the INSERT are assigned their default
values. Additional columns in the table that are not involved in the INSERT
statement are just handled - eezy-peezy.
Vern
On 7/27/2012 4:00 PM, Stone, Joel wrote:
I am trying to use INSERT.
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Tommy.Holden@xxxxxxxxxxxxxxxxxxxxx
Sent: Friday, July 27, 2012 3:53 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL: insert records, but intialize most fields to blanks
& zeros
Would this not work?
CREATE TABLE QTEMP/ORDERDTL AS ( SELECT RRN(A) AS ROWNUMBER, A.*
FROM ORDERDTL A JOIN ORDERHDR B USING (ORDER#) WITH DATA
That would get it in one fell swoop (or a swell foop if you're a Piers
Anthony fan :D )
Thanks,
Tommy Holden
From: Luis Rodriguez <luisro58@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>,
Date: 07/27/2012 03:48 PM
Subject: Re: SQL: insert records, but intialize most fields to
blanks & zeros
Sent by: midrange-l-bounces@xxxxxxxxxxxx
Joel,
If your orderDTL has been defined with default values in its fields,
just insert the required fields. For example, if when you defined you
table you
wrote:
CREATE TABLE ORDTL(
FIELD1 CHAR (30 ) NOT NULL WITH DEFAULT
, FIELD2 NUMERIC (10 , 2) NOT NULL WITH DEFAULT
, DATE1 DATE NOT NULL WITH DEFAULT '0001-01-01'
) ;
Then just insert the RRN you need, something like:
INSERT INTO QTEMP/ORDTL (ROWNUMBER)
SELECT RRN(A) FROM orderDTL A JOIN orderHDR B using(ORDER#)
If you need to change the default values for an existing table, use
the ALTER TABLE sentence.
HTH,
Luis Rodriguez
IBM Certified Systems Expert ? eServer i5 iSeries
--
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 inbound email has been scanned for all viruses by the MessageLabs
SkyScan
service.
________________________________________________________________________
______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs
Skyscan service.
For more information please visit
http://www.symanteccloud.com
______________________________________________________________________
midrange.com
