Re: Date Time PS/SQL

Brian Peasland wrote:
> swaxolez wrote:
> > I'm sure this is a very obvious solution but I'm having trouble
> > organizing this problem.
> >
> > I have a database with date/time field.
> >
> > Each record looks like this:
> >
> > Scanned_DateTime,Passkey,Last_Scanned_DateTime,TimeDifference
> >
> > When I import these records using sql*loader I assume I can set up a
> > trigger and procedure to go through the whole database (to compare
> > against previous records already stored there)
> > and compute the two fields listed above "Last_Scanned_DateTime" and
> > "TimeDifference" as they apply to each unique passkey. So that each
> > record will contain the time the passkey was intially scanned and the
> > last time and time difference of the previous scan.
> >
> > Is there a faster way then doing a sequential scan from beginning to
> > end of the entire database to achieve this? I expect this database to
> > contain millions of records. I'm just beginning Oracle and am not
> > quite sure on the PS/SQL coding.
> >
> > Any help or pointers would be appreciated.
> >
>
>
> The TimeDifference is a computed value. Computed values are not
> typically stored in a table as you will compute them on the fly when
> querying the data. If TimeDifference = Last_Scanned_DateTime -
> Scanned_DateTime then do not store this value as you can compute this
> value when querying as follows:
>
> SELECT Scanned_DateTime,Passkey,Last_Scanned_DateTime,
> Last_Scanned_DateTime - Scanned_DateTime AS TimeDifference
> FROM my_table;
>
> So you do not need a trigger to populate this column.
>
> HTH,
> Brian
>

swaxolez, if I understand the problem correctly at the time of insert the only data known is the passkey and the scanned_datetime. This is actually the only columns you need to define in your table. You can use a view to calculate the other two columns when the data is retrieved as the Last_Scanned_DateTime is actually just the Scanned_DateTime of the prior row for the same Passkey based on Scanned_DateTime ordering. You can easily use an analytic function such as lead or lag to calculate this time per Passkey.

If you must store this information then you should select the max(Scanned_DateTime) for a Passkey value and use this row to provide the Last_Scanned_DateTime and in the calculation of the TimeDifference.  Again as Brian pointed out there is no need to store the calculated value.

HTH -- Mark D Powell -- Received on Wed Jul 26 2006 - 09:31:51 CDT