Oracle: VLOOKUP Equivalent

Question

I have a view like this:

col_1  col_2  my_date
-----  -----  -------
1      5      2011
2      6      2014
3      7      2012
4      8      2011

And a table like this:

date_1  date_2  the_value
------  ------  ---------
2010    2012    v1
2013    2015    v2

I want something like the Excel VLOOKUP function that find the value (the_value) which my_date is between date_1 and date_2, so I can have a result like this:

col_1  col_2  my_date  the_value
-----  -----  -------  ---------
1      5      2011     v1
2      6      2014     v2
3      7      2012     v1
4      8      2011     v1

The type of date columns are DATE. These are sample data for simplicity.

Answer 1

That is a join in SQL with a between rather than an equality join condition.

select t1.col_1, t1.col_2, t1.my_date, t2.the_value
from table_one t1
  join table_two t2 on t1.my_date between t2.date_1 and t2.date_2;

Note that between includes the boundaries so it would also return rows where my_date is 2010. If you don't want that you need to use a join condition with > and <:

select t1.col_1, t1.col_2, t1.my_date, t2.the_value
from table_one t1
  join table_two t2 on t1.my_date > t2.date_1 
                   and t1.my_date < t2.date_2;

This also requires your "date" ranges to be non-overlapping, otherwise you'd get some strange results.

Answer 2

You can use a correlated query to fetch the value:

SELECT t.*,
       (SELECT s.the_value FROM t2 s
        WHERE t.my_date between s.date_1 and s.date_2) as the_value
FROM t1 t