SQL does not calculate with NULL values

Question

SQL does not calculate with NULL values

Asked 3 years, 10 months ago

Viewed 58 times

1

I have a query where I have 3 columns grouped and last totaling 3, the problem is for Edson the query is not TOTALING.

TOTAL = CREDIT - (DISCOUNT + REBATES)

The total Edson has to give 266,49 but my code does not calculate, due to the "discount" still be NULL.

SELECT
  U.NOME,
  CAST(SUM(R.CAMPOCALC1) as NUMERIC (15,2)) AS CREDITO_FLEX,
  CAST(SUM(R.CAMPOCALC2) AS NUMERIC(15,2)) AS DESCONTO_PRODUTOS_FLEX,
  CAST(SUM(R.CAMPOCALC3) AS NUMERIC(15,2)) AS BONIFICACOES,
  CAST(SUM(R.CAMPOCALC1) as NUMERIC (15,2)) - CAST(SUM(R.CAMPOCALC3) AS NUMERIC(15,2)) - CAST(SUM(R.CAMPOCALC2) AS NUMERIC(15,2)) AS TOTAL_FLEX_VENDEDOR
FROM
  COTACAOI R
  LEFT JOIN COTACAO C ON R.ID_COTACAO = C.ID
  LEFT JOIN CADUSUARIO U ON C.ID_VENDEDOR = U.ID
WHERE
  R.ID_PEDIDO IS NOT NULL AND
  R.CAMPOADICIONAL1 = 'SIM'
GROUP BY
  U.NOME

"Edson’s total has to be 266.49 but my code doesn’t calculate" can you put an example with the table data that has to give this result? Pq cast, what is the data type of CAMPOCALC1, CAMPOCALC2 and CAMPOCALC3 columns?

– Ricardo Pontual

2021/02/16 at 19:10
3

You have to understand the meaning of NULL. NULL means that the value of that attribute is unknown, is an absence of value, so any operation involving NULL will result in NULL. Up to a comparison, using one of the comparison operators (>, <. =, !=, >= or <=) involving a NULL will result in NULL. SQL provides a special operator for you to verify whether or not a field is NULL, the IS NULL and IS NOT NULL.

– anonimo

2021/02/16 at 19:23
If, for special reasons of your application, you want to consider as zero a field that contains NULL use the COALESCE function: COALESCE(campo, 0). This function can have a list of fields as parameters and will return the first field not NULL. For your case SUM(COALESCE(R.CAMPOCALC1,0)) and the same thing in other accumulations.

– anonimo

2021/02/16 at 19:23
@Ricardopunctual the example was above, total = credit - (discount + bonuses) on the cast is changing the number of digits after the comma... all data are numerical.

– Vinicius Ferreira

2021/02/16 at 19:40
This is why it is important to set a default value for columns that store values with 0. Always. And restrict the ability to save null. NOT NULL

– Mateus

2021/02/16 at 22:46

1 answer

Browser other questions tagged php mysql sql database firebird

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

by Gabriel Santana • **372** points · Answer 1 · 2021-02-16T19:09:41+00:00

Try using the ISNULL function to see if it solves according to the example below:

SELECT
  U.NOME,
  CAST(SUM(R.CAMPOCALC1) as NUMERIC (15,2)) AS CREDITO_FLEX,
  CAST(SUM(CASE WHEN ISNULL(R.CAMPOCALC2) THEN 0 ELSE R.CAMPOCALC2 END) AS NUMERIC(15,2)) AS DESCONTO_PRODUTOS_FLEX,
  CAST(SUM(R.CAMPOCALC3) AS NUMERIC(15,2)) AS BONIFICACOES,
  CAST(SUM(R.CAMPOCALC1) as NUMERIC (15,2)) - CAST(SUM(R.CAMPOCALC3) AS NUMERIC(15,2)) - CAST(SUM(CASE WHEN ISNULL(R.CAMPOCALC2) THEN 0 ELSE R.CAMPOCALC2 END ) AS NUMERIC(15,2)) AS TOTAL_FLEX_VENDEDOR