Case in Where for query filter

Question

I have this select below, but when date is true, he needs to bring all the records where the field OrdemServicos.DataConclusao is different from null, and when it is false needs to bring everyone where the OrdemServicos.DataConclusaois equal to null, and when @data is null, needs to bring both, how to solve, it does not understand Is not null, I am putting in a dataset, for reports.

    SELECT        OrdemServicos.DataConclusao, OrdemServicos.ValorTotal, OrdemServicos.ClienteId, OrdemServicos.OrcamentoId, Marca.Descricao AS marca, Modelo.Descricao AS modelo, Clientes.Nome, Empresas.RazaoSocial, Veiculos.Placa, Veiculos.Cor
FROM            OrdemServicos INNER JOIN
                         Empresas ON Empresas.Id = OrdemServicos.EmpresaId INNER JOIN
                         Clientes ON Clientes.Id = OrdemServicos.ClienteId INNER JOIN
                         Veiculos ON Veiculos.Id = OrdemServicos.VeiculoId INNER JOIN
                         Modelo ON Modelo.Id = Veiculos.ModeloId INNER JOIN
                         Marca ON Marca.Id = Veiculos.MarcaId
where (Clientes.id = @idcliente or @idcliente is null)
and (Empresas.id = @idempresa or @idempresa is null)
and (Veiculos.id = @idveiculo or @idveiculo is null)
and (CASE WHEN @data = 'true' THEN OrdemServicos.DataConclusao IS NOT NULL 
 WHEN @data = 'false' THEN ordemservicos.dataconclusao is null)

Answer 1

The problem is that SQL does not support conditional IS NULL or NOT NULL referenced from a case, the most it does is the following:

SELECT *
FROM atendimento atd
WHERE atd.cd_paciente >
(case when 1 = 1 then 1
 ELSE 100
 END)

There are several other ways to solve your problem, most likely this is a trial, in case I would use IFS, for example:

IF @data = 'true' THEN 

   SELECT
   INTO tabela_final
   ...
   WHERE   OrdemServicos.DataConclusao IS NOT NULL

 ELSIF @data = 'false'

   SELECT
   INTO tabela_final
   ...
   WHERE   OrdemServicos.DataConclusao IS NOT NULL 

 ELSE

   SELECT
   INTO tabela_final
   ...
 END IF;

 --Consultando a tabela final
 SELECT * FROM tabela_final;

It is necessary to create a cleaning routine of this table, or drop it and create it every time you run the previous:

--EXCLUINDO TABELA
DROP TABLE tabela_final;

    IF @data = 'true' THEN 

   SELECT
   INTO tabela_final
   ...
   WHERE   OrdemServicos.DataConclusao IS NOT NULL

 ELSIF @data = 'false'

   SELECT
   INTO tabela_final
   ...
   WHERE   OrdemServicos.DataConclusao IS NOT NULL 

 ELSE

   SELECT
   INTO tabela_final
   ...
 END IF;

 --Consultando a tabela final
 SELECT * FROM tabela_final;

In the above example it is good to add a conditional to check if the table exists, to avoid unnecessary errors. You can use the IF_EXIST if using SQL SERVER.

With this method it is possible to solve most cases of such problems.