Do not return a field value when it is zero

Asked

Viewed 873 times

2

I’m creating a consultation on SQL, but I need a certain field not to display the values when it is zero.

It is 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)
sql database sql-server

  • Henrique, it is possible yes, but could be more specific, putting your SQL, and highlighting the fields you want not to be displayed?

  • Don’t want me to display the line field? Or don’t want me to display the line? I reiterate what @jmfrolim asked to be more specific. Examples would help a lot

3 answers

3

Yes this is possible through the IF command

Thus : 

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

You can also customize 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 how it works. 

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 :

CASE COMMAND

  • 1

    There is no command IF in the SQL Server

  • Where it specified to be SQL SERVER?

  • In tags of the question: sql sql-server

  • In this case I will punctuate your answer! But the question also refers to the SQL language and not only to those who implement it!

  • It was bad if it was not clear that it is SQL Server. But I really could not with the sentence explained by the colleague

  • I don’t know if it helps, but I need the TITMMOV.QUANTITY field to return blank when the value is zero.

  • The @sorack querie is correct! You can change your question and post the querie upstairs!

  • follow query performed "SELECT GFILIAL.NOMEFANTASIA AS 'Branch', TMOV.CODTMV AS 'CODTMOV', TMOV.NUMEROMOV AS 'Number', TMOV.VALORLIQUIDO AS 'Valor líquido', TMOV.VALORBRUTOORIG AS 'Valor bruto', TITMMOV.NSEQITMMOV AS 'ITEM', TITMMOV.IDPRD AS 'IDPRODUTO', TPRODUTO.CODIGOPRD AS 'Cód. product', TPRODUTO.NOMEFANTASIA AS 'Produto', TITMMOV.CODUND AS 'Unidade', TITMMOV.PRECOUNITARIO ,TITMMOV.QUANTIDADE, TITMMOV.VALORBRUTOITEM 
FROM TMOV WITH(NOLOCK)"

Show 3 more comments

3


Use the clause CASE:

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.

  • In this case I will have to inform a condition (Case WHEN) for each query field? I am beginner in SQL. Patience with me guys.

  • @Henriquebonfim I think it would be interesting if you put an example of the result you seek in your question, otherwise it will be difficult to understand what you meant

  • example: raincoat product | quantity 0.00 when the quanitty (titmmov.quantity) is 0, I wish the value did not arise. I could tell?

  • @Henriquebonfim in case by value you mean the line?

  • yes. tried the "... CASE TITMMOV.QUANTITY WHEN 0 THEN '' ELSE TITMMOV.QUANTITY END AS QUANTITY..." however returned the following error: Msg 8114, Level 16, State 5, Line 1 Error Converting data type varchar to Numeric.

  • @Henriquebonfim The problem is that you put with ' and this makes BD try to convert the value.

  • As I would in this case, since from what I understand the form of writing empty is ' '?

  • @Henriquebonfim vazio is NULL unless you wanted to return a string empty

  • Thank you very much, guys. I got this way "CASE TITMMOV.QUANTITY WHEN '0.0000' THEN NULL ELSE TITMMOV.QUANTITY END AS 'Quantity'"... plus RM Totvs resources to not show nulls, the result was the correct one.

Show 4 more comments

2

Try to use the CASE for the field in question, if 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 this one already helps you because it’s simpler.

Browser other questions tagged sql database sql-server

You are not signed in. Login or sign up in order to post.