How to put ISNULL in Subselect with case?

Question

How do I return instead Null return as Não. I’m having second thoughts about using the ISNULL within the Subselect along with the case.

Upshot:

Projeto teste     Null

Projeto OK        Sim.

Instead of Null I want you to call me back NÃO, how do I do?

SELECT DISTINCT concat(CC.Descricao,'-',U1.UsuNome),
                C.CompDesc QtdeCNPJs,

  (SELECT top 1 Faturado = CASE T1.TarEstagioID
                               WHEN 112 THEN 'SIM'
                               ELSE 'NÃO'
                           END
   FROM Projetos P
   INNER JOIN Tarefa T1 ON P.ProjID = T1.ProjID
   WHERE T1.TarStatus = 9
     AND T1.TarTitulo = 'Treinamento Realizado - Emitir Boleto Setup'
     AND P.ProjID = PP.ProjID
     AND T1.TarTipID = 674) Faturado
FROM PROJETOS PP
INNER JOIN Tarefa T ON PP.ProjID = T.ProjID
INNER JOIN Usuario U ON T.UsuIDResponsavel = U.UsuID
INNER JOIN Usuario U1 ON T.UsuIDCliente = U1.UsuID
LEFT JOIN Complemento C ON C.UsuID = T.UsuIDCliente
AND C.CompID = 1
LEFT JOIN CurvaABC CC ON (CC.CurvaID = U1.CurvaID)
WHERE CC.CurvaID = 1

Answer 1

Renan,

I used sqlServer’s ISNULL function.

Example:

SELECT ISNULL(STATUS, 0) AS STATUS FROM T_TESTE

In this situation, if the status field comes null it will be replaced by 0.

If you can’t, take a look at this post:

http://t-sql.com.br/como-utilizar-a-funcao-isnull-funcoes-sql-server-parte-5/

Answer 2

The calculation of "Invoiced" can be rewritten to

-- código #1 v2
SELECT concat(CC.Descricao,'-', U1.UsuNome),
       C.CompDesc as QtdeCNPJs,    
       case when exists (SELECT * 
                           from Tarefa as T1
                           where T1.ProjID = PP.ProjID
                                 and T1.TarStatus = 9
                                 and T1.TarTitulo = 'Treinamento Realizado - Emitir Boleto Setup'
                                 and T1.TarTipID = 674
                                 and T1.TarEstagioID = 112)
            then 'SIM' 
            else 'NÃO' end as Faturado
  from PROJETOS as PP
... 

Is a correlated subconsultation.

In this sub-survey it is not necessary to use the Projects table; it is enough to establish the direct correlation with the Projects table of the external consultation.