Return all results in a grouped query even if the sum is zero or null

Asked

Viewed 32 times

0

I have the following consultation grouped:

SELECT 

SUM(SUC.PESO),
EXTRACT(DAY FROM HU.FIM),

CASE WHEN (ACO.COLUNA_2 IN ('L','C') AND HU.ESP_MM_UNMET<=3) THEN 'Carb' WHEN (ACO.COLUNA_2 IN ('L','C') 
AND HU.ESP_MM_UNMET>3) THEN 'Carb' WHEN (SUBSTR(ACO.COLUNA_1,1,3)='P39') THEN 'Duplex' WHEN (SUBSTR(ACO.COLUNA_1,1,2)='P3') THEN '3XX' 

WHEN (SUBSTR(ACO.COLUNA_1,1,2)='P2') THEN '3XX' 
WHEN (SUBSTR(ACO.COLUNA_1,1,2)='P4') THEN '4XX' 
WHEN (ACO.COD_IDENT_FMACO='GNO') THEN 'GNO' 
WHEN (SUBSTR(ACO.COLUNA_1,1,4)='P999') THEN 'GO' 
WHEN (SUBSTR(ACO.COLUNA_1,1,4)='P989') THEN 'GO' ELSE 'XXX' END TIPO_PRODUTO



FROM 
PROCESSO_HIST HU, 
PROCESSO_HIST HU1, 
ACO, 
SUCATAS SUC, 
CODIGOS COD 
WHERE 
HU.COD_IDENT_ACO = ACO.COD_IDENT_ACO AND 
HU.COD_IDENT_PRODC= SUC.COD_IDENT_PRODCAND 
HU.SEQ_OPERA_PRODC= SUC.SEQ_OPERA_PRODCAND 
HU1.COD_IDENT_PRODC= HU.COD_IDENT_PRODCAND 
HU1.SEQ_OPERA_PRODC= (HU.SEQ_OPERA_PRODC-1) AND 
SUC.COD_IDENT_GERAL = COD.COD_IDENT_GERAL(+) AND  
COD.CODIGO_TYPE(+) = 'MOTSUCLTQ' AND  
SUC.CODIGO_TYPE = 'MOTSUCA' AND 
HU.COD_EQPMT_PRODC IN ('LDP', 'LTQ', 'BTQ', 'DBQ', 'TL8') AND 
NOT EXISTS 
( 
SELECT '1' 
FROM 
PROCESSO_HIST HU2, 
ACO A 
WHERE 
A.COD_IDENT_ACO = HU2.COD_IDENT_ACO AND 
HU2.COD_IDENT_PRODC= HU.COD_IDENT_PRODCAND 
HU2.SEQ_OPERA_PRODC= HU.SEQ_OPERA_PRODCAND 
HU2.COD_EQPMT_PRODC = 'EMB' AND 
(A.COD_IDENT_ACO NOT LIKE 'L%' AND A.COD_IDENT_ACO NOT LIKE 'C%') 
) 
AND 
HU.COD_EQPMT_PRODC_DEPOSITO <> 'SUC' AND 
HU.FIM >= TO_DATE('01052019000000', 'DDMMYYYYHH24MISS') AND 
HU.FIM <= TO_DATE('31052019235959', 'DDMMYYYYHH24MISS')
GROUP BY
EXTRACT(DAY FROM HU.FIM),
CASE WHEN (ACO.COLUNA_2 IN ('L','C') AND HU.ESP_MM_UNMET<=3) THEN 'Carb' WHEN (ACO.COLUNA_2 IN ('L','C') 
AND HU.ESP_MM_UNMET>3) THEN 'Carb' WHEN (SUBSTR(ACO.COLUNA_1,1,3)='P39') THEN 'Duplex' WHEN (SUBSTR(ACO.COLUNA_1,1,2)='P3') THEN '3XX' 

WHEN (SUBSTR(ACO.COLUNA_1,1,2)='P2') THEN '3XX' 
WHEN (SUBSTR(ACO.COLUNA_1,1,2)='P4') THEN '4XX' 
WHEN (ACO.COD_IDENT_FMACO='GNO') THEN 'GNO' 
WHEN (SUBSTR(ACO.COLUNA_1,1,4)='P999') THEN 'GO' 
WHEN (SUBSTR(ACO.COLUNA_1,1,4)='P989') THEN 'GO' ELSE 'XXX' END

ORDER BY
EXTRACT(DAY FROM HU.FIM),
CASE WHEN (ACO.COLUNA_2 IN ('L','C') AND HU.ESP_MM_UNMET<=3) THEN 'Carb' WHEN (ACO.COLUNA_2 IN ('L','C') 
AND HU.ESP_MM_UNMET>3) THEN 'Carb' WHEN (SUBSTR(ACO.COLUNA_1,1,3)='P39') THEN 'Duplex' WHEN (SUBSTR(ACO.COLUNA_1,1,2)='P3') THEN '3XX' 

WHEN (SUBSTR(ACO.COLUNA_1,1,2)='P2') THEN '3XX' 
WHEN (SUBSTR(ACO.COLUNA_1,1,2)='P4') THEN '4XX' 
WHEN (ACO.COD_IDENT_FMACO='GNO') THEN 'GNO' 
WHEN (SUBSTR(ACO.COLUNA_1,1,4)='P999') THEN 'GO' 
WHEN (SUBSTR(ACO.COLUNA_1,1,4)='P989') THEN 'GO' ELSE 'XXX' END

This query returns me the sum of a database field with the following structure:

1º -> Sum of weight 2º -> Day of the sum 3º -> Product Type Group.

The problem is that I don’t have all the products in every day and so currently if I had the production of two products on the 1st it returns me the following result;

Product Weight Day

10000 1 3XX

56000 1 4XX

Only that I need the consultation always return all types of product, even if on the day it has not had production, for example on the first day it should return me:

Product Weight Day

10000 1 3XX

56000 1 4XX

0 1 Carb

0 1 Duplex

0 1 GNO

0 1 GO

  • Hello! See if you can get the result you need using left Join in the table of what brings the product, and you will need to treat the fields that may be null.

  • A basic idea and generate "zero" values and aggregate https://forum.imasters.com.br/topic/522521-for%C3%A7ar-select-bring-line-even-worthless/? tab=comments#comment-2079599 https://forum.imasters.com.br/topic/543061-query-com-contador-a-partir-de-um-mes/? do=findComment&comment=2169834

No answers

Browser other questions tagged

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