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

See my other reply to Birgitta that points out a QMQRY-with-substitution-variables solution - it might be a way to get closer to Joel's requirements.

Vern

On 7/31/2012 8:11 AM, Charles Wilt wrote:

Short answer, no....you can't do that.

Long answer, you could do it with SQL, but you'd have to use dynamic
SQL. You'd used the various APIs to retrieve a field list from both
tables (or via the SQL catalog tables) and build a dynamic SQL
statement with explicit field names.

HTH,
Charles


On Mon, Jul 30, 2012 at 6:43 PM, Stone, Joel <Joel.Stone@xxxxxxxxxx> wrote:

OK Thanks

I am not explaining myself clear enough in what I am trying to accomplish.

I want the SQL insert to copy fields with the same name from the input file to the output file. I don't want to have to name each field individually.

Also if a field doesn't exist in the output file, I want SQL to ignore it. Only exception is the RRNField, which I want to map to the output file.

I want it to work like CPYF *MAP *DROP.

FileIN FileOUT

Cust# Cust#
Order# Order#
Size ----
Price Price
Qty ----
---- Color


I would like the SQL Insert stmt to copy fields Cust#, Order#, and Price (without specifically naming them).

It should drop fields Size and Qty (like CPYF *DROP) and it should initialize the COLOR field to blanks (like CPYF) - all without naming ANY of those fields (like Cobol Move Corresponding).

I don't want to name them so I can re-use the CL for many files, not just one.

Is this possible with SQL Insert?

I am using SQL Create to a temp file then CPYF *MAP *DROP. It works, but it would be great if SQL could do the whole deal.

Thanks!




-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Monday, July 30, 2012 4:51 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL: insert records, but intialize most fields to blanks & zeros

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

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