I have the select below, which always brings me two lines, one with zero and one with value:
SELECT COUNT(DISTINCT ROMANEIO) ROMANEIO FROM PCN_ROMANEIO_DISTR_ITEM WHERE USUARIO = 'junior'
UNION ALL
SELECT COUNT(DISTINCT ROMANEIO) ROMANEIO FROM PCN_ROMANEIO_DISTR_ITEM WHERE USUARIO_EMPILHADEIRA = 'junior'
The result is as below:
What you need to do is to only display the result with a value greater than 0. Only one will have value, always. Any suggestions?
Use a Subselect and filter the result of the select internor.
select * from
(
SELECT COUNT(DISTINCT ROMANEIO) ROMANEIO FROM PCN_ROMANEIO_DISTR_ITEM WHERE USUARIO = 'junior'
UNION ALL
SELECT COUNT(DISTINCT ROMANEIO) ROMANEIO FROM PCN_ROMANEIO_DISTR_ITEM WHERE USUARIO_EMPILHADEIRA = 'junior'
)saida
where saida.ROMANEIO > 0
You can use the HAVING clause to filter aggregated values:
SELECT COUNT(DISTINCT ROMANEIO) ROMANEIO
FROM PCN_ROMANEIO_DISTR_ITEM
WHERE USUARIO = 'junior'
HAVING COUNT(DISTINCT ROMANEIO) > 0
UNION ALL
SELECT COUNT(DISTINCT ROMANEIO) ROMANEIO
FROM PCN_ROMANEIO_DISTR_ITEM
WHERE USUARIO_EMPILHADEIRA = 'junior
HAVING COUNT(DISTINCT ROMANEIO) > 0;