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

On 27 Jul 2012 13:23, Stone, Joel wrote:

I need to capture the RRN of a record, and later come by and
populate remaining fields with current values.

insert into orderDTL
( select rrn(a) as rrn
from <<SNIP>>
)

This fails because there are more fields in orderDTL file than just
RRN.

How can I initialize the other fields to spaces and zeros?

Should I do a 2-step process and CREATE a new file followed by
CPYF with Format Option *MAP *DROP?

To limit the columns into which data is inserted, specify a (column-list) for the TABLE [or VIEW] as target of the INSERT INTO statement. All other columns will have defaults assigned; those defaults may not be blanks and zero values. For example:

insert into orderDTL ( rrn )
( select rrn(a) as rrn
from ...
)

If some other columns need a value other than DEFAULT, then a literal\constant could be selected to assign the desired value to the columns:

insert into orderDTL ( rrn , myChar5, myDec5 )
( select rrn(a), '', 0
from ...
)

Regards, Chuck