× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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
______________________________________________________________________

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.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 on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.