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



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.


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.