Re: How to get "...WHERE test IN (5,23,2,12,56)" in this order

On Thu, 10 Jan 2002 17:51:39 +0100, "Dirk Huber" <maiwert_at_t-online.de> wrote:

>Thomas,
>
>thank you!
>The function of andrija worked, but on the production system i cannot create
>functions. So i tried order by decode(..)
>My problem is solved!
>But:
>I do it different: I do not use DECODE as i should, because i get
>"5,23,2,12,56" as ONE string which i cannot manipulate (to set 1,..2..,3 for
>sorting)

Yes, you can.

Use REPLACE() in a loop. But, that would require a function.

>It's good, but i do not understand why DECODE does this magic.

Decode is not setting the order, Product_Id is.

1. the IN() limits the product ids to what you want.
2. The DECODE() in the ORDER BY, tell Oracle not to look at the actual values of Product_Id, rather to look at what DECODE() says it is.

For Example:

CREATE TABLE Moo(Cow NUMBER);

INSERT INTO Moo VALUES(1);
INSERT INTO Moo VALUES(2);
INSERT INTO Moo VALUES(3);

Now we'll spit out a column, and its DECODEd value, ORDERing by the actual column.

SELECT Cow, DECODE(Cow, 1,9,2,8,3,7) FROM Moo ORDER BY Cow;

And again, but now we'll ORDER it by the decoded column. SELECT Cow, DECODE(Cow, 1,9,2,8,3,7) FROM Moo ORDER BY DECODE(Cow, 1,9,2,8,3,7);

HTH,
Brian Received on Thu Jan 10 2002 - 14:54:40 CST