INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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:

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.

RE: Pivot - NULL to 0

Try

NVL(SUM(AMT),0)

Ian

RE: Pivot - NULL to 0

(OP)
Tried that, got an error:
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... sad

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

Did you try

SUM(NVL(AMT,0))

Ian

RE: Pivot - NULL to 0

(OP)
Yes, I did try SUM(NVL(AMT,0)), that does not change anything, NULLs still appear. sad

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Pivot - NULL to 0

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

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

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

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close