How to return zero when JOIN does not bring me a value?

Asked

Viewed 597 times

0

I have a problem with this SQL, when I need to list a drive but SMTITMVE the code has not yet been recorded. Ai when JOIN AND A.NCODIMVEPI = E.NCODIMVEPI.and JOIN AND D.NCODIEQEPI = E.NCODIEQEPI. No, return. how can I treat this problem in SQL?

SELECT A.NCODIMVEPI,
       D.CCODIEQEPI,
       A.DDATAMVEPI,
       B.CNOMEFUNCI || ' / CRACHA& ' || B.CCHRMFUNCI || ' / SITUACAO& ' ||
       C.CDESCSITFU NOME
  FROM SMTMVEPI A, SMTFUNCI B, SMTSITFU C,SMTEQEPI  D,SMTITMVE E
 WHERE A.NCODIFUNCI = B.NCODIFUNCI
 AND A.NCODIMVEPI = E.NCODIMVEPI
   AND B.NCODISITFU = C.NCODISITFU
   AND A.DDATAMVEPI BETWEEN &INICIO AND &FIM
   AND B.CNOMEFUNCI || ' / CRACHA& ' || B.CCHRMFUNCI || ' / SITUACAO& ' ||
       C.CDESCSITFU LIKE &FUNCIONARIO || '%'
   AND B.NCODIGOEMPRE = &EMPRESA
  AND  D.NCODIEQEPI = E.NCODIEQEPI
 ORDER BY 2, 3;
sql oracle

  • I suggest you take a look at the function COALESCE.

  • Search by OUTER JOINS and NVL https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_2048.htm#OLADM624

1 answer

0

It was settled with a OUTER JOIN, I forced to bring the data even if they were null.

SELECT A.NCODIMVEPI,
       D.CCODIEQEPI,
       A.DDATAMVEPI,
       B.CNOMEFUNCI || ' / CRACHA& ' || B.CCHRMFUNCI || ' / SITUACAO& ' ||
       C.CDESCSITFU NOME
  FROM SMTMVEPI A, SMTFUNCI B, SMTSITFU C, SMTEQEPI D, SMTITMVE E
 WHERE A.NCODIFUNCI = B.NCODIFUNCI
   AND A.NCODIMVEPI = E.NCODIMVEPI(+)
   AND B.NCODISITFU = C.NCODISITFU
   AND A.DDATAMVEPI BETWEEN &INICIO AND &FIM
   AND B.CNOMEFUNCI || ' / CRACHA& ' || B.CCHRMFUNCI || ' / SITUACAO& ' ||
       C.CDESCSITFU LIKE &FUNCIONARIO || '%'
   AND B.NCODIGOEMPRE = &EMPRESA
   AND D.NCODIEQEPI(+) = E.NCODIEQEPI
 ORDER BY 2, 3;

Browser other questions tagged sql oracle

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