CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
Nick
Nick

Reputation: 3845

Oracle 11g SQL - Replacing NULLS with zero where query has PIVOT

i have the following SQL code which is returning the data i require, but im trying to replace all occurrences of NULL with zero.

        SELECT * from 
( 
    SELECT Table1.ITMCOD, Table1.ITMDSC, Table2.GRPDSC, Table3.ZONDSC,COALESCE(Table4.CASQTY,0) AS QTY,COALESCE(Table4.QASTAT,'0') AS QASTAT 
    FROM Table5 
    JOIN Table1 ON BLDITM.ITMCOD = Table1.ITMCOD 
    JOIN Table3 ON Table5.PUT_ZONLST = Table3.ZONLST 
    JOIN Table2 ON Table2.Group = Table1.Group  
    LEFT JOIN Table4  ON Table1.ITMCOD = Table4.ITMCOD 
) 
PIVOT 
    (Sum(Qty) 
    FOR QASTAT in ('RL' RL,'HD' HD) 
    )order by ITMCOD;

i have tried replacing the COALESCE with NVL but still get the same results as shown. The nulls im trying to get rid of are in the RL and HD columns.They are not actual fields in any of the tables, but are produced by the pivot function.

Note that the first occurrence of Coalesce in my code replaces nulls with an actual zero, while the second is the character '0'. I tried a zero but it gave an error. They should both be numbers (zero)

Im not very experienced in SQL and would really appreciate help on this.

Results

Upvotes: 1

Views: 323

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

Instead of select *, specify the columns generated by the pivoted query; you can then apply nvl() or coalesce() as normal:

SELECT ITMCOD, ITMDSC, GRPDSC, ZONDSC, NVL(RL, 0) RL, NVL(HD, 0) HD
from 
( 
    SELECT Table1.ITMCOD, Table1.ITMDSC, Table2.GRPDSC, Table3.ZONDSC, Table4.CASQTY AS QTY, Table4.QASTAT
    ...

Replacing null quantities within the inner query doesn't really do any thing here, so I've removed those coalesce() calls; the rest remains the same.

Upvotes: 0

Nick
Nick

Reputation: 3845

SOLUTION FOUND!

Out of trial and error i managed to solve this :)

Basically, instead of the select * from at the beginning of the code, i entered the actual field names and the COALESCE functions too as shown,

SELECT ITMCOD, ITMDSC, GRPDSC, ZONDSC,COALESCE(RL,0) AS RL,COALESCE(HD,0) as HD from

i hope this will help anyone else with a similar problem.

thanks

Upvotes: 1

Related Questions