How to add 2 columns?

0

I have 2 subselects , SQL , which bring 2 different information with the sum of the data. How to do to sum the 2 " sums " and bring in a new column, or a single column?

CASE
    WHEN sd3.D3_UM = 'PC' THEN
           (SELECT ISNULL(sum(sd3_sub1.D3_QTSEGUM),0) AS D3_QUANT_pc
            FROM SD3010 AS sd3_sub1 WITH(NOLOCK)
            WHERE sd3_sub1.D3_TM = '010'
              AND sd3_sub1.D3_LOCAL IN ('01',
                                        '02',
                                        '98')
              AND sd3_sub1.D3_EMISSAO BETWEEN (LEFT(sd3.D3_EMISSAO,6) + '01') AND (LEFT(sd3.D3_EMISSAO,6) + '31')
              AND sd3_sub1.D3_FILIAL = sd3.D3_FILIAL
              AND sd3_sub1.D3_CC LIKE LEFT(sd3.D3_CC,5) + '%'
              AND sd3_sub1.D3_UM = 'PC'
              AND sd3_sub1.D_E_L_E_T_ <> '*'
            GROUP BY sd3_sub1.D3_UM)
    ELSE
           (SELECT ISNULL(sum(sd3_sub1.D3_QUANT),0) AS D3_QUANT_not_pc
            FROM SD3010 AS sd3_sub1 WITH(NOLOCK)
            WHERE sd3_sub1.D3_TM = '010'
              AND sd3_sub1.D3_LOCAL IN ('01',
                                        '02',
                                        '98')
              AND sd3_sub1.D3_EMISSAO BETWEEN (LEFT(sd3.D3_EMISSAO,6) + '01') AND (LEFT(sd3.D3_EMISSAO,6) + '31')
              AND sd3_sub1.D3_FILIAL = sd3.D3_FILIAL
              AND sd3_sub1.D3_CC LIKE LEFT(sd3.D3_CC,5) + '%'
              AND sd3_sub1.D3_UM <> 'PC'
              AND sd3_sub1.D_E_L_E_T_ <> '*'
            GROUP BY sd3_sub1.D3_UM)
END producao
    
asked by anonymous 06.04.2017 / 12:55

2 answers

1

It seems to me that the post you posted is a correlated subquery. Evaluate the following code:

-- código #1 v4
SELECT ...,
       (SELECT sum(case when sd3_sub1.D3_UM = 'PC' then sd3_sub1.D3_QTSEGUM else sd3_sub1.D3_QUANT end) 
          from SD3010 as sd3_sub1 WITH(NOLOCK)
          where sd3_sub1.D3_TM = '010'
                and sd3_sub1.D3_LOCAL IN ('01','02','98')
                and sd3_sub1.D3_EMISSAO BETWEEN (LEFT(sd3.D3_EMISSAO,6) + '01') AND (LEFT(sd3.D3_EMISSAO,6) + '31')
                and sd3_sub1.D3_FILIAL = sd3.D3_FILIAL
                and sd3_sub1.D3_CC LIKE LEFT(sd3.D3_CC,5) + '%'
                and sd3_sub1.D3_UM = sd3.D3_UM
                and sd3_sub1.D_E_L_E_T_ <> '*') as producao,
       (SELECT sum(case when sd3_sub1.D3_UM = 'PC' then sd3_sub1.D3_QTSEGUM else sd3_sub1.D3_QUANT end) 
          from SD3010 as sd3_sub1 WITH(NOLOCK)
          where sd3_sub1.D3_TM = '010'
                and sd3_sub1.D3_LOCAL IN ('01','02','98')
                and sd3_sub1.D3_EMISSAO BETWEEN (LEFT(sd3.D3_EMISSAO,6) + '01') AND (LEFT(sd3.D3_EMISSAO,6) + '31')
                and sd3_sub1.D3_FILIAL = sd3.D3_FILIAL
                and sd3_sub1.D3_CC LIKE LEFT(sd3.D3_CC,5) + '%'
                and sd3_sub1.D_E_L_E_T_ <> '*') as producao_total
  from SD3010 AS sd3 WITH(NOLOCK)
  where ...

However, it accesses the table 2 times (once for each totalization), which does not seem to me efficient.

Evaluate whether the following code sketch generates the expected result and is more efficient:

-- código #2 v2
-- informe o período de emissão
declare @dataInicial char(8), @dataFinal char(8);
set @dataInicial= '20170101';
set @dataFinal= '20170131';

--
with sd3_sub1 as (
SELECT D3_FILIAL, left(D3_CC,5) as D3_CC_5, left(D3_EMISSAO, 6) as D3_EMISSAO_6,
       sum (case when D3_UM = 'PC' then D3_QTSEGUM else 0 end) as D3_QUANT_pc,
       sum (case when D3_UM <> 'PC' then D3_QUANT else 0 end) as D3_QUANT_not_pc,
       sum (case when D3_UM = 'PC' then D3_QTSEGUM else D3_QUANT end) as D3_QUANT_total
  from SD3010 
  where D3_TM = '010'
        and D3_LOCAL in ('01','02','98')
        and D3_EMISSAO between @dataInicial and @dataFinal
        and D_E_L_E_T_ <> '*'
  group by D3_FILIAL, left(D3_CC,5), left(D3_EMISSAO, 6)
)
SELECT sd3.D3_FILIAL, ...,
       sd3_sub1.D3_QUANT_pc, sd3_sub1.D3_QUANT_not_pc,
       sd3_sub1.D3_QUANT_total
  from SD3010 as sd3 WITH(NOLOCK)
       left join sd3_sub1 on sd3_sub1.D3_FILIAL = sd3.D3_FILIAL
                             and sd3_sub1.D3_CC_5 = left(sd3.D3_CC_5, 5)
                             and sd3_sub1.D3_EMISSAO_6 = left(sd3.D3_EMISSAO, 6)
  where ...
        and sd3.D3_EMISSAO between @dataInicial and @dataFinal
        and sd3.D_E_L_E_T_ <> '*';
    
09.04.2017 / 22:45
0

Try to do this:

SELECT (D3_QUANT_pc + D3_QUANT_not_pc) AS resultado FROM tabela_a
    
06.04.2017 / 22:46