I am creating a query that ensures some constraints are met. Here's a semi-working version right now:
SELECT CASE
WHEN TaskId IS NULL THEN 0
ELSE 1
END AS TaskExists,
CASE
WHEN IsDownTask = 0 AND TaskStatus = 63 THEN 1
WHEN IsDownTask = 1 THEN 1
ELSE 0
END AS PressReady,
CASE
WHEN IsDownTask = 1 AND MachineId <> 2710 THEN 1
ELSE 0
END AS DownTaskAssignedToDifferentMachine
FROM Task T
WHERE TaskId = 555555
This works fine when TaskId
exists in the Task
table, but I also need to return values if that Task doesn't exist (hence the TaskExists
field).
For a query on a non-existent Task, I'd expect to return
How can I modify my query to return this even when no TaskId
exists?
If you want to return those values just wrap each column with a SUM
and an ISNULL
:
SELECT ISNULL(SUM(CASE
WHEN TaskId IS NULL THEN 0
ELSE 1
END), 0) AS TaskExists,
ISNULL(SUM(CASE
WHEN IsDownTask = 0 AND TaskStatus = 63 THEN 1
WHEN IsDownTask = 1 THEN 1
ELSE 0
END), 0) AS PressReady,
ISNULL(SUM(CASE
WHEN IsDownTask = 1 AND MachineId <> 2710 THEN 1
ELSE 0
END), 0) AS DownTaskAssignedToDifferentMachine