ORACLE SQL: How do I replace NULL with 0 in a Pivot function

Question

How can I replace NULL with 0 in a PIVOT function on ORACLE SQL? This is the query I'm trying to write:

SELECT * 
FROM
(
SELECT DISTINCT
    CUSTOMER_ID AS CUSTOMER_ID,
    CASE  
        WHEN CATEGORY_CODE = '01' THEN 'CAT 01'
        WHEN CATEGORY_CODE = '02' THEN 'CAT 02'
        WHEN CATEGORY_CODE = '03' THEN 'CAT 03'
        ELSE 'OTHER' END AS CATEGORY,
    SUM(ORDERS) AS ORDERS
FROM
    TABLE_01
GROUP BY
    CUSTOMER_ID,
    CASE  
        WHEN CATEGORY_CODE = '01' THEN 'CAT_01'
        WHEN CATEGORY_CODE = '02' THEN 'CAT_02'
        WHEN CATEGORY_CODE = '03' THEN 'CAT_03'
        ELSE 'OTHER' END
)
PIVOT
    (
    SUM(ORDERS)
    FOR CATEGORY IN 
        (
        'CAT_01',
        'CAT_02',
        'CAT_03',
        'OTHER'
        )
    )
)
;

What I would like is to have a table that when a customer doesn't have any order on a specific category, it would return 0 instead of NULL. like this:

CUSTOMER_ID   CAT_01   CAT_02   CAT_03
00001              0      100        0
00002            100        0        0
00003              0        0      100

Please, keep in mind that this is a very simplified part of a complex query with several categories and nested queries.

Answer 1

You'll have to change the select * part of your query at the top to specify the columns individually, so that you can wrap them in calls to nvl. You can also use coalesce if you like.

select customer_id,
       nvl(cat_01, 0) as cat_01, 
       nvl(cat_02, 0) as cat_02, 
       nvl(cat_03, 0) as cat_03,
       nvl(other, 0) as other
from (...