How to return empty value?

Asked

Viewed 448 times

0

By adding up any positive number with the result of a Subquery get as return null and need to return 0 so it is possible to add with the first value obtained.

What can I do to make the query return me an entire amount?

Segue Query:

DECLARE @TOTAL VARCHAR(100)

SET @TOTAL = (SELECT TOP 1 PTB.PATSLDCTAVALCONTAB 
                FROM PAT_SALDO_CONTA PTB  WITH (NOLOCK), CONTAB_SALDO_MOV_CTA CS WITH (NOLOCK)
               WHERE CS.PlanoCtaCodRed = PTB.PlanoCtaCodRed
                 AND PTB.PlanoCtaCodRed  = '18872'
                 AND PTB.PatSldCtaAnoMes = '201809' 
                 AND PTB.EmpCod like '%' + (CASE WHEN '01.13' = '01' THEN '01.' ELSE '01.13' END)+ '%')
            +(SELECT @TOTAL 
                     + COALESCE((SELECT (SUM(ISNULL(CSA.ContabSaldoMovCtaValDeb,0))) 
                                       -(SUM(ISNULL(CSA.ContabSaldoMovCtaValCred,0))) 
                                   FROM CONTAB_SALDO_MOV_CTA CSA 
                                  WHERE CSA.PlanoCtaCodRed = '18872' 
                                    AND CSA.ContabSaldoMovCtaAnoMes >= '2018' + '' + '10'
                                    AND CSA.ContabSaldoMovCtaAnoMes <= '2018' + '' + '10'
                                    AND CSA.EmpCod like '%' + (CASE WHEN '01.13' = '01' THEN '01.' ELSE '01.13' END) + '%')
                                ,0))

SELECT @TOTAL
sql-server

  • I saw you use the ISNULL() within select... Could not use it again? for example TOTAL + ISNULL((SELECT....), 0)

  • yes but it returns 0, I wanted it to return 10, type 10 + 0 = 10

  • Felipe, I suggest you first separate the sum to check the returned values on total and in the subselect. After finding that the problem is unique to the return of the subselect try to put do as Matheus suggested. It is possible that the total is different from 10 as quoted in the comment above and so the return is 0?

2 answers

1

How your query can return a value null, treat her return with the ISNULL or COALESCE setting a value 0, which should solve your problem.

COALESCE(value, default)

The ISNULL and the COALESCE return the default if the valor be it NULL... So in your case, for example:

TOTAL = 10
QUERY = COALESCE(NULL, 0)

Would you return 10 + 0 = 10... If you want, you can change the default and for the amount you want.

Try it like this:

TOTAL + ISNULL(
 (SELECT (SUM(ISNULL(CSA.ContabSaldoMovCtaValDeb,0))) - (SUM(ISNULL(CSA.ContabSaldoMovCtaValCred,0))) FROM CONTAB_SALDO_MOV_CTA CSA 
   WHERE CSA.PlanoCtaCodRed          = @P_PLANO_CTA_COD_RED 
     AND CSA.ContabSaldoMovCtaAnoMes >= @p_ANO_INI + '' + @p_MES_INI
     AND CSA.ContabSaldoMovCtaAnoMes <= @p_ANO_FIM + '' + @p_MES_FIM
     AND CSA.EmpCod like '%' 
         + (CASE WHEN @P_EMP_COD = '01' THEN '01.' 
            ELSE @P_EMP_COD END) + '%')
  ,0)

or 

TOTAL + COALESCE(
 (SELECT (SUM(ISNULL(CSA.ContabSaldoMovCtaValDeb,0))) - (SUM(ISNULL(CSA.ContabSaldoMovCtaValCred,0))) FROM CONTAB_SALDO_MOV_CTA CSA 
   WHERE CSA.PlanoCtaCodRed          = @P_PLANO_CTA_COD_RED 
     AND CSA.ContabSaldoMovCtaAnoMes >= @p_ANO_INI + '' + @p_MES_INI
     AND CSA.ContabSaldoMovCtaAnoMes <= @p_ANO_FIM + '' + @p_MES_FIM
     AND CSA.EmpCod like '%' 
         + (CASE WHEN @P_EMP_COD = '01' THEN '01.' 
            ELSE @P_EMP_COD END) + '%')
  ,0)

