Home » Developer & Programmer » Reports & Discoverer » Order by problem - please help
Order by problem - please help [message #490934] Fri, 28 January 2011 03:53 Go to next message
ahazin
Messages: 39
Registered: August 2010
Member
Hi guys. In my report I am experiancing quite a big problem and I do not know how to fix it. One of my columns consists of room numbers ie: room 1, room 2, these can range from 1 - 99. Now, it is very important that I am able to order these rooms, howver when i try to order them, as you may imagine it goes like

just take these following data examples. The way I have wrote them is the current way in which they are displayed:


conference room 1
conference room 11
conference room 2
dinning room 1
dinning room 19
dinning room 2
room 1
room 11
room 2
room 21
room 22

But I want them to be displayed as the following when i decide to order by room:


conference room 1
conference room 2
conference room 11
dinning room 1
dinning room 2
dinning room 19
room 1
room 2
room 11
room 21
room 22

The data output will vary depending on parameters passed into it so it may not always be the above data.

Is there anyway to get round this problem? if someone could help me out I would really appreciate it.

Thanks.
Re: Order by problem - please help [message #490938 is a reply to message #490934] Fri, 28 January 2011 04:04 Go to previous messageGo to next message
Michel Cadot
Messages: 67222
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
order by col, to_number(substr(col,instr(col,' ',-1)+1))

Regards
Michel
Re: Order by problem - please help [message #490957 is a reply to message #490938] Fri, 28 January 2011 04:56 Go to previous messageGo to next message
ahazin
Messages: 39
Registered: August 2010
Member
Thanks very much for your help but I seem to be getting the error ora - 01722 invalid number.

Is this error due to the fact that there is more than 1 space ie in conference room 1?

Do you have any idea why this could be? sorry im quite new to this.

Thanks again.

[Updated on: Fri, 28 January 2011 05:00]

Report message to a moderator

Re: Order by problem - please help [message #490983 is a reply to message #490957] Fri, 28 January 2011 06:25 Go to previous messageGo to next message
Littlefoot
Messages: 21572
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Go to SQL*Plus and run
SELECT substr(col,instr(col,' ',-1)+1) FROM your_table
as it appears that query - for some records - returned a CHARACTER instead of a NUMBER.
Re: Order by problem - please help [message #490994 is a reply to message #490983] Fri, 28 January 2011 06:55 Go to previous messageGo to next message
ahazin
Messages: 39
Registered: August 2010
Member
Ahh thanks, I see the problem now, some rooms dont alwys contain a number, ie could just be 'staff room' etc whilst others could be 'staff cantene 1 table 4'.

I guess doing what I want isnt possible with my data range? or is it?

Thanks very much for your help so far.
Re: Order by problem - please help [message #491053 is a reply to message #490994] Fri, 28 January 2011 12:38 Go to previous messageGo to next message
Littlefoot
Messages: 21572
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Detect the last character (using SUBSTR). If it is a numeric, fine. If not, omit it from sorting. You might also need DECODE or CASE to do that.
Re: Order by problem - please help [message #492347 is a reply to message #491053] Sat, 29 January 2011 15:11 Go to previous messageGo to next message
Littlefoot
Messages: 21572
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK, here's one way to do that. Image first - left is the "original", and on the right side is the result ordered by room numbers (suppose that a room with no number is, actually a "room 0"):

/forum/fa/8653/0/

This is a query:
SQL> select room
  2  from test
  3  order by
  4    trim(substr(room, 1, decode(instr(room, ' ', -1), 0, length(room), instr(room, ' ', -1)))),
  5    case when ascii(substr(room, -1)) between 48 and 57 then
  6              to_number(substr(room, -2))
  7         else 0
  8    end;

What does it do?

Line 4: selects room name. It searches the original room name for a space character; "-1" means "search backwards". TRIM removes blanks.

Line 5: CASE: SUBSTR checks the last character ("-1"). If it is a number (ASCII between 48 and 57):
SQL> select ascii(0), ascii(1), ascii(9) from dual;

  ASCII(0)   ASCII(1)   ASCII(9)
---------- ---------- ----------
        48         49         57
then let a room number be exactly what it is. Otherwise (ELSE) make it the "first" room (number 0).


Another option, which is prettier and easier to follow, uses regular expressions. Query and the result looks like this:
SQL> select room
  2  from test
  3  order by
  4    regexp_substr(room, '[[:alpha:][:blank:]]+'),
  5    to_number(regexp_substr(room, '[[:digit:]]+'));

ROOM
--------------------
conference 1
conference 2
conference 11
conference room 3
dining 1
dining 2
dining 19
living
room
room 1
room 2
room 11
room 21
room 22

What does it do?

Line 4: selects room name, having letters (ALPHA) and spaces (BLANK).

Line 5: takes room name's numeric (DIGIT) part. As the result is a string, we need TO_NUMBER.

Although this solution doesn't look bad, its drawback might be the fact that your Report doesn't understand regular expressions. If that's the case, you still might try to create a VIEW that looks the same as the above query, and then base report's query on that view. If your database version doesn't support regular expressions, you're out of luck.


I suppose that there's another, prettier way to do the job - feel free to experiment.
  • Attachment: order_by.PNG
    (Size: 6.06KB, Downloaded 387 times)
Re: Order by problem - please help [message #492349 is a reply to message #492347] Sun, 30 January 2011 00:09 Go to previous message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
There are always various ways to do things. The following allows for your sample data with room number and table number. It does so by separating the character portion, first number, and last number. It gets the characters by replacing all digits with spaces. It gets the numbers by first replacing all letters with spaces, then using substr and instr. Depending on your data, you may need to add other characters. You could also resort to a user-defined function if you have a lot of special cases that need to be handled.

SCOTT@orcl_11gR2> select room
  2  from   (select room,
  3  		    ltrim (rtrim (translate (room, ' 1234567890', ' '))) room_char,
  4  		    ltrim (translate (lower (room), ' abcdefghijklmnopqrstuvwxyz', ' ')) || ' ' room_num
  5  	     from   test)
  6  order  by room_char,
  7  	       to_number (nvl (substr (room_num, 1, instr (room_num, ' ') - 1), 0)),
  8  	       to_number (nvl (substr (rtrim (room_num), instr (rtrim (room_num), ' ', -1) + 1), 0))
  9  /

ROOM
-------------------------
conference room 1
conference room 2
conference room 3
conference room 11
dinning room 1
dinning room 2
dinning room 19
living
room
room 1
room 2
room 11
room 21
room 22
staff cantene
staff cantene 1 table 2
staff cantene 1 table 4
staff cantene 2 table 3
staff room

19 rows selected.

SCOTT@orcl_11gR2>

Previous Topic: Blank space between name and address
Next Topic: Master Detail Report, If there is no record in detail table
Goto Forum:
  


Current Time: Thu Jul 02 10:44:58 CDT 2020