× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.