SQL query to translate a list of numbers matched against several ranges, to a list of values

Question

I need to convert a list of numbers that fall within certain ranges into a list of values, ordered by a priority column. The table has the following values:

| YEAR | R_MIN |  R_MAX | VAL | PRIO |
------------------------------------
  2010   18000    90100   52    6
  2010  240000   240099   82    3
  2010  250000   259999   50    5
  2010  260000   260010   92    1
  2010  330000   330010   73    4
  2010  330011   370020   50    5
  2010  380000   380050   84    2

The ranges will be different for different years. The ranges within one year will never overlap.

The input will be a year and a list of numbers that might fall within one these ranges. The list of input number will be small, 1 to 10 numbers. Example of input numbers:

(20000, 240004, 375000, 255000)

With that input I would like to get a list ordered by the priority column, or a single value:

82
50
52

The only value I'm interested in here is 82, so UNIQUE and MAX_RESULTS=1 would do. It can easily be done with one query per number, and then sorting it in the Java code, but I would prefer to do it in a single SQL query.

What SQL query, to be run in an Oracle database, would give me the desired result?

Answer 1

I think your first task would be to convert the list of numbers into a result set (ie. in-memory table) that you can join to. I don't know Oracle, so there may be an easy way to do it, but if not you'll need to write some kind of user-defined function that does this. It shouldn't be too hard and performance is not an issue since the list is small. You can then do a join to that table. Something like this:

SELECT yt.val
FROM your_table yt
JOIN your_parse_numbers_function(@inputlist) il
ON il.value >= yt.R_MIN AND il.value <= yt.R_MAX
WHERE yt.YEAR = @year

You could limit that to 1 result if you wish, but if your assumption about ranges not overlapping is correct then it should only return 1 anyway.

Answer 2

I am guessing you want to pass that set of numbers as a string and split into into individual numbers. This is harder than you might think, because Oracle doesn't come with a built-in tokenizer. Weird, huh?

There are a number of PL/SQL tokenizer solutions knocking around Das Interwabs. I am using a variant of Anup Pani's implementation, which uses Regex (hence only Oracle 10g or higher). My variant returns an array of numbers which I have declared as a SQL type:

SQL> create or replace type numbers as table of number
  2  /

Type created.

SQL>

This means I can use it as an input to a TABLE() function in a SELECT statement:

SQL> select * from table (str_to_number_tokens('20000, 240004, 375000, 255000'))
  2  /

COLUMN_VALUE
------------
       20000
      240004
      375000
      255000

SQL>

This means I can turn your string of numbers into a table which I can join to in a query, like this:

SQL> select val
  2  from t23
  3       , ( select column_value as i_no
  4           from table (str_to_number_tokens('20000, 240004, 375000, 255000')) ) sq
  5  where t23.year = 2010
  6  and   sq.i_no between t23.r_min and t23.r_max
  7  order by t23.priority
  8  /

       VAL
----------
        82
        50
        52

SQL>