Re: TO_CHAR and TO_DATE question

From: Dante <dnotari_at_my-deja.com>
Date: Tue, 08 Jun 1999 12:33:14 GMT
Message-ID: <7jj2ia$jj0$1@nnrp1.deja.com>

Mark,
 it is quite simple

 You are trying to insert something like 'JAN-99' into a  Date column which can't work.

 Or you change the datatype of your column to a varchar2 or   you create a valid date: eg.
   INSERT INTO coldatamonthly

     (ipaddr
     ,instance
     ,varid
     ,collecttime
     ,floatvalue)
    SELECT ipaddr
     ,instance
     ,varid
     ,to_date('01-' || to_char(collecttime,'Mon-yy', 'DD-MON-YY')
     ,round(avg(floatvalue))

    FROM coldata
    GROUP BY

     ipaddr
     ,instance
     ,varid
     ,to_date('01-' || to_char(collecttime,'Mon-yy', 'DD-MON-YY');

  One more technical thought:
  What you are doing looks to me like a typical DW like application.   I don't know which DB version you are using, but    Oracle supports snapshot which can do exactly what you are doing.    In addition you can specify refresh intervals wiht snapshots which    would cause them to refresh automatic (more or less)

  In 8.1 you can use materialized views and dimension which would    even result in an query rewrite if possible (instead of selecting    from the big table it would re-direct the select to a materialized    view containing the collapsed data)

  One more functional thought:
  In a DW environment you normally collapse your data into   data/hour. When you collapse it daily, you collapse it from    your data/hour-table into you data/day-table. If you go on    to do it weekly you go on collapsing from data/day into data/week    and so on.
  The benefit is an increased performance and reduced resourcelock   of your orginal table when collapsing.

Regards
Dante

In article <375CF4D3.5AD0C95C_at_comtech.com.au>,   Mark van Kerkwyk <kerkwyk_at_comtech.com.au> wrote:
> Hi,
> I am trying to create some summary tables for lots of collected
> data.
> For many devices , I collect a number of stats every 5 mins.
> I have created Hourly,Daily and Monthly tables
> Source table is COLDATA
> Hourly -> COLDATAHOURLY
> Daily -> COLDATADAILY
> Monthly->COLDATAMONTHLY
>
> I have made the following scripts to transfer the data from the master
> table into the summary tables.
>
> Hourly Script
>
> insert into coldatahourly

(ipaddr,instance,varid,collecttime,floatvalue)
> select
> ipaddr,instance,varid,to_char(collecttime,'dd-Mon-yy
> HH24'),round(avg(floatvalue))
> from coldata group by
> ipaddr,instance,varid,to_char(collecttime,'dd-Mon-yy HH24');
>
> Daily Script
>
> insert into coldatadaily

(ipaddr,instance,varid,collecttime,floatvalue)
> select
> ipaddr,instance,varid,to_char(collecttime,'dd-Mon-yy'),round(avg
(floatvalue))
>
> from coldata group by
> ipaddr,instance,varid,to_char(collecttime,'dd-Mon-yy');
>
> Monthly Script
>
> insert into coldatamonthly
> (ipaddr,instance,varid,collecttime,floatvalue) select
> ipaddr,instance,varid,to_char(collecttime,'Mon-yy'),round(avg
(floatvalue))
>
> from coldata group by
> ipaddr,instance,varid,to_char(collecttime,'Mon-yy');
>
> This works fine until I get to the Monthly one, where the sort order
> will have to be yyyy-Mon instead of Mon-yyyy, otherwise it won't group
> properly.
> The problem I have is that I am unable to get a TO_DATE working on the
> insert section of the statement, no matter what I do, I get an error
> back telling me that there is a comma missing, I have tried every
> combination of TO_DATE and keep getting the same error, if I can't
> format the date going in the insert, the statement fails telling me
that
> I am trying to insert an invalid date.
>
> The column in question here is named COLLECTTIME (DATE type), all
other
> columns are OK.
>
> Can anyone give me an example of how I might better create and run
these
> scripts and use the TO_DATE function to format the data coming into
the
> COLLECTTIME field
>
> Your help would be much appreciated.
>
> Mark :-)
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Jun 08 1999 - 07:33:14 CDT