Norbut, Jim wrote:
> <<SNIP>>
> What I am looking to do is automate a daily process that says
> something like:
> Insert into HCARCHIVE Only the records from headcount
> if max(filedate) in headcount > max(filedate) in hcarchive
>
> So that way the hcarchive file gets updated <ed: rows added>
> with the latest data automatically, w/o me having to remember
> to do it.
> Hoping there is some sort of conversion to take a text like that
> <ed: date & time> And convert it into a date that SQL will like.
>
> I've been dabbling with CASE statements...and views...but not
> making much headway.
I infer the request is to perform daily batch copy\replication of:
Insert into HCARCHIVE
select * from HEADCOUNT
where filedate > (select max(filedate)
from hcarchive )
-- filedate as character string in its current form is
-- ineligible for the selection by the greater than test,
-- so its value must be transformed to allow selection
Obviously redefining the /filedate/ field to enable collation and
selection without requiring transformations would be the better
approach, but...
Assuming the date\time information is /fixed/ for the positions in
the given format [Mmm dd YYYY hh:mmXM], then I think the expression for
the TIMESTAMP function in the following SQL will suffice. That
expression could be implemented instead, as a UDF. The given expression
generates the /14–character form 'yyyymmddhhmmss'/ for the first
argument of the TIMESTAMP(). A variation on that could use the second
argument as the time and the first as the date, but then separators must
be added, which may be prettier than removing the time separators with
the replace built-in.?
http://publib.boulder.ibm.com/infocenter/systems/scope/i5os/topic/db2/rbafztsstrings.htm
The TIME() is built from the data as an IBM USA standard form, which
should allow either of the forms "01:13 PM" or "1:13 PM", such that the
use of DIGITS(DECIMAL(xx,,)) is not required like it is for the day of
the month.
http://publib.boulder.ibm.com/infocenter/systems/scope/i5os/topic/db2/rbafztimestrings.htm
I can not test the syntax nor results, but here is a script that sets
up the tables and a row to archive, and three ways to make the copy
happen but with the same expression in each, to transform the data for
collation & selection:
<code>
create table HEADCOUNT
(FILEDATE CHAR(26)
,EMPLOYEEID CHAR(41)
,EMPLOYEESTATUS CHAR(50)
,LASTNAME CHAR(30)
) RcdFmt HC
;
create table HCARCHIVE /* the same as above */
like HEADCOUNT
;
-- This row would be added to the archive file if the
-- date\time is later than the latest date\time in the
-- archive file
insert into HeadCount values(
,'Jun 5 2008 11:36PM '
,'110886 '
,'Active '
,'Wong '
)
;
-- Need a row in the archive to enable comparison; as an
-- older date, the newer date\time can be archived
insert into HCArchive values
('Jun 4 2008 01:00PM',,'110886','Asleep','Wong')
;
-- The following performs the selection using a subquery
-- for the greater-than test; the correlation identifiers
-- H & S are included to emphasize no correlation; these
-- identifiers should be optional
insert into HCArchive
select * from HeadCount H
where
timestamp(
substr(H.filedate, 8, 4) concat
digits(decimal(substr(H.filedate, 5, 2), 2, 0)) concat
case upper(substr(H.filedate, 1, 3))
when 'JAN' then '01'
... /* add missing months */
when 'DEC' then '12'
end concat
replace(char(time(substr(H.filedate, 13, 5) concat
' ' concat substr(H.filedate, 18, 2)),*ISO),'.','')
)
> (select max(
timestamp(
substr(S.filedate, 8, 4) concat
digits(decimal(substr(S.filedate, 5, 2), 2, 0)) concat
case upper(substr(S.filedate, 1, 3))
when 'JAN' then '01'
... /* add missing months */
when 'DEC' then '12'
end concat
replace(char(time(substr(S.filedate, 13, 5) concat
' ' concat substr(S.filedate, 18, 2)),*ISO),'.','')
) )
from HCArchive S
)
;
-- The following explicitly places the maximum value into a
-- temporary table [CTE] before referencing that single-row
-- table in the WHERE for the full-select of the INSERT.
insert into HCArchive
with
HCAmax (HCAmax) as /* Generate Max T/S in temp table */
(select max(
timestamp(
substr(filedate, 8, 4) concat
digits(decimal(substr(filedate, 5, 2), 2, 0)) concat
case upper(substr(filedate, 1, 3))
when 'JAN' then '01'
... /* add missing months */
when 'DEC' then '12'
end concat
replace(char(time(substr(filedate, 13, 5) concat
' ' concat substr(filedate, 18, 2)),*ISO),'.','')
) )
from HCArchive
)
select * from HeadCount
where
timestamp(
substr(H.filedate, 8, 4) concat
digits(decimal(substr(filedate, 5, 2), 2, 0)) concat
case upper(substr(filedate, 1, 3))
when 'JAN' then '01'
... /* add missing months */
when 'DEC' then '12'
end concat
replace(char(time(substr(filedate, 13, 5) concat
' ' concat substr(filedate, 18, 2)),*ISO),'.','')
)
> (select HCAmax
from HCAmax
)
;
-- The following enables conversion of the 'Mmm dd hh:mmxM'
-- form into a timestamp, as a UDF
drop function QGPL .MmmDTtoTS
;
Create function QGPL .MmmDTtoTS (filedate CHAR(26))
/* if input is literal versus "filedate" field, */
/* then best to change the parm type to VARCHAR */
Returns TIMESTAMP
LANGUAGE SQL Is Deterministic Returns NULL on NULL input
SET OPTION DBGVIEW = *STMT
BEGIN
RETURN
timestamp(
substr(filedate, 8, 4) concat
digits(decimal(substr(filedate, 5, 2), 2, 0)) concat
case upper(substr(filedate, 1, 3))
when 'JAN' then '01'
... /* add missing months */
when 'DEC' then '12'
end concat
replace(char(time(substr(filedate, 13, 5) concat
' ' concat substr(filedate, 18, 2)),*ISO),'.','')
);
END
;
-- The following uses the user defined function with
-- the subquery for a /prettier/ SQL select
Insert into HCARCHIVE
select * from HEADCOUNT
where qgpl.MmmDTtoTS(filedate)
> (select max(qgpl.MmmDTtoTS(filedate))
from HCARCHIVE )
;
</code>
Regards, Chuck
midrange.com
