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



Chuck,

As always - thank you for your complete reply, and analysis.

BTW - you are right in that the statement posted looks for the zero in
the wrong place. That is a result from a program I use that strips
out white spaces and carriage returns so the statement posted will
look better. My statement had ' 0%' instead of what was posted.

  Rather than updating flat file data originating from a spooled
report, why not just get the original data from the same place [most
likely from a described database file] whence whatever reporting
tool had generated that spooled output?

 This is a one time fix for records that were created in statements
from the first two months of this year.  We do save the spool files,
but also copy the data in the spool files to a physical file that will
allow A / R to recreate past statements just as they were, and on
demand for a specific customer.  This programming is not my design, I
just get to work with it and fix issues. Recreating these statements
from data would require getting the EOM tapes back from storage,
restore, and re-running the program. This seemed a faster option to
give A / R the function they needed quickly.

We did an upgrade to the software that creates the statements at the
first of the year, and this error was created due to field sizes
increasing.  Any time the line was split, the 2nd line was omitted
from the physical file, and therefore not recreated correctly.  I know
what the problem is that created the issue and will fix it before the
next run of statements.  I just needed a way to fix the records that
are incorrect in the file for A / R.

I was trying to have SQL find the first non-blank position starting in
position 5, and the only way I could think to do it was translate all
the numbers and editing characters to a non-numeric character, and
locate the first occurrence of that value. It did work for what I
needed.

My biggest issue was not being able us use the RRN as a key to update
to. With this file there is no issue of reuse deleted records, or the
file getting out of sequence. It was created using a CPYSPLF command
to a database file.

The email/archive search showed another thread where Pete Halgren had
a similar issue, and found no way to update using the RRN as a key.
That discussion can be found at;

http://archive.midrange.com/midrange-l/201001/msg01044.html

My solution was to create two files, one containing the fixes and RRN
number of the line to fix, and a second that had a key equal to the
RRN. Then I could use an UPDATE command to fix the lines I needed to,
and the DELETE the lines containing ' 0%' at the start of the line.
I used the fixed file to replace the archived records for the
statements that had the issue.

Here are the statements I used;

File 1 - records that contain the fixed data:

create table FixFile1 as (

select rrn(a) as upd_line#, Char( substr(a.line,1,String_str - 1)
concat substr(c.line,string_str),185) as New_line 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 )

with data

File 2 - copy of the file to be fixed with added key based on RRN

Create table FixFile2 as ( select RowNumber() over (order by rrn(a) )
as RRN1, line from MyFile a ) with data

Update statement:

update FixFile2
set line = (select new_line from FixFile1
where rrn1 = upd_line# )
where exists (select 1 from FixFile1
where rrn1 = upd_line# )

I copied the FixFile2 back to the original file using *map and *drop
options, then replaced the statement lines for the last two months.
Testing extensively along the way.

Thanks again Chuck and Richard. I love people that help me improve my
code and SQL statements, so if you have comments, please post them.

Jim

On Wed, Mar 17, 2010 at 10:54 AM, CRPence <CRPbottle@xxxxxxxxx> wrote:


As an Amazon Associate we earn from qualifying purchases.

This thread ...

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.