Re: Sql update of a file based on the same file

I would say that SQL isn't really appropriate for I/O on flat files. This
can be done quite easily with plain RPG, and a bit of knowledge about the
print data stream. You can simply read through the file in arrival
sequence, and buffer each line. When you get a 0 line add the portion
that is to the right of the last non-blank character in the buffer. This
will strip out any overprinting, and keep the lines together.

Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx

midrange-l-bounces@xxxxxxxxxxxx wrote on 03/16/2010 12:24:06 PM:

From: Jim Essinger <dilbernator@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 03/16/2010 12:34 PM
Subject: Sql update of a file based on the same file
Sent by: midrange-l-bounces@xxxxxxxxxxxx

All,

I have a file that contains 1 field called LINE that is 186 characters

long,

and contains spool data. In about 900 cases, (of 25,000+ lines) one
particular line gets split into 2 lines like;

2 1234.56-
0 798.00

I need to have them combined into one line like:

2 1234.56- 798.00


The data is all numbers, edited. The 2nd line is always a zero in

position

4 of the line, and one RRN later in the file. What I want to do is to

update

the first line with both parts of the data, and then I will remove the
"Zero" record.

I have created an SQL statement that will combine the two pieces into

one

line that is correct. Now I need to use that statement to update the
correct records. That is where my mind has failed me.

The select statement that puts together the 2 lines is;

select rrn(a), substr(a.LINE,1,String_str - 1) concat
substr(c.LINE,string_str)
from MyFile a join (
select rrn(b) - 1 as Join_rrn ,
locate('Z',
substr(b.LINE,1,4) concat
translate(substr(b.line,5,length(b.LINE) -1),'Z','.0123456789-','Z'))
as String_Str,
b.LINE
from MyFile b
where line like ' 0%' ) c on rrn(a) = join_rrn

The subselect translates all editing and numbers to a "Z" and then

locates

the first occurrence of "Z" to find the starting of the string on the

second

line. The file is joined to itself based on RRN and the line containing

a

zero in position 4.

1st - Is there an easier way I could code this statement?

2nd - How can I use this statement in an UPDATE to change the correct

line

using RRN as the key?

Thanks!

Jim
--

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.