Re: Arbitrary amount of BETWEENs

From: <phil_herring_at_yahoo.com.au>
Date: Mon, 28 Apr 2008 17:01:23 -0700 (PDT)
Message-ID: <4070c958-1034-4c4b-951f-72bf5f60f83e@b5g2000pri.googlegroups.com>

Something like this, maybe?

SQL> create table test_pairs

  2          (
  3          what_we_are_looking_for         varchar2(100),
  4          the_key                         integer
  5          );

Table created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('one', 1);

1 row created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('two', 2);

1 row created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('three', 3);

1 row created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('four', 4);

1 row created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('five', 5);

1 row created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('six', 6);

1 row created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('seven', 7);

1 row created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('eight', 8);

1 row created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('nine', 9);

1 row created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('ten', 10);

1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> create or replace type pair_of_integers_typ as object

  2          (
  3          integer_1               integer,
  4          integer_2               integer
  5          );

  6 /
Type created.
SQL>
SQL> create or replace type pair_of_integers_tab as table of pair_of_integers_typ;
  2 /
Type created.

SQL>
SQL> set serveroutput on
SQL>
SQL> declare
  2
  3          v_pairs_of_integers     pair_of_integers_tab :=
pair_of_integers_tab();
  4          rec_test_pairs          test_pairs%rowtype;

  5
  6 begin
  7

  8          v_pairs_of_integers.extend;
  9          v_pairs_of_integers(1) := pair_of_integers_typ(4, 5);
 10
 11          v_pairs_of_integers.extend;
 12          v_pairs_of_integers(2) := pair_of_integers_typ(7, 8);
 13
 14          for rec_test_pairs in
 15                  (
 16                  select
 17                          *
 18                  from
 19                          test_pairs tp,
 20                          table(cast(v_pairs_of_integers as
pair_of_integers_tab)) ranges
 21                  where
 22                          tp.the_key between ranges.integer_1 and
ranges.integer_2
 23                  ) loop

 24
 25
dbms_output.put_line(rec_test_pairs.what_we_are_looking_for || ' ' || rec_test_pairs.the_key);
 26
 27 end loop;
 28
 29 end;
 30 /
four 4
five 5
seven 7
eight 8
PL/SQL procedure successfully completed. SQL>

• Phil

Received on Mon Apr 28 2008 - 19:01:23 CDT