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 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 it so that when the subquery is updated/changed, that the main query needs to also be changed; as doing so is more likely to cause errors.
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.