Pivot - NULL to 0 1

[Andrzejek]

I have this SQL:
[tt]
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)
)
[/tt]
and the outcome of it looks like this:

[pre]
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
[/pre]

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)

[pre]
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
[/pre]

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.

 

[IanWaterman]

Try

NVL(SUM(AMT),0)

Ian

 

[Andrzejek]

Tried that, got an error:
[tt]ORA-56902: expect aggregate function inside pivot operation[/tt]
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.

 

[IanWaterman]

Did you try

SUM(NVL(AMT,0))

Ian

 

[Andrzejek]

Yes, I did try [tt]SUM(NVL(AMT,0))[/tt], that does not change anything, NULLs still appear. :-(

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[fredericofonseca]

maybe something like (untested and not sure about the "2018" naming

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

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Andrzejek]

Thank you Frederico, it worked with a little modification
Had to replace some ) with "

[pre]
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) )
[/pre]


Have fun.

---- Andy

There is a great need for a sarcasm font.