How to return empty value?

Question

How to return empty value?

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

Adding any positive number to the result of a Subquery returns as null and needs to return 0 so that it can be added with the first value obtained.

What can I do to get the query to return an integer value?

Follow 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

asked by anonymous 27.11.2018 / 12:06

2 answers

Function being executed before receiving the value in ngModel What does it mean (Char) 0 / (Char) 8 / (Char) 13?

score 1 · Answer 1

Since your query can return a value of null , treat its return with ISNULL or COALESCE by setting a value of 0, which should solve your problem.

COALESCE (value, default)

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

TOTAL = 10
QUERY = COALESCE(NULL, 0)

I would return you 10 + 0 = 10 ... If you want, you can change default and whatever value you want.

Try 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 can understand a little better ... Try it like this

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

score 0 · Answer 2

use the COALESCE function

would look like this:

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 it as well

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 to return the value set after the comma if the value of the query is null or false.