CODEWITHSUNDEEP
sqlsql-serveribatis.net
Killnine
Killnine

Reputation: 5890

Return result from query even if WHERE clause not met

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?

Upvotes: 0

Views: 6761

Answers (4)

Sean Lange
Sean Lange

Reputation: 33581

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

Upvotes: 15

pilcrow
pilcrow

Reputation: 58589

If it must be in a query, rather than in the code, then just cheat and tack the default row on to the end of your query. Guarantee that the default sorts after any possible veritable result, and limit to your first row:

SELECT TOP 1 * FROM (
  SELECT 1 AS TaskExists, CASE ... END AS PressReady, CASE ... AS WowYouHaveALongFieldNameHere
    FROM Task
   WHERE TaskID = 55555

  UNION

  -- default if no matching row
  SELECT 0,               0,                          0
) ORDER BY TaskExists DESC;

You may find this more readable than alternatives involving aggregate functions or forcing a join and COALESCE/ISNULLing, etc.

Upvotes: 1

Bulat
Bulat

Reputation: 6969

You can try something like this:

DECLARE @task INT
SET @task = 555555

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 = @task
UNION ALL 
SELECT 0 TaskExists, 0 PressReady, 0 DownTaskAssignedToDifferentMachine   
WHERE NOT EXISTS (SELECT * FROM  Task WHERE TaskId = @task)

Upvotes: 1

Adi
Adi

Reputation: 232

Please try the below code. I haven't tested. 

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  1= case when TaskId = 555555 then 1 
    else 0 end

Upvotes: -1

Related Questions