[sf-perl] Perl's to_date() equivalent

Loo, Peter # PHX Peter.Loo at source.wolterskluwer.com
Fri Sep 1 15:13:24 PDT 2006


Hi David,

The problem is that the database is Netezza.  Although Netezza will be
coming out with its own internal language, equivalent to, PL/SQL, 4GL,
etc..., it is currently not available.

The logic is quite simple really.  First I SELECT the required rows in
the correct sort order.  Then I calculate the dates within the records.
The problem is that when I fetch in a row, I have to compare the date of
the current row to a previous row then add days to the date accordingly.
Also there is a column that contains grace period of which I also need
to compare against the difference of the dates.

Here is the logic in pseudo code:

SELECT statement:
=================

select   distinct x.ptnt_gid, x.prdct_gid, x.rx_fill_dte,
                  x.claim_gid, x.ovrd_days_sply_nbr,
                  x.clfsn_grace_prd_nbr, 'N',
                  x.rx_fill_dte + x.ovrd_days_sply_nbr
from     p_dlvrb_rx_claim_extract x, p_dlvrb_ptnt_list y
where    x.dlvrb_gid = y.dlvrb_gid
and      x.ptnt_gid = y.ptnt_gid
order by x.ptnt_gid, x.prdct_gid, x.rx_fill_dte, x.claim_gid

WHILE loop:
===========
 
while (fetch) {
  if (previous cted - current rx_fill_dte) >= 0 or 
     ((previous cted - current rx_fill_dte) < 0 and 
      (previous cted - current rx_fill_dte) <= current
clfsn_grace_prd_nbr)
  then
    current cted = previous cted + rx_fill_dte
    current class = "C"
  elsif (previous cted - current rx_fill_dte) < 0 and
        (previous cted - current rx_fill_dte) > current
clfsn_grace_prd_nbr
  then
    current cted = rx_fill_dte + ovrd_days_sply_nbr
    current class = "R"
  else
    current cted = rx_fill_dte + ovrd_days_sply_nbr 
    current class = "N"
  fi


 
Peter

-----Original Message-----
From: sanfrancisco-pm-bounces+peter.loo=source.wolterskluwer.com at pm.org
[mailto:sanfrancisco-pm-bounces+peter.loo=source.wolterskluwer.com at pm.or
g] On Behalf Of David Fetter
Sent: Friday, September 01, 2006 2:59 PM
To: San Francisco Perl Mongers User Group
Subject: Re: [sf-perl] Perl's to_date() equivalent

On Fri, Sep 01, 2006 at 02:42:00PM -0700, Loo, Peter # PHX wrote:
> 
> Hellow there.
> 
> Per Andy's recommendation, I went ahead and had DataTime.pm installed 
> on our test node.  It turned out that the date calculation is taking 
> approximately 11 seconds for every 5,000 records.  Is there something 
> a bit faster?  Here is my code:

Are you sure you can't do this with the DBMS's facilities?  It seems to
me that Perl's are what you use when you don't have a DBMS date library
available.  Also, could you explain in english what the code is supposed
to be doing?

Cheers,
D



More information about the SanFrancisco-pm mailing list