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

0

I have a problem with this SQL, when I need to list a move but the code has not yet been written. There when you fall into this JOIN AND A.NCODIMVEPI = E.NCODIMVEPI .and JOIN AND D.NCODIEQEPI = E.NCODIEQEPI . I do not have it, come back. how can I handle 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;
    
asked by anonymous 18.04.2017 / 14:44

1 answer

0

It was resolved with an OUTER JOIN , I forced to fetch the data even if it was 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;
    
18.04.2017 / 15:37