Search code examples
sql-servert-sqlcoalesce

Why COALESCE return NULL instead of value I need?

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.

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

enter image description here

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

enter image description here

What am I missing here? Thanks

Solution

  • 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.

    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
       )