Replace NULL values from PIVOT query
fperezhnAug 20 2010 — edited Aug 21 2010Hi,
I'm querying a table for sales, for some values, when now columns, the finish with a null value. How can I handle these values as 0 (zeros).
WITH PIVOT_DATA AS (
SELECT S.ZONE_CODE,Z.ZONE_NAME,S.YEAR,S.PERIOD,S.SALES
FROM STAT_TABLE_SALES VTA
JOIN ZONES Z ON S.COMP=Z.COMP AND S.ZONE_CODE=Z.ZONE_CODE
WHERE S.COMP = '001'
AND S.BRAND_CODE = '001'
)
SELECT *
FROM PIVOT_DATA
PIVOT(
SUM(SALES) FOR (YEAR,PERIOD) IN ((2009,1),
(2009,2),
(2009,3),
(2009,4),
(2009,5),
(2009,6),
(2009,7),
(2009,8),
(2009,9),
(2009,10),
(2009,11),
(2009,12),
(2010,1),
(2010,2),
(2010,3),
(2010,4),
(2010,5),
(2010,6),
(2010,7))
)
ORDER BY 14 DESC NULLS LAST;
This query returns the following:
COD_ZONA NOM_ZONA 2009_1 2009_2 2009_3
-------- ------------------------------ ---------------------- ---------------------- --------------------
01 YEDUSIJH. 1382367.75 1559943.27 1441279.64
02 DO,ASKAK 711897.82 865854.05 1583232.3
03 ASDFG 130443.03 205409.84 178633.69
04 OSOIDSD 320118.32 439008.83 409251.18
05 ODFSDF 300908.21 276301.59 260188.53
06 CH 242749.65 325196.71 464938.9
07 SOA 610312.31 606312.93 754569.82
08 SAN 89426.8 81360.04 61649.27
09 YP 284487.79 328281.31 267210.85
10 TC 87043.28 158594.43 85195.8
11 BAGNN 76778.78 68180.76 118530.04
12 CRTS 122023.7 143442.21 134744.85
13 OCC 209992.79 196477.03 185222.14
14 IDLB
15 YA 23870.41 4137.33 16660.53
**These are not all the columns and rows, but this is a piece of what it returns.
How can I replace these NULLS with 0(zeros).