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?

Googling `nvsl` or `nvsl2` doesn't return anything meaningful. Could you perhaps be referring to `nvl2`? — Mureinik, Mar 20 '16 at 17:56
What is your expected output? Some sample data would be good. — Kamil Gosciminski, Mar 20 '16 at 18:00
It's a bit vague, but wouldn't a left join and a case statement to choose which color to use work here? — Alex Poole, Mar 20 '16 at 18:02
I have a case built, but could not get it included witht he original question... — user761758, Mar 20 '16 at 18:02
expected output is to create a view showing all rows that match with name, style and doors and the color listed in t1, and rows that match in name but not in style OR doors, to be an alternative color.. orange.. — user761758, Mar 20 '16 at 18:04

score 1 · Answer 1 · answered Mar 20 '16 at 18:03

I think you're approaching this the wrong way. Assuming that t1 and t2 have a 0:[0..1] relationship (which I guess they must have, or the question doesn't make much sense), your weapon of choice would be a left join, and applying nvl, or something to that effect (see below) after joining:

SELECT    t2.*, NVL(t1.color, 'orange')
FROM      t2
LEFT JOIN t1 ON t1.style = t2.style AND t1.doors = t2.doors

Note that nvl is an Oracle function. The ANSI-SQL equivalent would be coalesce, and using it would make your application a tad more portable:

SELECT    t2.*, COALESCE(t1.color, 'orange')
FROM      t2
LEFT JOIN t1 ON t1.style = t2.style AND t1.doors = t2.doors

@user761758 Just slap this select inside a `create view` statement: `CREATE VIEW myview AS SELECT -- etc...`. — Mureinik, Mar 21 '16 at 05:57

Kamil Gosciminski · Accepted Answer · 2016-03-20T18:09:21.930

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

Spot on.. the left join with a case got it done.. and seems the least confusing way to get it done going forward... when others have to see the same thing...... — user761758, Mar 20 '16 at 18:45

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

2 Answers2