-1

I have view that combines two tables. One of the requested values by select has to be in either one of those tables and the other has to have null as this value. As a solution I use coalesce

COALESCE(tbl1.Val, tbl2.Val) As Val

I am now struggling to select the next value from the same table as Val was taken from, as they can be different/NULL in both.

What I was thinking in pseudo

(if tbl1.Val = NULL then tbl1.Val2 else tbl2.Val2) as Val2

Is this possible to write it using sql functions? (IIF, ISNULL, NULLIF,... probably?)

How complicated would this get with 3 tables?

Input

tbl1.Val = NULL,
tbl2.Val = NULL,
tbl3.Val = 'VALUE'

tbl1.Val2 = 'NOT THIS ONE',
tbl2.Val2 = 'NOT THIS ONE',
tbl3.Val2 = 'THIS ONE'

Expected

Val     Val2
'Value' 'THIS ONE'

I tried constructions like so

IIF(tbl1.Val = NULL, tbl1.Val2, tbl2.Val2)
IIF(ISNULL(tbl1.Val, False), tbl1.Val2, tbl2.Val2)
Thom A
  • 88,727
  • 11
  • 45
  • 75
AweSomeBody
  • 3
  • 2
  • 1
    `null` represents an unknown or undefined value; nothing inc `null` is equal to `null`; use `is null` – Stu Mar 04 '23 at 10:01
  • Does this answer your question? [Why does NULL = NULL evaluate to false in SQL server](https://stackoverflow.com/questions/1843451/why-does-null-null-evaluate-to-false-in-sql-server) – Thom A Mar 04 '23 at 10:05
  • 1
    If both tbl1.Val, tbl2.Val are NULL are you asking for a value FROM THE "NEXT" ROW? Please illustrate 2 or more rows of sample data and then the expected result from that data. By the way if it does involve "the "next" row, we need to know what order is required to determine "next" – Paul Maxwell Mar 04 '23 at 10:08
  • 1
    `coalesce()` accepts more than 2 parameters and returns the first non-null value e.g. `coalesce(null, null, null, 1)` would return 1 – Paul Maxwell Mar 04 '23 at 10:10

1 Answers1

0

Instead of IIF try a case expressions like so:

CASE WHEN tbl1.Val IS NULL THEN COALESCE(tbl1.Val2, tbl2.Val2) END AS X

or

CASE WHEN tbl1.Val IS NOT NULL THEN COALESCE(tbl1.Val2, tbl2.Val2) END AS X

You can also include ELSE E.G.

CASE
    WHEN tbl1.Val IS NULL THEN COALESCE(tbl1.Val2, tbl2.Val2) 
    ELSE some-expression-here
END AS X

but the data type of that expression must be compatible with tbl1.Val2, tbl2.Val2

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51