Joins: How to Return default values for empty right side of left outer join

Question

In my Oracle DB, I have a left outer join for a parent workorder to its child workorders. I then run a calculation performing a SUM() of some of the child values. I wrap the results from the child workorders in Nvl() to ensure they'll be calculated correctly.

This works, except when there are no child workorders for the parent. In this case, the "nulls" returned during display are because there are no results in the join, and thus it appears that Nvl([fieldname],0) doesn't convert them to show 0. Thus, when I think sum the child values with the parent values, they also return null due to adding a value to a null value.

What's the best way to get around this? Is this something that can be worked around or is it a smell that something is wrong with my query fundamentally?

The Query

Sorry, I'm not able to post a setup for it at the moment. For this specific workorder (hard-coded), the "right hand" part of the join is empty because the parent has no children, and thus displays null.

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';

Chandu · Accepted Answer · 2012-03-02T03:25:39.943

You need to apply NVL after the results are returned by LEFT join.

Try this:

SELECT  WOINFO.*
                , TASKWORKORDRENUMBER
                , TASKPARENT
                , NVL(TOTALMATESTCOSTFORWO_TASK,0) AS TOTALMATESTCOSTFORWO_TASK
                , NVL(TOTALLABESTCOSTFORWO_TASK,0) AS TOTALLABESTCOSTFORWO_TASK
                , NVL(TOTALMATACTCOSTFORWO_TASK,0) AS TOTALMATACTCOSTFORWO_TASK
                , NVL(TOTALLABACTCOSTFORWO_TASK,0) AS TOTALLABACTCOSTFORWO_TASK
                , NVL(TOTALLABACTHOURSFORWO_TASK,0) AS TOTALLABACTHOURSFORWO_TASK
FROM   R_PCR_ALLWOSANDTASKSSEPARATELY WOINFO
       LEFT OUTER JOIN (SELECT WORKORDERNUMBER                AS TASKWORKORDRENUMBER
                               , PARENT                       AS TASKPARENT
                               , TOTALMATESTCOSTFORWO_TASK
                               , TOTALLABESTCOSTFORWO_TASK
                               , TOTALMATACTCOSTFORWO_TASK
                               , TOTALLABACTCOSTFORWO_TASK
                               , TOTALLABACTHOURSFORWO_TASK
                        FROM   R_PCR_ALLWOSANDTASKSSEPARATELY) TASKINFO
         ON ( WOINFO.WORKORDERNUMBER = TASKINFO.TASKPARENT )
WHERE  WORKORDERNUMBER = '2826059';

Or COALESCE. And now something to make the comment big enough. — Shannon Severance, Mar 02 '12 at 06:05
Yep! That was absolutely it. Completely obvious, too, but not so much after a long day of staring at SQL. Thanks! — SeanKilleen, Mar 03 '12 at 15:24

Joins: How to Return default values for empty right side of left outer join

The Query

1 Answers1