Solved

oracle table data query

Posted on 2007-11-27
10
685 Views
Last Modified: 2013-12-18
Hi...i'm a little lost how to fix this....i have a table created by  a inexperienced programmer....the table has been created without  a sequence no or primary key..it has a entry date column but a lot of the data in it is date truncated....

I want to fix the table by adding a sequence no...how do i know the order of data in the table.....if i order it by rownum or rowid, it returns the data inconsistently....data with earlier entry dates appear later than data with later entry dates...is there any wany to retrive the data in the way order it entered the table?

i would'nt mond even moving all the data into another  table with a primary key, but i want to keep the order of the table intact..
0
Comment
Question by:hegde123
  • 2
  • 2
  • 2
  • +2
10 Comments
 
LVL 1

Accepted Solution

by:
arunyeshi2000 earned 168 total points
ID: 20362218
Try to print the entry date column value as a timestamp format.
"select date(col,'dd-mm-yyyy hh24:mi:ss") from <table> "

If it has a minutes and seconds with different values then you can order the table with a timestamp convertion.

Something like
insert into <new table> select * from <table> order by to_date(col,'dd-mm-yyyy hh24:mi:ss")
where <new table> structure is a replica of the old table.

Pls let me know if this solves ur problm.
0
 

Author Comment

by:hegde123
ID: 20362262
thanks...but unfortunately i have already tried that...there are records that have the same timestamp and some that are truncated to 00:00 hrs....so i cant say which record precedes which..


is there any other way in oracle to tell which record has entered a table first?
0
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 166 total points
ID: 20362861
>> i want to keep the order of the table intact..
As long as there are no sequence number columns/no date column with time; you dont have any order of data in the table.

The best you can do here is to make use of the date column in the table(but you have already stated that the date is truncated for some of the records).

You may add a sequence number column to your table and update it with a generated sequence number based on your date columns.

Something like:

SQL> select * from tbl1;

        ID DT
---------- ---------
           28-NOV-07
           29-NOV-07
           27-NOV-07
           26-NOV-07

SQL> update tbl1 t
  2  set id = ( select rn
  3             from (select rowid r_id , row_number() over (order by dt) rn
  4                   from tbl1 )
  5             where r_id = t.rowid)
  6  /

4 rows updated.

SQL> select * from tbl1;

        ID DT
---------- ---------
         3 28-NOV-07
         4 29-NOV-07
         2 27-NOV-07
         1 26-NOV-07
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20363243
It is not possible to find which record entered first and which entered after that and so on
because RDBMS like oracle does not care about the ordering of records in a table.

I think you have to do it manually if you want to do it accurately though it is a painful
activity because you have already told that you are not able to identify which record has come first and so on because there is no time portion in the date field.

can you tell us what other columns in the table do you have, so that we can help you to find some other column if any we can use for this. If you do not have any other way and you do not want to fix it manually, then try sujith80 solution to see if that is fine for you.

Thanks
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 18

Assisted Solution

by:Jinesh Kamdar
Jinesh Kamdar earned 166 total points
ID: 20366754
Folks, how about row-id? Wouldnt that help him sort the table in the way it was entered? I tried it for my table and it seemed to work!
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20366760
SELECT rowid, t.* FROM your_table_name t ORDER BY 1;
0
 

Author Comment

by:hegde123
ID: 20368631
i have tried rowid and it doesnt seem to do the trick either..thanks
0
 
LVL 27

Expert Comment

by:sujith80
ID: 20371977
>> how about rowid

Rowid doesnt ensure any order of data, as DMLs happens on the table, earlier rows may get deleted  and new rows may come in the same rowid.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now