Return zero when no record is found

Asked

Viewed 55 times

-1

I have a table that basically exist the Branch, the Revenue Code, the period of this revenue and the value of the revenue.

I need that when generating the select it brings me information of the chosen period and the two previous months.

In the select I made, it only works when the recipe code exists for the 3 periods. When there is a recipe code in the chosen period that does not exist in the previous two months, select returns nothing.

I need the previous months that there is no such prescription code, be returned 0.

Follow my select:

SELECT AA.ZZV_FILIAL,
   AA.ZZV_PERIOD,
   AA.ZZV_TREC,
   AA.ZZV_DREC,
   AA.ZZV_CCUSTO,
   AA.ZZV_NOME,
   SUM(AA.ZZV_VALOR) AS 'PERIODO1',
   CASE
       WHEN SUM(BB.ZZV_VALOR) IS NULL THEN '0'
       ELSE SUM(BB.ZZV_VALOR)
   END AS 'PERIODO2',
   CASE
       WHEN SUM(CC.ZZV_VALOR) IS NULL THEN '0'
       ELSE SUM(CC.ZZV_VALOR)
   END AS 'PERIODO3'
FROM ZZV020 AS AA
INNER JOIN ZZV020 AS BB ON (BB.ZZV_PERIOD = '201905'
                            AND BB.ZZV_FILIAL = '0177')
AND (BB.ZZV_TREC = AA.ZZV_TREC
     AND BB.ZZV_CCUSTO = AA.ZZV_CCUSTO)
INNER JOIN ZZV020 AS CC ON (CC.ZZV_PERIOD = '201904'
                            AND CC.ZZV_FILIAL = '0177')
AND (CC.ZZV_TREC = AA.ZZV_TREC
     AND CC.ZZV_CCUSTO = AA.ZZV_CCUSTO)
WHERE AA.ZZV_FILIAL = '0177'
  AND AA.ZZV_PERIOD = '201906'
  AND AA.D_E_L_E_T_ = ''
GROUP BY AA.ZZV_FILIAL,
         AA.ZZV_PERIOD,
         AA.ZZV_TREC,
         AA.ZZV_DREC,
         AA.ZZV_CCUSTO,
         AA.ZZV_NOME
ORDER BY AA.ZZV_TREC,
         AA.ZZV_CCUSTO

Thanks for your help!

sql

  • Rafael, please give more details, such as which DBMS you are using, an example on SQL Fiddle it would be interesting too

  • 1

    "works only when the recipe code exists for the 3 periods", the recipe code you speak are the columns: AA.ZZV_VALOR, BB.ZZV_VALOR and CC.ZZV_VALOR?

  • The COALESCE function does what you need. COALESCE(field, 0).

1 answer

0

Hello as already mentioned you should put which database, I assume it is Mysql, I share what seems to me to be the solution, although you do not have much information:

SELECT AA.ZZV_FILIAL,
   AA.ZZV_PERIOD,
   AA.ZZV_TREC,
   AA.ZZV_DREC,
   AA.ZZV_CCUSTO,
   AA.ZZV_NOME,
   SUM(AA.ZZV_VALOR) AS 'PERIODO1',
   IFNULL(SUM(BB.ZZV_VALOR),0) AS 'PERIODO2',
   IFNULL(SUM(CC.ZZV_VALOR),0) AS 'PERIODO3',
FROM ZZV020 AS AA
INNER JOIN ZZV020 AS BB ON (BB.ZZV_PERIOD = '201905'
                            AND BB.ZZV_FILIAL = '0177')
AND (BB.ZZV_TREC = AA.ZZV_TREC
     AND BB.ZZV_CCUSTO = AA.ZZV_CCUSTO)
INNER JOIN ZZV020 AS CC ON (CC.ZZV_PERIOD = '201904'
                            AND CC.ZZV_FILIAL = '0177')
AND (CC.ZZV_TREC = AA.ZZV_TREC
     AND CC.ZZV_CCUSTO = AA.ZZV_CCUSTO)
WHERE AA.ZZV_FILIAL = '0177'
  AND AA.ZZV_PERIOD = '201906'
  AND AA.D_E_L_E_T_ = ''
GROUP BY AA.ZZV_FILIAL,
         AA.ZZV_PERIOD,
         AA.ZZV_TREC,
         AA.ZZV_DREC,
         AA.ZZV_CCUSTO,
         AA.ZZV_NOME
ORDER BY AA.ZZV_TREC,
         AA.ZZV_CCUSTO

Browser other questions tagged sql

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