CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
Stuart
Stuart

Reputation: 4258

Case with Select and Null Check

select a.AgentId,
       case(select acs.IsConnected
            from rmm.tblAgentConnectionStatus acs
            where acs.AgentName = a.AgentName)
        when 1 then 'True'
        when 0 then 'False'
        when null then 'False'
        end as ConnectionStatus
from Sentilan2.rmm.tblAgent a
order by AgentName asc

I have the above, but it results in ConnectionStatus being null when their is no corresponding row in tblAgent.

Is it possible for the result to be False when the column is null.

Current output

AgentId                                 ConnectionStatus
010D0206-5D8C-4AB1-90B6-7BD0C2773E22    True
CA4C48DD-3D2E-4948-9F93-254CDF081658    True
1DB90EE5-D96A-4071-8F51-26B3130EC6D4    NULL
ACA694D0-0C1D-45BA-80DD-273F41BD70B1    NULL
941B539B-7CA0-4472-ABCD-2777AE8B2E5D    NULL
1E7DDA4D-C119-4E47-8478-277952024FD1    NULL

I'd like those nulls to be false.

Upvotes: 2

Views: 987

Answers (3)

jhenderson2099
jhenderson2099

Reputation: 964

You should use a LEFT OUTER JOIN with a COALESCE between the two tables.

That way, you'll get what you need from tblAgent, but False where IsConnected is NULL or no corresponding row in tblAgentConnectionStatus exists

select 
   a.AgentId,
   case(coalesce(acs.IsConnected, 0))
      when 1 then 'True'
      else 'False'
    end as ConnectionStatus
from Sentilan2.rmm.tblAgent a
left join Sentilan2.rmm.tblAgentConnectionStatus acs
  on acs.AgentName = a.AgentName
order by a.AgentName asc

One thing that I would recommend is NOT joining on AgentName. That compares strings.

It would be more efficient to establish a integer foreign key between the two tables (where it is the primary key in the referenced table). This will make the query faster, especially if you index the foreign key.

I have demonstrated this within this SQLFiddle link

Upvotes: 1

HABO
HABO

Reputation: 15816

The correct test for a null value is is NULL. Unfortunately, that requires repeating the value for each when clause within the case expression. There are various ways to avoid repeating the correlated subquery, e.g. a common table expression (CTE). In this case a left outer join will suffice.

select a.AgentId,
       case
         when acs.IsConnected = 1 then 'True'
         when acs.IsConnected = 0 then 'False'
         when acs.IsConnected is null then 'False'
         else 'Oops: ' + Cast( acs.IsConnected as VarChar(64) )
         end as ConnectionStatus
  from Sentilan2.rmm.tblAgent as a left outer join
    rmm.tblAgentConnectionStatus as acs on acs.AgentName = a.AgentName
  order by AgentName asc;

Tip: It is generally a good idea to include an else clause in case expressions to catch any unexpected values.

Tip: It's helpful to tag database questions with both the appropriate software (MySQL, Oracle, DB2, ...) and version, e.g. sql-server-2014. Differences in syntax and features often affect the answers. Note that tsql narrows the choices, but does not specify the database.

Upvotes: 0

SqlZim
SqlZim

Reputation: 38023

You could use coalesce() or isnull() to substitute a value for null:

select a.AgentId,
       case coalesce((select acs.IsConnected
            from rmm.tblAgentConnectionStatus acs
            where acs.AgentName = a.AgentName),0)
        when 1 then 'True'
        when 0 then 'False'
        end as ConnectionStatus
from Sentilan2.rmm.tblAgent a
order by AgentName asc

Or just else 'False':

select a.AgentId,
       case (select acs.IsConnected
            from rmm.tblAgentConnectionStatus acs
            where acs.AgentName = a.AgentName)
        when 1 then 'True'
        else 'False'
        end as ConnectionStatus
from Sentilan2.rmm.tblAgent a
order by AgentName asc

Upvotes: 3

Related Questions