Is there any way to default the field values from a subquery aggregate function to zero when no results are returned when using a * rather than naming the fields and using NVL or COALESCE?
e.g. Here is an over simplified version of what I have:
SELECT *
FROM WIDGETS
LEFT JOIN (
SELECT widget_id, count(*) as total,
SUM(CASE WHEN status='closed' THEN 1 ELSE 0 END) AS open,
SUM(CASE WHEN status='open' THEN 1 ELSE 0 END) AS closed
FROM WIDGET_ISSUES
GROUP BY widget_id
) AS ISSUES
ON WIDGETS.widget_id=ISSUES.widget_id
When doing that query there are widgets that don't currently have any issues, and therefore the totals should return zero. However, since the given widget_id doesn't exist within the issues table, there are no results and the values for "open" and "closed" are null rather than zero.
I understand that rather than doing a SELECT * from the main query, I could do: NVL(open,0) AS open, NVL(closed,0) AS closed
However, I'm trying not to do that because unlike this simple example the real example is rather complicated and there are a very large number of these aggregate function values. I'm trying to prevent the main query from needing to be changed when the subquery is changed to keep things simpler to maintain.
A perfectly acceptable answer is "This is not possible, you have to use NVL"; I just wanted to make sure there weren't any creative solutions before I landed on that solution; as it would make my life much easier long-term if this could be done ONLY requiring the subquery to change.
If you ready to incur the cost of an additional JOIN
SELECT *
FROM WIDGETS
LEFT JOIN (
SELECT w.widget_id, count(status) as total,
SUM(CASE WHEN status='closed' THEN 1 ELSE 0 END) AS open,
SUM(CASE WHEN status='open' THEN 1 ELSE 0 END) AS closed
FROM WIDGETS w
left join WIDGET_ISSUES wi on w.widget_id=wi.widget_id
GROUP BY w.widget_id
) AS ISSUES
ON WIDGETS.widget_id=ISSUES.widget_id