How to make a Select to not show reset result?

Question

I have the select below, which always brings me two lines, one with zero and the other 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 below:

What you would need is to make it only display the result with a value greater than 0. Only one will have value, always. Any suggestion?

Answer 1

Use a Subselect and filter the result of 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

Answer 2

You can use the HAVING clause to filter aggregate 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;