为什么合并返回NULL而不是我需要的值?

From the table tblQuoteStatusChangeLog I need to check if column NewQuoteStatusID has one of those values (2, 25 or 202), and pick the earliest TimeStamp. So if it has value 2, then pick up the TimeStamp, if it doesnt have value 2 then check if there is 25 and pick up corresponding TimeStamp, and if its not then check for 202 and pick up proper stamp.

从表tblQuoteStatusChangeLog中，我需要检查列newquote雕像sid是否具有其中的一个值(2,25或202)，并选择最早的时间戳。因此，如果它有值2，那么取时间戳，如果它没有值2，那么检查是否有25，并取相应的时间戳，如果没有，则检查202并取适当的戳。

So from tblQuoteStatusChangeLog I need to pick up first row with StatusID 202, because its the only that falls under condition.

所以从tblQuoteStatusChangeLog中，我需要拿起第一行，sid 202，因为这是唯一一个符合条件的。

So I have this query:

我有一个问题:

SELECT  
      (SELECT        TOP (1) Timestamp
                    FROM            tblQuoteStatusChangeLog
                    WHERE        NewQuoteStatusID = COALESCE (2,25,202)  AND ControlNo = tblQuotes.ControlNo
                    ORDER BY Timestamp DESC) as DateQuoted
FROM tblQuotes
INNER JOIN tblMaxQuoteIDs ON tblQuotes.QuoteID = tblMaxQuoteIDs.MaxQuoteID
where tblQuotes.ControlNo = 50065 

But for some reason I got NULL value as a result

但是出于某种原因，我得到了零值

What am I missing here? Thanks

我错过了什么?谢谢

1 个解决方案

#1

I don't think coalesce() is the function that you want. coalesce(2, 25, 2002) returns the first non-NULL value, which is always "2". Your sample data doesn't have the value "2", so that is why the subquery returns NULL.

我认为coalesce()并不是您想要的函数。coalesce(2,25,2002)返回的第一个非空值总是“2”。您的示例数据没有“2”的值，所以这就是子查询返回NULL的原因。

I think you might want IN:

我想你可能想要:

SELECT (SELECT TOP (1) Timestamp
        FROM tblQuoteStatusChangeLog
        WHERE NewQuoteStatusID IN (2, 25, 202) AND
              ControlNo = tblQuotes.ControlNo
        ORDER BY Timestamp DESC
       )