Finding order where there is none 
Author Message
 Finding order where there is none

Hello,

I've just been asked to help with a process that takes a bunch of log
records from a table that show ID changes for records in another table.
For example, in Table1 we have an ID field and several other fields. In
Table2 we have OldID, NewID and ChangeDate. The process must map the
beginning ID with the final ID, so if we have:

OldID    NewID    ChangeDate
1        5        990101
5        7        000516

then we would map ID 1 to ID 7. The problem is that the ChangeDate is
exactly as you see above... a char(6) field in YYMMDD format. It's also
possible that two changes take place on the same date. Furthermore,
it's possible that IDs are reused. (I didn't design this system!)

Here's a possible scenario:

OldID    NewID    ChangeDate
1        5        990101
5        1        990101

Given the ordering above, it's easy to see what has happened. ID 1 got
changed to 5 and then it was changed back. Unfortunately, when creating
a cursor to process this data there is no way to guarantee that the
ordering will be correct, and if we reverse the order the algorithm for
stepping through the changes gets thrown off completely.

Any ideas on how I might determine the mapping? Is there any way to
order a bunch of records by the date and time that they were added even
though their is no field that shows that? I don't think so, but it's
worth asking I guess.

   Thanks for any help!
         -Tom.

Sent via Deja.com http://www.***.com/
Before you buy.



Fri, 07 Mar 2003 03:00:00 GMT
 Finding order where there is none

As you've already gathered, there is no way to guarantee the ordering of
rows unless you have an ORDER BY clause and you cannot, of course, order by
a non-existent column.

I suggest you add either an identity column or an datetime column containing
a sufficient time resolution.  If this is not possible and there is no
clustered index on the log table and rows are never updated or deleted, the
physical sequence may be the same as the entry sequence.  Consequently, you
*might* be able to get away with something like:

SELECT IDENTITY(int) AS ID, *
INTO #Table2
FROM Table2 (INDEX(0))

This will allow you to order by the generated identity value in the temp
table and ignore the ChangeDate for ordering purposes.

Hope this helps.



Quote:
> Hello,

> I've just been asked to help with a process that takes a bunch of log
> records from a table that show ID changes for records in another table.
> For example, in Table1 we have an ID field and several other fields. In
> Table2 we have OldID, NewID and ChangeDate. The process must map the
> beginning ID with the final ID, so if we have:

> OldID    NewID    ChangeDate
> 1        5        990101
> 5        7        000516

> then we would map ID 1 to ID 7. The problem is that the ChangeDate is
> exactly as you see above... a char(6) field in YYMMDD format. It's also
> possible that two changes take place on the same date. Furthermore,
> it's possible that IDs are reused. (I didn't design this system!)

> Here's a possible scenario:

> OldID    NewID    ChangeDate
> 1        5        990101
> 5        1        990101

> Given the ordering above, it's easy to see what has happened. ID 1 got
> changed to 5 and then it was changed back. Unfortunately, when creating
> a cursor to process this data there is no way to guarantee that the
> ordering will be correct, and if we reverse the order the algorithm for
> stepping through the changes gets thrown off completely.

> Any ideas on how I might determine the mapping? Is there any way to
> order a bunch of records by the date and time that they were added even
> though their is no field that shows that? I don't think so, but it's
> worth asking I guess.

>    Thanks for any help!
>          -Tom.

> Sent via Deja.com http://www.deja.com/
> Before you buy.



Fri, 07 Mar 2003 03:00:00 GMT
 Finding order where there is none
Thanks for the reply. Inserting into a temporary table is what we were
doing, but as you mentioned and as I told the developer, there's no
guarantee that the ordering will be correct even if it turns out that
way most of the time. Luckily, it looks like another column, which I
was told was a randomly generated ID number is actually a sequential ID
number. Hopefully that's the case and we can use that.

   Thanks again,
       -Tom.



Quote:
> As you've already gathered, there is no way to guarantee the ordering
of
> rows unless you have an ORDER BY clause and you cannot, of course,
order by
> a non-existent column.

> I suggest you add either an identity column or an datetime column
containing
> a sufficient time resolution.  If this is not possible and there is no
> clustered index on the log table and rows are never updated or
deleted, the
> physical sequence may be the same as the entry sequence.
Consequently, you
> *might* be able to get away with something like:

> SELECT IDENTITY(int) AS ID, *
> INTO #Table2
> FROM Table2 (INDEX(0))

> This will allow you to order by the generated identity value in the
temp
> table and ignore the ChangeDate for ordering purposes.

> Hope this helps.



> > Hello,

> > I've just been asked to help with a process that takes a bunch of
log
> > records from a table that show ID changes for records in another
table.
> > For example, in Table1 we have an ID field and several other
fields. In
> > Table2 we have OldID, NewID and ChangeDate. The process must map the
> > beginning ID with the final ID, so if we have:

> > OldID    NewID    ChangeDate
> > 1        5        990101
> > 5        7        000516

> > then we would map ID 1 to ID 7. The problem is that the ChangeDate
is
> > exactly as you see above... a char(6) field in YYMMDD format. It's
also
> > possible that two changes take place on the same date. Furthermore,
> > it's possible that IDs are reused. (I didn't design this system!)

> > Here's a possible scenario:

> > OldID    NewID    ChangeDate
> > 1        5        990101
> > 5        1        990101

> > Given the ordering above, it's easy to see what has happened. ID 1
got
> > changed to 5 and then it was changed back. Unfortunately, when
creating
> > a cursor to process this data there is no way to guarantee that the
> > ordering will be correct, and if we reverse the order the algorithm
for
> > stepping through the changes gets thrown off completely.

> > Any ideas on how I might determine the mapping? Is there any way to
> > order a bunch of records by the date and time that they were added
even
> > though their is no field that shows that? I don't think so, but it's
> > worth asking I guess.

> >    Thanks for any help!
> >          -Tom.

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.


Sat, 08 Mar 2003 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Project builder finds error where there is none!

2. Hi,I am newbie,and want to find solution in NT 4.0 with samba

3. HI,I am newbie,and want to find solution between NT 4.0 and samba

4. HI,I am newbie,and want to find solution between NT 4.0 and samba

5. HI,I am newbie,and want to find solution between NT 4.0 and samba

6. I am a dumb, can't find enterprise manager

7. How do I find out what database I am in

8. I am getting this message when i am tring to export or import anything using

9. error ORA-01855: AM/A.M. or PM/P.M. required

10. Busy Day = Slowdown from 12 AM - 5 AM

11. Use of @am, Am I dumb?

12. Find First, Find Next, Find Previous, Find Last


 
Powered by phpBB® Forum Software