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

Jim,

I couldn't come up with a way of using UPDATE. Would it work to create a new
file (table) to hold the desired result?

Check out the following SQL commands.

-- Create new table in same format as original
create table mylib.myfile2 like mylib.myfile

-- Copy records to new table
insert into mylib.myfile2
select case
when c.joinrrn is null then a.line
else substr(a.line,1,c.pos-1)||substr(c.line2,c.pos)
end
from qtemp.test a
left outer join (
select
rrn(b)-1 as joinrrn,
locate('Z', translate(b.line, 'Z', '0123456789-', 'Z'), 5) as pos,
b.line as line2
from qtemp.test b
where substr(b.line,4,1)='0') c

on rrn(a)=c.joinrrn
where substr(a.line,4,1)<>'0'


Have fun!
Richard


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jim Essinger
Sent: Tuesday, March 16, 2010 12:24 PM
To: Midrange Systems Technical Discussion
Subject: Sql update of a file based on the same file

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