Do not return value of a field, when it is zero

2

I am creating a query in SQL , but I need a field not to display the values when it is zero.

Is it possible?

SELECT gfilial.nomefantasia AS 'Filial',
       tmov.codtmv AS 'CODTMOV',
       tmov.numeromov AS 'Número',
       tmov.valorliquido AS 'Valor líquido',
       tmov.valorbrutoorig AS 'Valor bruto',
       titmmov.nseqitmmov AS 'ITEM',
       titmmov.idprd AS 'IDPRODUTO',
       tproduto.codigoprd AS 'Cód. produto',
       tproduto.nomefantasia AS 'Produto',
       titmmov.codund AS 'Unidade',
       titmmov.precounitario,
       titmmov.quantidade,
       titmmov.valorbrutoitem
  FROM tmov WITH(nolock) 
    
asked by anonymous 10.07.2017 / 20:19

3 answers

3

Use the CASE clause:

SELECT CASE t.valor
         WHEN 0 THEN NULL
         ELSE t.valor
       END AS valor
  FROM tabela t
  

CASE

     

The CASE expression is used to evaluate various conditions and return a unique value for each condition.

    
10.07.2017 / 20:42
3

Yes this is possible through the IF command

So:

SELECT IF(MEDIA_SALARIO = 0 , '', MEDIA_SALARIO) FROM FUNCIONARIO WHERE ID_FUNCIONARIO = 1;

You can also customize the values when they are null like this:

  SELECT IF(MEDIA_SALARIO IS NULL , '', MEDIA_SALARIO) FROM FUNCIONARIO WHERE ID_FUNCIONARIO = 1;

Example beast okay? Just to understand the operation.

SELECT GFILIAL.NOMEFANTASIA AS 'Filial', TMOV.CODTMV AS 'CODTMOV', 
TMOV.NUMEROMOV AS 'Número', TMOV.VALORLIQUIDO AS 'Valor líquido', 
TMOV.VALORBRUTOORIG AS 'Valor bruto', TITMMOV.NSEQITMMOV AS 'ITEM', 
TITMMOV.IDPRD AS 'IDPRODUTO', TPRODUTO.CODIGOPRD AS 'Cód. produto', 
TPRODUTO.NOMEFANTASIA AS 'Produto', TITMMOV.CODUND AS 'Unidade', 
TITMMOV.PRECOUNITARIO ,CASE TITMMOV.QUANTIDADE WHEN 0 THEN '' ELSE 
TITMMOV.QUANTIDADE END AS QUANTIDADE, TITMMOV.VALORBRUTOITEM FROM TMOV 
WITH(NOLOCK)

tente isso e vê se vai!

Read also:

  

/ a>

    
10.07.2017 / 20:28
2

Try to use CASE for the field in question if you are using SQLSERVER.

CREATE TABLE #TABLE_TEMP (ID INT , VALOR NUMERIC(15,2))
INSERT INTO #TABLE_TEMP (ID, VALOR)
VALUES(1,11.00),(2,8.54),(3,NULL),(4,0.00)

SELECT * FROM #TABLE_TEMP
SELECT ID, CASE WHEN VALOR = 0.00 THEN NULL ELSE VALOR END FROM #TABLE_TEMP

There are other ways, but I think it helps you to be simpler.

    
10.07.2017 / 20:51