Adding cases to a query

Question

Adding cases to a query

Navigation

#1 by (3 votes)

1

I need to create a query that adds 2 cases , but I can not do it the way I did. If the D3_UM = 'PC' field, it should add the D3_QTSEGUM field. If the field D3_UM <> 'PC' , it should add the field D3_QUANT .

At the end, it should add the fields D3_QTSEGUM and D3_QUANT and bring the result.

Bring the sum of each one, I did, however it lists 2 records (one for when the field is equal to PC and another for when the field is different from PC). Could you give me a light?

Follow script for this. It is a subselect

    SELECT...,
    (SELECT CASE 
                WHEN sd3_sub1.D3_UM = 'PC' THEN sum(sd3_sub1.D3_QTSEGUM) 
                WHEN sd3_sub1.D3_UM <> 'PC' THEN sum(sd3_sub1.D3_QUANT)
         END AS D3_QUANT
                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_ <> '*' group by sd3_sub1.D3_UM) AS Producao
FROM ...

With WITH it worked (below), however, because it is a subselect error in the query, when implemented in the original query subselects as mentioned above).

WITH SOMATORIA (SOMA_TOTAL) AS
(SELECT sum(CASE 
            WHEN sd3_sub1.D3_UM = 'PC' THEN sd3_sub1.D3_QTSEGUM 
            WHEN sd3_sub1.D3_UM <> 'PC' THEN sd3_sub1.D3_QUANT
     END) AS D3_QUANT
            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 ('20170201')
                                                AND ('20170231')                
                --AND sd3_sub1.D3_FILIAL = sd3.D3_FILIAL
                AND sd3_sub1.D3_CC LIKE ('13603%')              
                AND sd3_sub1.D_E_L_E_T_ <> '*'
                group by D3_UM
                )
                SELECT SUM(SOMA_TOTAL) FROM SOMATORIA

sql query script

asked by anonymous 27.03.2017 / 15:02

1 answer

How to focus on a row by the contents of a td column? Display freight calculation on each WP product page

score 3 · Accepted Answer

You can use else in your case :

WITH SOMATORIA (SOMA_TOTAL) AS
  (SELECT CASE WHEN 
        sd3_sub1.D3_UM = 'PC' THEN sum(sd3_sub1.D3_QTSEGUM) 
        ELSE sum(sd3_sub1.D3_QUANT)
   END AS D3_QUANT
        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_ <> '*' group by sd3_sub1.D3_UM)
SELECT SUM(SOMA_TOTAL) FROM SOMATORIA

You'll need to use WITH because SUM would not allow another aggregation or subquery.

See more at Using WITH AS command on Sql Server .