John Croneh John Croneh - 27 days ago 16
SQL Question

Oracle: VLOOKUP Equivalent

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

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.

Comments