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



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


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.