Solved

oracle table data query

Posted on 2007-11-27
10
689 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle Database Upgrade 13 60
Migrate database to ASM disks. 1 39
Schema creation in Oracle12c 6 38
join 2 views with 5 conditions 3 42
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

930 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

12 Experts available now in Live!

Get 1:1 Help Now