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

In article <a1k11m$2ik$06$1_at_news.t-online.com>, "Dirk says...
>
>
>"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)
>> >

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