Select with case inside (date)

0

Well, I have a problem with my select. I need this select to count how many estadoVisu has, but if the date is nula inside the case it should have a subquery inside checking if estadoVisu is VisualizadoTec2 or VisualizadoCli1 . That is, if the date is null, it must count how many estadoVisu it has when the values are quoted. The tbl_notification table has a record with the null date, with the data related all right, but it is not going.

Codes:

//aqui a select com o count, case e a subquery.

select count(estadoVisu) as notificacao  from carregarNotificacao where 
cpf_cliente = 12345678911 and CASE datareg 
WHEN (datareg is null) THEN (select estadoVisu = 'VisualizadoTec2' or 
estadoVisu = 'VisualizadoCli1') END; 

// aqui eu testei sem o case, porém com a verificação da data e mesmo assim 
// não foi.
select count(estadoVisu) as notificacao  from carregarNotificacao where 
cpf_cliente = 12345678911 and datareg is null;

// aqui foi de boas.
select count(estadoVisu) as notificacao  from carregarNotificacao where 
cpf_cliente = 12345678911;

// view
create view carregarNotificacao as
select tbl_contrato.cpf_cliente, tbl_contrato.cpf_tecnico, 
tbl_contrato.id_contrato, tbl_notificacao.estadoVisu, 
tbl_notificacao.id_contrato_fk,
tbl_notificacao.id_not, tbl_notificacao.data_hora_reg_not as datareg
from tbl_contrato
left join tbl_notificacao on tbl_notificacao.id_contrato_fk = 
tbl_contrato.id_contrato
left join tbl_usuario on tbl_usuario.cpf = tbl_contrato.cpf_cliente;
    
asked by anonymous 31.05.2018 / 06:40

0 answers