Pivot - NULL to 0
Pivot - NULL to 0
(OP)
I have this SQL:
SELECT * FROM
(SELECT PS5YR_PN_PIN_FK, PS5YR_PN_WORK_TYPE,
PS5YR_PN_FY, PS5YR_PN_PROGRAM_AMT AS AMT
FROM S4111000.PSFIVEYR_PROJ_NO
WHERE (PS5YR_PN_FY BETWEEN 2018 AND 2022)
)
PIVOT
(
SUM(AMT)
FOR PS5YR_PN_FY IN (2018, 2019, 2020, 2021, 2022)
)
and the outcome of it looks like this:
All empty spaces in columns 2018 to 2022 are NULLs
What do I need to do to my SQL to have the outcome like this:
Replace NULLs with 0 (zeros)
The Select statement is a part of an Insert statement. Insert statement adds records to a table where all fields need to have values, so NULLs are not allowed.
SELECT * FROM
(SELECT PS5YR_PN_PIN_FK, PS5YR_PN_WORK_TYPE,
PS5YR_PN_FY, PS5YR_PN_PROGRAM_AMT AS AMT
FROM S4111000.PSFIVEYR_PROJ_NO
WHERE (PS5YR_PN_FY BETWEEN 2018 AND 2022)
)
PIVOT
(
SUM(AMT)
FOR PS5YR_PN_FY IN (2018, 2019, 2020, 2021, 2022)
)
and the outcome of it looks like this:
PS5YR_PN_PIN_FK PS5YR_PN_WORK_TYPE 2018 2019 2020 2021 2022 60 1536 600 600 600 600 600 63 1001 13814 71 1001 5100 78 1536 200 200 200 200 200 82 1509 930 89 2011 30419 1412 1928 16607 89 4551 523 92 5021 76 109 2521 450 123 2513 500 17000
All empty spaces in columns 2018 to 2022 are NULLs
What do I need to do to my SQL to have the outcome like this:
Replace NULLs with 0 (zeros)
PS5YR_PN_PIN_FK PS5YR_PN_WORK_TYPE 2018 2019 2020 2021 2022 60 1536 600 600 600 600 600 63 1001 0 13814 0 0 0 71 1001 0 5100 0 0 0 78 1536 200 200 200 200 200 82 1509 0 930 0 0 0 89 2011 0 30419 1412 1928 16607 89 4551 523 0 0 0 0 92 5021 0 76 0 0 0 109 2521 0 0 450 0 0 123 2513 500 0 0 17000 0
The Select statement is a part of an Insert statement. Insert statement adds records to a table where all fields need to have values, so NULLs are not allowed.
Have fun.
---- Andy
There is a great need for a sarcasm font.
Talk To Other Members
RE: Pivot - NULL to 0
NVL(SUM(AMT),0)
Ian
RE: Pivot - NULL to 0
ORA-56902: expect aggregate function inside pivot operation
I did try NVL, IsNull(), and some other ways to accomplish what I need, but so far - no luck...
For now I've changed the declaration of the fields accepting the data so NULLs can go in there. I may just stick with it, although I would prefer to not deal with NULLs and have 0's in there instead.
Have fun.
---- Andy
There is a great need for a sarcasm font.
RE: Pivot - NULL to 0
SUM(NVL(AMT,0))
Ian
RE: Pivot - NULL to 0
Have fun.
---- Andy
There is a great need for a sarcasm font.
RE: Pivot - NULL to 0
CODE
SELECT PS5YR_PN_PIN_FK , PS5YR_PN_WORK_TYPE , nvl("2018", 0) as "2018) , nvl("2019", 0) as "2019) , nvl("2020", 0) as "2020) , nvl("2021", 0) as "2021) , nvl("2022", 0) as "2022) FROM (SELECT PS5YR_PN_PIN_FK , PS5YR_PN_WORK_TYPE , PS5YR_PN_FY , PS5YR_PN_PROGRAM_AMT AS AMT FROM S4111000.PSFIVEYR_PROJ_NO WHERE (PS5YR_PN_FY BETWEEN 2018 AND 2022) ) PIVOT ( SUM(AMT) FOR PS5YR_PN_FY IN (2018, 2019, 2020, 2021, 2022) )Regards
Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Pivot - NULL to 0
Had to replace some ) with "
SELECT PS5YR_PN_PIN_FK , PS5YR_PN_WORK_TYPE , NVL("2018", 0) AS "2018" , NVL("2019", 0) AS "2019" , NVL("2020", 0) AS "2020" , NVL("2021", 0) AS "2021" , NVL("2022", 0) AS "2022" FROM (SELECT PS5YR_PN_PIN_FK, PS5YR_PN_WORK_TYPE, PS5YR_PN_FY, PS5YR_PN_PROGRAM_AMT AS AMT FROM S4111000.PSFIVEYR_PROJ_NO WHERE (PS5YR_PN_FY BETWEEN 2018 AND 2022) ) PIVOT ( SUM(AMT) FOR PS5YR_PN_FY IN (2018, 2019, 2020, 2021, 2022) )Have fun.
---- Andy
There is a great need for a sarcasm font.