Re: Date Time PS/SQL

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

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown