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

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

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