Sorting Problem 3

[sim133]

Hi all,
I am trying to query a table and sort it by the material Name. I want the unknown, Other and None Material lists to show up at the last. Is there any way to do this? I appreciate it.
Thanks

 

[Dagon]

Use a decode on the sort:

order by decode(material_name, 'Unknown', 'ZZZZ9',
'Other', 'ZZZZ8', 'None Material', 'ZZZZ7', material_name)

 

[SantaMufasa]

Sim,

Yes, you can achieve this. In the example, below, I'm presuming that "MATERIAL_NAME" is the only column upon which you are sorting. Also, I'm presuming that you want the values of MATERIAL_NAME to be sorted in the following groups:

Group 2: "unknown",
Group 3: "Other",
Group 4: "None"
Group 1: Actual value, alphabetically, if not in Group 2-4.

Here is the ORDER BY that you could use:

...ORDER BY DECODE(UPPER(MATERIAL)
    ,'UNKNOWN',2
    ,'OTHER',3
    ,'NONE',4
    ,UPPER(MATERIAL)
    );

Let us know if this generates the results that you want.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Dagon]

I think that might create problems with the data type because you are mixing numeric and non-numeric values in the decode:

drop table material;

CREATE TABLE material (material VARCHAR2(50));

INSERT INTO material VALUES ('OTHER');

INSERT INTO material VALUES ('XXXX');

select * from material 
ORDER BY DECODE(UPPER(MATERIAL),'UNKNOWN',2
    ,'OTHER',3
    ,'NONE',4
    ,UPPER(MATERIAL)
    );

ORA-01722: invalid number

 

[SantaMufasa]

Sorry, do this instead (it's a much more fool-proof solution):

...ORDER BY DECODE(UPPER(MATERIAL)
    ,'UNKNOWN',[b]chr(253)[/b]
    ,'OTHER',[b]chr(254)[/b]
    ,'NONE',[b]chr(255)[/b]
    ,UPPER(MATERIAL)
    );

Let us know if this works for you.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[BJCooperIT]

ORDER BY DECODE(UPPER(MATERIAL)
    ,'UNKNOWN','2'
    ,'OTHER','3'
    ,'NONE','4'
    ,'1'||UPPER(MATERIAL)
    );

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[SantaMufasa]

Barb,

I considered the '2','3','4' solution, but most collating sequences will sort those three values in front of other alphabetic values. Conversely, nothing will sort highter than chr(253), chr(254), and chr(255).

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[BJCooperIT]

Dave,

That is why I added the '1'||UPPER(MATERIAL). Also, what if the MATERIAL column contains data that begins with a numeric character?

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[BJCooperIT]

Missed your fool-proof solution, nothing like cross-posting !

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus