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

"andrija" <ar35644_at_fer.hr> schrieb im Newsbeitrag news:a1jsoq$e0am$1_at_as201.hinet.hr...
> > Hello!
> >
> > SELECT ...WHERE product_id IN (5,23,2,12,56)
> >
> > I try to get a list of records in the same order like inside the
> IN-clause.
> > I do not have a field to sort these and dependent from user- Input its
> > always different (2 to 6 items).
> >
> > Is this possible? or is there a different way to get records in a
> particular
> > order without having a field which I can use to sort?
>
> You could create function like this
>
> create function sort(product_id number,sort1 number default null, sort3
> number default null,...,sort6 number default null) return number is
>
> begin
>
> if product_id=sort1 then
> return 1;
> end if;
>
> if product_id=sort2 then
> return 2;
> end if;
>
> ...
>
> end;
>
> so you use:
>
> SELECT ...WHERE product_id IN (5,23,2,12,56) order by
> sort(product_id,5,23,2,12,56);
>
> This is not so good approach, because you limit number of arguments.
Better
> way is to declare second parameter as table, so you can have any number of
> parameters, but that's little more complicated.
>

Andrija,

thank you!!
May not look that elegant but is a perfect workaround for me. (Why isn't there a possibility inside Oracle or a predefined function...?)

greetings, Dirk Received on Thu Jan 10 2002 - 06:23:39 CST