Re: SQL: insert records, but intialize most fields to blanks & zeros -- MIDRANGE-L

Joel

You do not have the required field list in your INSERT statement. That's what the message means - "wrong number of values" - your SELECT has exactly 1 value - the file you are inserting into has more than 1 field, I bet.

You jumped to the complicated problem of NULL-capable fields - there is another reason given in the message text.

So you statement has to be something like this -

insert into bzmdcdtl (RRNField) (
select rrn(a) as XCM_RRN
from mdcdtl a join memodc b on a.ADBTCRD = b.ADBTCRD
where a.ADBTCRD = '2278179')

That will put default values - there are ALWAYS defaults, even if not expressly stated - they are the data-type defaults - blanks for alpha fields, 0 for numeric - same as any database system does. I forget what they are for dates, times, timestamps, but it's in the documentation.

HTH
Vern

On 7/30/2012 4:24 PM, Stone, Joel wrote:

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