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

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 10 Jan 2002 12:07:56 -0800
Message-ID: <a1ksas0ecc@drn.newsguy.com>

In article <a1kgo6$bp4$07$1_at_news.t-online.com>, "Dirk says...
>
>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).

order by DECODE(5,23,2,12,56)

is IDENTICAL to

order by NULL;

  1* select DECODE(5,23,2,12,56) from dual order by DECODE(5,23,2,12,56) SQL> / DECODE(5,23,2,12,56)

---

SQL> you see DECODE is a function that does matching. If you MUST use a string (and I beg of you -- use BIND VARIABLES please, they are pretty important) you can use instr like the following shows:

SQL> create table t ( x int );

Table created.

SQL> insert into t values ( 56 );

1 row created.

SQL> insert into t values ( 12 );

1 row created.

SQL> insert into t values ( 2 );

1 row created.

SQL> insert into t values ( 23 );

1 row created.

SQL> insert into t values ( 5 );

1 row created.

SQL> 
SQL> 
SQL> select *

  2 from t;

         X

---

        56
        12
         2
        23
         5

SQL>
SQL> select *
  2 from t
  3 order by instr( ',' || '5,23,2,12,56' || ',', ',' || x || ',' );

         X

---

         5
        23
         2
        12
        56

See, the second query is orderd by the list you supply. Now, if the that list can contain whitespace and such, you'll want to use REPLACE() on it to remove the whitespace so the numbers are all:

   ,NUMBER,NUMBER,NUMBER,
>
>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
>>
>
>

--
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 - 14:07:56 CST