子查询中的合并仍然返回DBnull。

Unfortunately i am facing today another bug which i don't understand. I'm selecting the next and previous id from my database and i am using COALESCE to replace a null value with a number. But for some reason it still returns null, am i missing something?

不幸的是，我今天又遇到了一个我不明白的问题。我正在从数据库中选择下一个和上一个id，并使用COALESCE将一个空值替换为一个数字。但出于某种原因，它仍然返回null，我是不是漏掉了什么?

"Select Gags.Title, Gags.DatePosted, Gags.Image, Users.Username, (Select Top(1) COALESCE(Id, 0) from Gags where Id > " + IdModel.Id + "), (Select Top(1) COALESCE(Id, 0) from Gags where Id < " + IdModel.Id + ") From Gags Inner Join Users on Gags.Userid = Users.Id where Gags.Id = " + IdModel.Id + "

Fixed Query:

固定的查询:

Select Gags.Title, Gags.DatePosted, Gags.Image, Users.Username, isnull((Select Top(1) id from Gags where Id > " + IdModel.Id + "),-1), Isnull((Select Top(1) id from Gags where Id < " + IdModel.Id + "), -1) From Gags Inner Join Users on Gags.Userid = Users.Id where Gags.Id = " + IdModel.Id + "

I'm talking about the two sub queries.

我说的是两个子查询。

Thanks in advance!

提前谢谢!

It's a terrible programming day today ..

今天是一个糟糕的编程日。

1 个解决方案

#1

For an empty rowset, coalesce won't be invoked:

对于空行集，合并不会被调用:

(select top 1 coalesce(Id, 0) from ...)

And an empty rowset in a scalar context returns null:

在标量上下文中的空行集返回null:

select (select 1 where 1=0)
-->
NULL

One solution is to move the coalesce outside the subquery:

一种解决方案是将合并移到子查询之外:

coalesce((select top 1 Id from ...),0)

#1