SQL Server Using the same table in another value from previous COALESCE

qxsslcnc 于 2023-03-07 发布在其他

关注(0)|答案(1)|浏览(134)

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)

sql-server

来源：https://stackoverflow.com/questions/75634893/using-the-same-table-in-another-value-from-previous-coalesce

1条答案

按热度按时间

ds97pgxw1#

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

赞(0）回复(0）举报 2023-03-07

我来回答

SQL Server Using the same table in another value from previous COALESCE

1条答案

相关问题

热门标签

最新问答