How to add two columns?

Asked

Viewed 5,013 times

0

I have 2 subselects, in SQL, that bring 2 different information, with the sum data. How to add 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
sql sql-server query

  • Make the result a subquery, or use with transforming the result into a common table: with resultado as ( /* Seu select */) select D3_QUANT_pc+D3_QUANT_not_pc as Soma

  • Sqlserver or mysql? WITH not supported in mysql.

  • @Thiagoalessandro: I notice that you are using what was proposed in http://answall.com/questions/185921/como-utilizar-o-like-em-uma-compara%C3%A7%C3%a3o-de-campos-em-tabelas-diferentes

2 answers

1

It seems to me that the section you posted is a correlated subconsultation. 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, he accesses the table 2 times (once for each totalization), which does not seem to me efficient.

Evaluate whether the following draft code 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_ <> '*';

0

Try to do so:

SELECT (D3_QUANT_pc + D3_QUANT_not_pc) AS resultado FROM tabela_a

Browser other questions tagged sql sql-server query

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