In my Oracle database, I have a left outer join for the parent work order for its child workflows. Then I run a calculation that performs SUM () of some child values. I am finalizing the results of child workflows in Nvl () to make sure that they are correctly calculated.
This works, unless there are no parent guardians for the parent. In this case, the "nulls" returned during the display are due to the fact that there are no results in the union, and therefore it seems that Nvl ([fieldname], 0) does not convert them to show 0. Thus, when I I think child values with parent values, they also return null due to adding a value to a null value.
What is the best way to get around this? Is it something that can be circumvented or is it a smell, is something wrong with my request at the root?
Request
Sorry, I cannot post the settings for this at the moment. For this particular working order (hard-coded), the right-hand part of the connection is empty because the parent has no children and therefore displays zero.
SELECT * FROM (SELECT * FROM R_PCR_ALLWOSANDTASKSSEPARATELY)WOINFO LEFT OUTER JOIN (SELECT WORKORDERNUMBER AS TASKWORKORDRENUMBER , PARENT AS TASKPARENT , NVL(TOTALMATESTCOSTFORWO, 0) AS TOTALMATESTCOSTFORWO_TASK , NVL(TOTALLABESTCOSTFORWO,0) AS TOTALLABESTCOSTFORWO_TASK , NVL(TOTALMATACTCOSTFORWO,0) AS TOTALMATACTCOSTFORWO_TASK , NVL(TOTALLABACTCOSTFORWO,0) AS TOTALLABACTCOSTFORWO_TASK , NVL(TOTALLABACTHOURSFORWO,0) AS TOTALLABACTHOURSFORWO_TASK FROM R_PCR_ALLWOSANDTASKSSEPARATELY)TASKINFO ON ( WOINFO.WORKORDERNUMBER = TASKINFO.TASKPARENT ) WHERE WORKORDERNUMBER = '2826059';
source share