MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » June 2008

Re: SQL guru question



fixed

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





Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot 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 here. If you have questions about this, please contact