Home » Developer & Programmer » Forms » order by clause (Oracle 8i, Windows xp)
order by clause [message #315270] Sun, 20 April 2008 13:05 Go to next message
gargi_12300
Messages: 11
Registered: March 2008
Junior Member
Helloo
I need some help
actually have a table having attribute Citizen_id as primary key.
My citizen_id is of the form D001B001CTZ1,D001B001CTZ2,D001B001CTZ3,etc.
Now i want to arrange them in ascending order using order by clause.
It works fine until D001B001CTZ10 is reached.
AFter that it arranges as:
D001B001CTZ1
D001B001CTZ10
D001B001CTZ2
and so on...
please can suggest me how to prevent this and arrange to ids as D001B001CTZ1,D001B001CTZ2,.....D001B001CTZ10.
Re: order by clause [message #315285 is a reply to message #315270] Sun, 20 April 2008 15:47 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Ordering has been done correctly (as you are ordering CHARACTERS). You want to sort characters PLUS numbers. One way to do that is to split the ID do it like this:
SQL> WITH TEST AS
  2    (SELECT 'D001B001CTZ1' id FROM dual
  3     UNION
  4     SELECT 'D001B001CTZ2' id FROM dual
  5     UNION
  6     SELECT 'D001B001CTZ10' id FROM dual
  7     UNION
  8     SELECT 'D001B001CTZ3' id FROM dual
  9    )
 10  SELECT id FROM TEST
 11  ORDER BY SUBSTR(id, 1, 11), TO_NUMBER(SUBSTR(id, 12, 2));

ID
-------------
D001B001CTZ1
D001B001CTZ2
D001B001CTZ3
D001B001CTZ10

SQL>
Re: order by clause [message #315897 is a reply to message #315270] Wed, 23 April 2008 02:52 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
I put to you that the design of 'Citizen_id' is wrong. Instead of one field, it should have been stored in the database as four fields. You do NOT have to store data the same way that it appears on the screen or in a report.

David
Re: order by clause [message #315986 is a reply to message #315270] Wed, 23 April 2008 07:24 Go to previous messageGo to next message
deepakkrjain
Messages: 4
Registered: April 2007
Location: Delhi
Junior Member

hi used this

select substr('D001B001CTZ1',12) from dual;

or

select substr(<field_name>,12) from <table_name>
order by <field_name>;

after that u goted the last digit in accending order.

Please try it.



Regards.
Deepak
Re: order by clause [message #316073 is a reply to message #315897] Wed, 23 April 2008 11:58 Go to previous messageGo to next message
gargi_12300
Messages: 11
Registered: March 2008
Junior Member
Thanx a lot to all of you..
I got the solution....
Re: order by clause [message #316118 is a reply to message #316073] Wed, 23 April 2008 16:17 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Would you mind to post it so that other forum members might benefit from it?
Re: order by clause [message #316318 is a reply to message #316118] Thu, 24 April 2008 10:50 Go to previous message
gargi_12300
Messages: 11
Registered: March 2008
Junior Member
I followed your suggestion only....
Previous Topic: "unable to insert or update a view"
Next Topic: Migration from Forms 6i to Forms 10g
Goto Forum:
  


Current Time: Thu Dec 08 20:21:27 CST 2016

Total time taken to generate the page: 0.18767 seconds