Home » Developer & Programmer » Forms » Order by
Order by [message #325862] Mon, 09 June 2008 04:57 Go to next message
kuwait
Messages: 52
Registered: October 2007
Member
Hi,

I want to order a block according to a string field (code) and if its length is greater than 9 order it in different way

I used an if statement inside order by as follows, but it didn't work how can I do it please?

order by
   if(len(code)<=9)
      substr(code,5) desc,substr(code,1,3) desc
   else
      substr(code,7) desc,substr(code,1,5) desc
   end if


Thanks

[Updated on: Mon, 09 June 2008 05:01]

Report message to a moderator

Re: Order by [message #325877 is a reply to message #325862] Mon, 09 June 2008 05:49 Go to previous messageGo to next message
kuwait
Messages: 52
Registered: October 2007
Member
I tried that but it's not working

Order by(
   DECODE
      (len(code),9,
      (substr(code,5) desc,substr(code,1,3) desc),
      (substr(code,7) desc,substr(code,1,5) desc))
)
Re: Order by [message #325880 is a reply to message #325877] Mon, 09 June 2008 06:08 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
DECODE is OK, just move "DESC" keyword out of it, such as
order by decode(length(code), 1, substr(code, 5, 6),
                                 substr(code, 1, 2) 
               ) desc
Re: Order by [message #325885 is a reply to message #325880] Mon, 09 June 2008 06:56 Go to previous messageGo to next message
kuwait
Messages: 52
Registered: October 2007
Member
It's working but not ordered as I needed

Order by
(DECODE
   (length(code),9,
   ('substr(code,6,1) ,substr(code,1,3)'),
   ('substr(code,8,1) ,substr(code,1,3) ,substr(code,5,1)'))
) desc


I've a block and I want to order it by code field

the code length could be 9 or 11 chars with the following format

9 digits as
001-08/09
002-08/09

or 11 digits as
002/A-07/08
002/B-07/08

so the order is
002-08/09
001-08/09
002/B-07/08
002/A-07/08

How can I work it out?

thanks

[Updated on: Mon, 09 June 2008 06:59]

Report message to a moderator

Re: Order by [message #325888 is a reply to message #325885] Mon, 09 June 2008 07:20 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No Message Body
Re: Order by [message #325950 is a reply to message #325885] Mon, 09 June 2008 11:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
use case:
order by length(code)
,        case when length(code) = 9 
              then substr(code, 5)||code 
              else substr(code, 7)||code
         end



hmm.. starting db to test out the desc part..


[Edit: tested it]
SQL> create table faq(code varchar2(15));

Table created.

SQL> insert into faq values('001-08/09');

1 row created.

SQL> insert into faq values('002-08/09');

1 row created.

SQL> insert into faq values('002/A-07/08');

1 row created.

SQL> insert into faq values('002/B-07/08');

1 row created.

SQL> select *
  2  from   faq
  3  order  by length(code)
  4  ,      case when length(code) = 9
  5              then substr(code, 5)||code
  6              else substr(code, 7)||code
  7         end desc
  8  /

CODE
---------------
002-08/09
001-08/09
002/B-07/08
002/A-07/08

[Updated on: Mon, 09 June 2008 12:02]

Report message to a moderator

Re: Order by [message #325966 is a reply to message #325888] Mon, 09 June 2008 14:23 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Littlefoot wrote on Mon, 09 June 2008 14:20
No message body

Where the heck did my body go?!?

OK, once again: DECODE is just fine, no problem with it; after all, it does the same thing as CASE does (however, CASE is often easier to read and maintain).

The problem was in single quotes: you can't ORDER BY 'substr(code,6,1) ,substr(code,1,3)' because it is no longer a function result, but pure string. What you could have done was to concatenate result of SUBSTR function, such as
order by
  decode(length(code), 9, substr(code,6,1) || substr(code,1,3),
                          substr(code,8,1) || substr(code,1,3) || substr(code,5,1)
        ) desc
Re: Order by [message #325971 is a reply to message #325966] Mon, 09 June 2008 14:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
don't forget to order by length first (if I read the original poster's example correctly)
Re: Order by [message #325976 is a reply to message #325971] Mon, 09 June 2008 14:48 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes, you are right, Frank (at least, that's what an example in message #325885 suggests).
Re: Order by [message #326166 is a reply to message #325971] Tue, 10 June 2008 07:13 Go to previous message
kuwait
Messages: 52
Registered: October 2007
Member
Thank you All

I used Littlefoot's solution thank you it's working great
and thank you Frank for your help you gave me a lesson in how to use case in a select statement maybe I'll use it in the future.
Previous Topic: how to highlight a column in a multirecord database block
Next Topic: inastallation of d2k 6i
Goto Forum:
  


Current Time: Sun Dec 11 04:06:44 CST 2016

Total time taken to generate the page: 0.09356 seconds