Oracle sql 11g - Can an nvl2 function include a select statement?

Question

Not even sure nvl2 is what I should be using, but it seems to make sense to me. If I could get the select to work. The data below is not the actual, but as close as I can get with imagined objects:

I tried to post this with a full case of two tables and one view, but I kept getting 'an error occurred while posting', so I thought I would try without any test data.... There are two tables - t1 and t2 -- that hold columns of 'name', 'style', 'color', and 'doors'.

.. they each have several rows of data where everything is equal and several rows that do not match. If 'name' and 'doors' are both equal, I need the color in the view to be whatever the color is in t1. If 'name' and 'doors' do not match, I need the color in the view to read 'orange'

I tried creating in the view select statement an nvl2 function with a select statement determining the first value

nvl2(color, (select color from t1 where t1.style = t2.style and t1.doors = t2.doors), orange)

Can an nvl2 function contain a select? Have I gone about it wrongly, and if so, what should I use in the stead of an nvl2 function?

Answer 1

It's a little vague what you're trying to achieve, but this may work:

SELECT
  t1.*,
  CASE WHEN t2.name is not null THEN t1.color ELSE 'orange' END as color
FROM
  t1
  LEFT JOIN t2 ON
    t1.name = t2.name
    AND t1.doors = t2.doors

You may need to change the ordering of t1 and t2 as joins are concerned because it's somewhat not clear from which table you'd like to pull your rowset from

You only need a LEFT JOIN and a CASE statement to implement your color picking logic.

Edit after comment:

I actually think you're looking for this logic, but it's still not clear to me:

SELECT
  t1.*,
  CASE WHEN t1.doors = t2.doors and t1.style = t2.style and t1.color = t2.color THEN t1.color ELSE 'orange' END as color
FROM
  t1
  LEFT JOIN t2 ON
    t1.name = t2.name