CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Robert Allan Hennigan Leahy
Robert Allan Hennigan Leahy

Reputation: 6578

Adding second CASE WHEN column changes query result completely?

I have the following queries:

1

SELECT TOP 1
    CASE WHEN latency=-1
        THEN 'Down'
        ELSE 'Up'
    END AS status
FROM
    @pings_temp
ORDER BY datetime DESC;

2

SELECT TOP 1
    CASE WHEN latency=-1
        THEN 'Down'
        ELSE 'Up'
    END AS status,
    CASE WHEN latency=-1
        THEN
        (
            SELECT TOP 1
                datetime
            FROM
                @downtimes_temp
            ORDER BY
                datetime DESC
        )
        ELSE NULL
    END AS datetime
FROM
    @pings_temp
ORDER BY datetime DESC;

The first should yield just 'Up' and the second should yield 'Up' in the first column and NULL in the second.

However, this doesn't happen. The first query performs exactly how it should, but adding the second column to the SELECT makes the query go berserk, causing it to read out 'Down' and the date from a seemingly random column.

Here's an image of the two results from the same T-SQL batch.

Upvotes: 0

Views: 227

Answers (1)

Eugene
Eugene

Reputation: 2985

The error in the second case is probably caused by your ORDER BY clause, since the engine cannot evaluate the order of a NULL value.

Upvotes: 3

Related Questions