SQL does not calculate with NULL values

Asked

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.

fotobd

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
php mysql sql database firebird

  • "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?

  • 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.

  • 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.

  • @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.

  • 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

1 answer

1

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

  • it is also possible to perform the same done with coalesce, it is less complex also

  • So @Abriel appreciate the return, I got it with the case but I thought the problem was in the column "DISCOUNT" but the problem was in the "TOTAL" I changed and now it worked.

Browser other questions tagged php mysql sql database firebird

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