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.
– NR_Andre
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
– Motta