Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Creating IDs subsequently

Re: Creating IDs subsequently

From: Ed prochak <ed.prochak_at_magicinterface.com>
Date: 23 Jul 2003 05:23:14 -0700
Message-ID: <4b5394b2.0307230423.199d55f1@posting.google.com>


"Gerd Forster" <gf_at_nospam.at> wrote in message news:<bfiu6q$f83qv$1_at_ID-119152.news.uni-berlin.de>...
> "Mark D Powell" <Mark.Powell_at_eds.com> schrieb im Newsbeitrag
> news:2687bb95.0307210545.24641c9b_at_posting.google.com...
> > "Gerd Forster" <gf_at_nospam.at> wrote in message
> news:<bfgek8$e117p$1_at_ID-119152.news.uni-berlin.de>...
> > > Hello everybody,
> > >
> > > I have a table with timestamp and other information.
> > > Recently I added a numeric ID column.
> > >
> > > I want to fill in IDs in CHRONOLOGIC ORDER.
> > >
> > > Is this possible by an update statement, or do I have to write
> > > a procedure?
> > >
> > > Thanks in advance
> > > Gerd Forster
> >
> > Gerd, this is from the version 9.2 DBA Admin manual, "Sequences are
> > database objects from which multiple users can generate unique
> > integers." There is a chapter on managing them in the Admin manual
> > and it refer readers to the Concepts manual for more information.
> > Perhaps a sequence generator (create sequence command in SQL manual)
> > will fit your bill.
> >
> > If you want true CHRONOLOGIC ORDER what was wrong with using the
> > timestamp as the key?
> >
> > HTH -- Mark D Powell --
>
> Hi Mark,
>
> I'm sorry, I did not mention that I am Using Oracle 8.
> And there is only the DATE type and SYSDATE, which is not able to
> generate unique keys.
> So "chronolgic order" is only an approximation.
>
> Thank you very much for your posting!
> Gerd Forster

If you think the DATE data type only contains the date information and not the time, then you are mistaken. The DATE data type records time differences down to (IIRC) about 100milliseconds. Or are you saying your data is chronologically closer than that?

Didn't you say that the original data has a timestamp. So how was it populated? (Now is when you'll tell us that this "timestamp" is actually a character field and you only stored the date.)

Really I do not see what your problem is. Make a procedure to SELECT * FROM yourtable ORDER BY yourtimestamp;

set a counter
for each row fetched
 UPDATE yourtable SET yournewid = counter;  increment counter;
end loop

That will work if timestamp is of type DATE and it really contains the chronological values that the name implies.

IOW, to answer your original question, YES YOU MUST WRITE A PROCEDURE. Received on Wed Jul 23 2003 - 07:23:14 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US