Given that you say this is an old system, are there any fields with the record that may be redundant/no longer in use, which could be used to hold a date?
Alternately, can you build a file, keyed to the original file, for the purpose of holding a date?
Might be an option.
Norm Dennis
----- Reply message -----
From: "CRPence" <CRPbottle@xxxxxxxxx>
To: <rpg400-l@xxxxxxxxxxxx>
Subject: Get Record Creation Date
Date: Mon, Jan 6, 2014 03:22
On 05-Jan-2014 08:17 -0800, William Salim wrote:
<<SNIP>> The program is in batch, runs at 00.30 am. Problem happened
when new year comes, reading from the main file that sort by the
month+day (no other keys).
When new year comes and program executed, it will be many
transactions duplicated the key month+day it could be from 5 years or
more. <<SNIP>>
Curious. Why has there not been the same issue from any of the prior
years? Or is this problem only since this new year because all data is
from just the one prior year [i.e. 2013]? If the latter, easy enough to
create a new database file that has the keyed values with the old-year
and the new-year; apply the trigger to update the new database table.
Or possibly do the same thing with copying the current year at the
end of each year [instead of synchronous (scheduling of asynchronous)
maintenance for each row, as effected with a trigger]. The report
program would then use an OUTER JOIN and the date values from the new
file would be the NULL value [generated as default rows] so the value
would be defined by an expression such as IFNULL() to produce the data
from the YEAR(CURRENT_DATE).
Given, existing table:
create table CurDBF
(MM numeric(2), DD numeric(2), keyFld1...keyFldn)
;
insert into CurDBF values(11, 27), (12, 25), (12, 31), (01, 04)
; -- and can somehow properly be inferred that the 01.04 value
-- is this year whereas all prior rows are the previous year
Then:
create table CurDBFj
(DT DATE, keyFld1...keyFldn) /* primary key if appropriate */
; -- add a CREATE INDEX if primary key not an option
insert into CurDBFj
select
TODATE('2013' concat digits(MM) concat digits(DD), 'YYYYMMDD')
,keyFld1
...
,keyFldN
from CurDBF
where ... -- select, e.g. by RRN(CurDBF) which are from 2013
; -- repeat the same request, but for 2014 data if adding trigger
-- or if without a trigger, then instead, for each year since
-- 2013 populated above, repeat for each full-year data using a
-- JOIN or an EXCEPT query, run at the end-of-year
Reporting:
select X.*
from CurDBF as X
left outer join
CurDBFj as J
using (keyFld1...keyFldn)
order by
J.DT -- if using maintained data in CurDBFj via insert trigger
-- else if using EOY batch update to populate each year
IfNull(J.DT
, TODATE( digits(dec(year(current_date), 4))
concat digits(MM) concat digits(DD), 'YYYYMMDD')
)
The query for /reporting/ could be encapsulated in a VIEW. If the
program requires a key, then a DDS join can be used, or an OPNQRYF ODP
using KEYFLD(). The join could be INNER if using maintained data, but
that would lose rows if unmatched [e.g. due to improperly maintained
data]. The DDS join with join-default for either a NULL value or a
default value could calculate the desired value if not defined
as-desired in the file itself; i.e. a DDS join can not evaluate the
expression as can the [query in the] VIEW or an OPNQRYF.
midrange.com
