Solved

oracle table data query

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

856 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