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

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)
I only say ORDER BY DECODE (5,23,2,12,56) or any other combination and it always orders descending, no matter if i say DESC or ASC (which is OK, i can build the list from end to start).

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

I just took the same list of values and >
> select ... where product_id in ( 5, 23, 2, 12, 56 )
> order by decode( product_id, 5, 1, 23, 2, 2, 3, 12, 4, 56, 5 )
>
> although this approach doesn't use bind variables which could doom you to
> failure. Consider issuing:
>
> alter session set cursor_sharing=force;
> open that query
> alter session set cursor_sharing=exact;
>
> when you do this query so that at least we only get 5 copies of the sql
> statement stuffed in the shared pool instead of thousands of them.
>
> or maybe even better since its always 2 .. 6
>
>
> select ... where product_id in ( :bv1, :bv2, :bv3, :bv4, :bv5, :bv6 )
> order by decode( product_id, :bv1, 1,
> :bv2, 2,
> :bv3, 3,
> :bv4, 4,
> :bv5, 5,
> :bv6, 6 );
>
> and bind in 6 values (4 nulls for bv3..bv6 when you have 2 items, 3 nulls
when
> you have 3 and so on)
>
>
>
> >> > 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
> >
> >
> >
> >
>
> --
> Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
>
Received on Thu Jan 10 2002 - 10:51:39 CST