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 an Excel VLOOKUP function that will find the value ( the_value ) that my_date is between date_1 and date_2 , so I can get this result:

 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 is DATE . This is simple data for simplicity.

Answer 1

This is a SQL join with between , not a 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 borders, so it also returns strings where my_date is 2010 . If you do not want you to use the 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; 

It also requires your date ranges to be non-overlapping, otherwise you would get some weird results.

Answer 2

You can use the correlated query to retrieve 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