I have a query using PIVOT function that has NULL values that wish to replace with 0 in the cell. I have tried to add the ISNULL into the sub query against O7CRAM but that did not remove the NULL values
select * from ( select O7ORNO, O7CRID, O7CRAM from MVXJDTA.OOLICH ) src pivot ( sum(O7CRAM) for O7CRID in ([PNP], [DTY], [HAN]) ) piv order by O7ORNO;
The issue I have now is the
ISNULL and where to place it to remove the
Here’s a way to do the same thing with the ancient, arcane, but simple “Black Arts” method known as a CROSSTAB. Notice how you do NOT neet explicit ISNULL()s.
--===== Same as the PIVOT SELECT O7ORNO ,PNP = SUM(IIF(O7CRID = 'PNP',O7CRAM,0) ,DTY = SUM(IIF(O7CRID = 'DTY',O7CRAM,0) ,HAN = SUM(IIF(O7CRID = 'HAN',O7CRAM,0) FROM MVXJDTA.OOLICH GROUP BY O7ORNO ORDER BY O7ORNO ;
Here’s a slightly different take with row/column totals and a grand total.
--===== Same as the PIVOT with row/column totals and grand total SELECT O7ORNO ,PNP = SUM(IIF(O7CRID = 'PNP',O7CRAM,0) ,DTY = SUM(IIF(O7CRID = 'DTY',O7CRAM,0) ,HAN = SUM(IIF(O7CRID = 'HAN',O7CRAM,0) ,RowTotal = SUM(O7CRAM) FROM MVXJDTA.OOLICH GROUP BY O7ORNO WITH ROLLUP ORDER BY GROUPING(O7ORNO),O7ORNO ;
For more information about how CROSSTABs work and for how to make Dynamic CROSSTABS, please see the articles at the following links: