OraFAQ Forum: Forms

Home » Developer & Programmer » Forms » Order by

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Order by [message #325862]

Mon, 09 June 2008 04:57

kuwait
Messages: 55
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

kuwait
Messages: 55
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))
)

Report message to a moderator

Re: Order by [message #325880 is a reply to message #325877]

Mon, 09 June 2008 06:08

Littlefoot
Messages: 21823
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

Report message to a moderator

Re: Order by [message #325885 is a reply to message #325880]

Mon, 09 June 2008 06:56

kuwait
Messages: 55
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

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

No Message Body

Report message to a moderator

Re: Order by [message #325950 is a reply to message #325885]

Mon, 09 June 2008 11:52

Frank
Messages: 7901
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

Littlefoot
Messages: 21823
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

Report message to a moderator

Re: Order by [message #325971 is a reply to message #325966]

Mon, 09 June 2008 14:42

Frank
Messages: 7901
Registered: March 2000

Senior Member

don't forget to order by length first (if I read the original poster's example correctly)

Report message to a moderator

Re: Order by [message #325976 is a reply to message #325971]

Mon, 09 June 2008 14:48

Littlefoot
Messages: 21823
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).

Report message to a moderator

Re: Order by [message #326166 is a reply to message #325971]

Tue, 10 June 2008 07:13

kuwait
Messages: 55
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.

Report message to a moderator

Previous Topic:	how to highlight a column in a multirecord database block
Next Topic:	inastallation of d2k 6i

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 09 06:03:44 CST 2025