RE: Records written to a table only visible at end of program execution, it seems.

You could just use 2 statements in place of the MERGE:

insert into <myOutputTable> a
where not exists ( select 1 from <myInputTable> b where a.PartNumber = b.PartNumber)

update <myOutputTable> a
set a.PartNumber = 'myValue'
where exists ( select 1 from <myOutputTable> b where b.PartNumber = 'myValue' )


Thanks,
Tommy Holden

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of a4g atl
Sent: Monday, April 23, 2018 1:32 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: Records written to a table only visible at end of program execution, it seems.

@Joep
This system is at V5R4 which does not have the MERGE option. We are going to 7.3 shortly.

Darryl

On Fri, Apr 20, 2018 at 3:58 AM, Joep Beckeringh <joep.beckeringh@xxxxxxxxxx

wrote:

Darryl,

Have you looked at SQL statement merge
(<https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww
.ibm.com%2Fsupport%2F&data=02%7C01%7CTommy.Holden%40tmcat.com%7C21a850
55c4764c2be70408d5a9489d6e%7C7bedfe4f5ee143529ded95e9c966aa39%7C0%7C0%
7C636601051709878504&sdata=Me%2BDIrf047xuSo3LtkRGBZ6DntVhhgSHCER%2F7D%
2BIt1s%3D&reserved=0
knowledgecenter/nl/ssw_ibm_i_73/db2/rbafzmerge.htm>)? Basically you
can execute either an update ('when matched') or an insert ('when not matched').

Joep Beckeringh


Op 19-4-2018 om 20:10 schreef a4g atl:

I have a SQL RPGLE program reading from the input file and testing if
a

record with the same part number exists in the target file. The input
file has duplicates and I am trying to filter out the duplicates.

The output file is cleared with DELETE ALL up front.


The process is
1. read input file.
2. use Select count(*) counting if the part number exists in the
output file.
SELECT COUNT(*) into :COUNT from INPUTFILE WHERE PART_NO =
:PART 3. if it does not exist, I do an INSERT.
4. if it exists, I update the record with the latest values.
5 Read next input record.

I have the isolation level set to *none. The file is a new file and
is not journaled.

Problem:
The count process does not see the records already inserted so I am
getting duplicates written to the file. Its almost like the program
needs a COMMIT executed after each INSERT.

Why does the program not find the records it has written?

TIA

Darryl Freinkel.

--
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:
https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists
.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=02%7C01%7CTommy.H
olden%40tmcat.com%7C21a85055c4764c2be70408d5a9489d6e%7C7bedfe4f5ee1435
29ded95e9c966aa39%7C0%7C0%7C636601051709878504&sdata=4x2QzcbgZW6x4w5GW
wYO9ErULeQTv0kInRRM%2F%2FS%2FIbA%3D&reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=02%7C01%7CTommy.Holden%40tmcat.com%7C21a85055c4764c2be70408d5a9489d6e%7C7bedfe4f5ee143529ded95e9c966aa39%7C0%7C0%7C636601051709878504&sdata=dQ4mfV7GafzbweXIHCERngRzNz88Rsec3HF653lG%2BdY%3D&reserved=0.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Famzn.t
o%2F2dEadiD&data=02%7C01%7CTommy.Holden%40tmcat.com%7C21a85055c4764c2b
e70408d5a9489d6e%7C7bedfe4f5ee143529ded95e9c966aa39%7C0%7C0%7C63660105
1709878504&sdata=yERWfPLgsRe5Ar3xrRtzSFMBkl0rAP%2BVmCxCe7xfdJ0%3D&rese
rved=0

--
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: https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=02%7C01%7CTommy.Holden%40tmcat.com%7C21a85055c4764c2be70408d5a9489d6e%7C7bedfe4f5ee143529ded95e9c966aa39%7C0%7C0%7C636601051709878504&sdata=4x2QzcbgZW6x4w5GWwYO9ErULeQTv0kInRRM%2F%2FS%2FIbA%3D&reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=02%7C01%7CTommy.Holden%40tmcat.com%7C21a85055c4764c2be70408d5a9489d6e%7C7bedfe4f5ee143529ded95e9c966aa39%7C0%7C0%7C636601051709878504&sdata=dQ4mfV7GafzbweXIHCERngRzNz88Rsec3HF653lG%2BdY%3D&reserved=0.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Famzn.to%2F2dEadiD&data=02%7C01%7CTommy.Holden%40tmcat.com%7C21a85055c4764c2be70408d5a9489d6e%7C7bedfe4f5ee143529ded95e9c966aa39%7C0%7C0%7C636601051709878504&sdata=yERWfPLgsRe5Ar3xrRtzSFMBkl0rAP%2BVmCxCe7xfdJ0%3D&reserved=0