EDITED

With all your script now you understand a little better... Try this way

DECLARE @TOTAL NUMERIC(18,2)

SET @TOTAL = (SELECT TOP 1 PTB.PATSLDCTAVALCONTAB 
                FROM PAT_SALDO_CONTA PTB  WITH (NOLOCK), CONTAB_SALDO_MOV_CTA CS WITH (NOLOCK)
               WHERE CS.PlanoCtaCodRed = PTB.PlanoCtaCodRed
                 AND PTB.PlanoCtaCodRed  = '18872'
                 AND PTB.PatSldCtaAnoMes = '201809' 
                 AND PTB.EmpCod like '%' + (CASE WHEN '01.13' = '01' THEN '01.' ELSE '01.13' END)+ '%')
            +(SELECT COALESCE(@TOTAL, 0)
                     + COALESCE((SELECT (SUM(ISNULL(CSA.ContabSaldoMovCtaValDeb,0))) 
                                       -(SUM(ISNULL(CSA.ContabSaldoMovCtaValCred,0))) 
                                   FROM CONTAB_SALDO_MOV_CTA CSA 
                                  WHERE CSA.PlanoCtaCodRed = '18872' 
                                    AND CSA.ContabSaldoMovCtaAnoMes >= '2018' + '' + '10'
                                    AND CSA.ContabSaldoMovCtaAnoMes <= '2018' + '' + '10'
                                    AND CSA.EmpCod like '%' + (CASE WHEN '01.13' = '01' THEN '01.' ELSE '01.13' END) + '%')
                                ,0))

SELECT @TOTAL

  • did it return always empty? had to return 10 + 0 = 10, and returned 0

  • @Felipemichaeldafonseca If you only run the query select alone, which return?

  • NULL, pq has no value

  • @Felipemichaeldafonseca I edited my answer, read it again, because the COALESCE serves to do just that, return a default value if your query returns NULL

  • Dude worked out, if you put 10 it returns the 10 msm, pq in the query has no value, only I will edit the question to you see that I have a query before and it returns a value like 9302.00 adding with the 2 query that is zero it returns null

  • @Felipemichaeldafonseca Why you created the variable @TOTAL of the kind VARCHAR(100) if you want to use it as numerical value? Check the editing of my reply!

Show 1 more comment

0

use the COALESCE function

would be so:

COALESCE(TOTAL + (
 SELECT (SUM(ISNULL(CSA.ContabSaldoMovCtaValDeb,0))) - 
  (SUM(ISNULL(CSA.ContabSaldoMovCtaValCred,0))) FROM CONTAB_SALDO_MOV_CTA CSA 
    WHERE CSA.PlanoCtaCodRed = @P_PLANO_CTA_COD_RED AND 
        CSA.ContabSaldoMovCtaAnoMes  >= @p_ANO_INI + '' + @p_MES_INI
        AND CSA.ContabSaldoMovCtaAnoMes <= @p_ANO_FIM + '' + @p_MES_FIM
    AND 
  CSA.EmpCod like '%' + (CASE WHEN @P_EMP_COD = '01' THEN '01.' ELSE 
  @P_EMP_COD END) + '%'
  )),0)

in your case use so

TOTAL + COALESCE((
 SELECT (SUM(ISNULL(CSA.ContabSaldoMovCtaValDeb,0))) - 
  (SUM(ISNULL(CSA.ContabSaldoMovCtaValCred,0))) FROM CONTAB_SALDO_MOV_CTA CSA 
    WHERE CSA.PlanoCtaCodRed = @P_PLANO_CTA_COD_RED AND 
        CSA.ContabSaldoMovCtaAnoMes  >= @p_ANO_INI + '' + @p_MES_INI
        AND CSA.ContabSaldoMovCtaAnoMes <= @p_ANO_FIM + '' + @p_MES_FIM
    AND 
  CSA.EmpCod like '%' + (CASE WHEN @P_EMP_COD = '01' THEN '01.' ELSE 
  @P_EMP_COD END) + '%'
  ), 0)

what it does is return the value set after the comma if the query value is null or false.

Browser other questions tagged sql-server

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