CODEWITHSUNDEEP
sqlcase
Tim Hein
Tim Hein

Reputation: 1

CASE statement bool error

How do i change the following WHERE CASE statement to bool to make the statement work? It's giving me the error "an expression of non-boolean type specified in a context where a condition is expected."

WHERE (dma.DeviceId = @DeviceID AND dma.AssociationStart = @Start AND dma.AssociationEnd = @End) AND
CASE WHEN (dma.AssociationEnd IS NOT NULL)
    THEN (SELECT m.TimeStamp FROM Measurements m WHERE m.TimeStamp BETWEEN @Start AND @End)
 ELSE 
    (SELECT m.TimeStamp FROM Measurements m WHERE m.TimeStamp BETWEEN @Start AND (SELECT SYSDATETIMEOFFSET()))
END

Upvotes: 0

Views: 247

Answers (2)

C. K. Young
C. K. Young

Reputation: 223113

Maybe you meant to do something like:

SELECT m.Timestamp FROM Measurements m
WHERE dma.DeviceId = @DeviceID
  AND dma.AssociationStart = @Start
  AND dma.AssociationEnd = @End
  AND m.Timestamp BETWEEN @Start AND COALESCE(@End, SYSDATETIMEOFFSET())

However, the fact that dma.AssociationEnd = @End succeeds already implies that neither dma.AssociationEnd nor @End is null. So the COALESCE (and the whole CASE in the original question) is a waste of time.

(Of course, this only makes sense within a larger SELECT that has dma.)

Upvotes: 2

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

Perhaps:

WHERE dma.DeviceId = @DeviceID
  AND dma.AssociationStart = @Start
  AND dma.AssociationEnd = @End
  AND EXISTS
    CASE WHEN (dma.AssociationEnd IS NOT NULL)
      THEN ( SELECT m.TimeStamp
             FROM Measurements m
             WHERE m.TimeStamp BETWEEN @Start AND @End
           )
      ELSE ( SELECT m.TimeStamp
             FROM Measurements m
             WHERE m.TimeStamp BETWEEN @Start AND SYSDATETIMEOFFSET()
           )
    END

Upvotes: 0

Related Questions