sql-server – Doubt – SQL Server Query Condition

Question:

Guys, in the query below is bringing it as follows. However, I want you to bring only the 'YES'. The difficulty is because it's a subselect with a case. Where do I bring? No where or a having? how would it be more or less

Current results

Cliente Integrado Sim Não

Expected result

Sim

Query

SELECT distinct 
       (select CASE WHEN COUNT(DISTINCT f2.contaid) > 1 THEN 'SIM' ELSE 'NÃO' END as Cliente_Integrado from FRM_51 f2 where f2.C02 = erp.EmpresaERP and (f2.C05 = 3641 or f2.C06 = 3643)) Cliente_Integrado
FROM Tarefa T
LEFT JOIN FRM_52 FRM52 ON FRM52.TarefaID = T.TarID
--LEFT JOIN FRM_51 FRM51 ON FRM51.C01 = FRM52.C01
LEFT JOIN ERPAuxiliar ERP ON ERP.ERPEmpresaERP = FRM52.C01
WHERE T.ProID = 13 
      AND T.TarTipID = 667 --AND CIR.UsuNome LIKE '%FARMACIA%NACIONAL%'
      AND T.TarTitulo NOT IN ('Integração ERP Parceiro','Integração ERP Parceiro - Retorno','Integração ERP Parceiro - Remessa','Envio de Layout - Integração ERP Parceiro')
      and t.TarID not in(163388)

Answer:

One way to get the result you expect is to use the query above inside a subquery in FROM and apply the filter after that. Replacing it would look something like this:

SELECT *
  FROM (SELECT DISTINCT (SELECT CASE
                                  WHEN COUNT(DISTINCT f2.contaid) > 1 THEN 'SIM'
                                  ELSE 'NÃO'
                                END AS Cliente_Integrado
                           FROM frm_51 f2
                          WHERE f2.c02 = erp.empresaerp
                            AND (f2.c05 = 3641 OR f2.c06 = 3643)) AS Cliente_Integrado
          FROM tarefa t
          LEFT JOIN frm_52 frm52 ON frm52.tarefaid = t.tarid
          LEFT JOIN erpauxiliar erp ON erp.erpempresaerp = frm52.c01
         WHERE t.proid = 13
           AND t.tartipid = 667
           AND t.tartitulo NOT IN ('Integração ERP Parceiro',
                                   'Integração ERP Parceiro - Retorno',
                                   'Integração ERP Parceiro - Remessa',
                                   'Envio de Layout - Integração ERP Parceiro')
           AND t.tarid NOT IN (163388)
       ) x
 WHERE x.Cliente_Integrado = 'SIM'
Scroll to Top