On 05-Nov-2014 11:33 -0600, Rick Mason wrote:
I just found out a tax file will need be updated to correct an
incorrect date field.
I have not used DFU that much.. I know I can change specific records
in a file via the record number
IIRC the DFU can be used to assist in changing records by key instead
of by RRN, by naming a keyed LF instead of the [non-keyed] PF.? I do
not recall if the DFU allows updating a shared Open Data Path (ODP); I
think not, and a quick test seems to confirm that. If the set of rows
to be changed can be defined by an SQL SELECT in an update-capable VIEW,
then as I recall the DFU has no problem operating against any VIEW that
would be supported by /native/ Row-Level-Access (RLA); a quick test
seems to confirm that.
but how can DFU change a date field in 15,000 records in this file.
The DFU can assist to change records either page-at-a-time or a
row-at-a-time. As an interactive utility that presents one or more rows
for update, the DFU Update Data (UPDDTA) is a poor choice for updating
large _sets_ of data; doing so requires /rolling/ through every row of
data that must be changed.
Of a user-written program and a more generic [perhaps better
described as more feature-rich] ad-hoc data-update feature than the DFU,
either would probably be a better choice than the DFU. The SQL is a
language that either can be used to write such a program [optionally as
embedded in a host language] or be used to formulate statements that
then can be passed to any utility that accepts and performs those SQL
statements. For the given scenario, either an UPDATE statement or an
embedded UPDATE with the WHERE CURRENT OF clause issued against selected
data.
Whatever predicates can define which are the 15k rows that need
updating can select those records for update; i.e. to subset the data to
just those rows requiring correction. One of the two following
predicates, as examples, might be similar to what is required to effect
that subset for the given scenario:
• all rows between [inclusive] RRN-1001 and RRN-16000
• all rows for REGION='UK'
What remains after the selection however, is the expression\algorithm
that would effect the necessary update\correction to the incorrect date
field. If the error with the dates are consistently set one year beyond
what is correct, then a simple expression to reset the data might be
described by:
date_field=date_field minus one year
If the dates are not consistent across the entire subset for the
required change, then the /expression/ is more complex and might be
described as an effective /algorithm/, though possibly still written as
a single expression [using the SQL; typically a CASE expression];
perhaps as described by:
date_field = case REGION
when 'UK' then date_field minus one year
when 'US' then date_field minus one month
...
midrange.com
