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

Hi Birgitta

I believe that the defaults are blanks or zeros if they are not specified - is that your understanding as well? Joel wants blanks and zeroes, I think.

What you have here gives defaults that are the same as the built-in defaults, along with some that are different from the "default" defaults, right?

I just ran an INSERT over a table that specified only one column of a table that has maybe 10 columns, each with a different data type. Here is the statement -

INSERT INTO VERN/ALLTYPES (FCHAR) SELECT lstnam FROM qiws/qcustcdt

FCHAR is a character field - there are almost every data type. The numerics all were given a value of zero - alphas were given blanks, date was the current date, time the current time, and timestamp the current timestamp. I think this was created with a CREATE TABLE statement.

I now created a PF using DDS - an alpha field, a packed field, and D/T/Z fields. I did a similar INSERT statement - specified ONLY the alpha field - packed was set to zero, the DTZ fields to current values.

So defaults work the same way with either DDS-based or DDL-based tables. If we specify a special default, that would work the same way, as well.

Regards
Vern

On 7/31/2012 12:11 AM, Birgitta Hauser wrote:

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
______________________________________________________________________