Home » Developer & Programmer » Reports & Discoverer » Order by problem - please help
| Order by problem - please help [message #490934] |
Fri, 28 January 2011 03:53  |
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 #490994 is a reply to message #490983] |
Fri, 28 January 2011 06:55  |
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 #492347 is a reply to message #491053] |
Sat, 29 January 2011 15:11 |
 |
Littlefoot
Messages: 21823
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"):
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
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 1734 times)
|
|
|
|
| Re: Order by problem - please help [message #492349 is a reply to message #492347] |
Sun, 30 January 2011 00:09 |
 |
Barbara Boehmer
Messages: 9102
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>
|
|
|
|
Goto Forum:
Current Time: Mon Jan 06 19:11:26 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
