Solved

Overiding a Null in a Sql statement using an aggregate function

Posted on 2013-11-04
4
303 Views
Last Modified: 2013-11-04
Hi Everyone,

Is it possible to overide null statement in the following sql.  Note the field in SPCL_EARNS cannot contain a null value.


UPDATE X
 SET X.SPCL_EARNS =
(SELECT SUM(SPCL.SPCL_EARNS)
 FROM PS_CAAT_TMP TMP
 LEFT OUTER JOIN PS_SPCL_EARNS SPCL
 ON  SPCL.PAGE_NUM = TMP.PAGE_NUM
 AND SPCL.LINE_NUM = TMP.LINE_NUM
 AND SPCL.SEPCHK =   TMP.SEPCHK
 AND SPCL.OFF_CYCLE = TMP.OFF_CYCLE
 AND SPCL.PAY_END_DT = TMP.PAY_END_DT
 AND SPCL.EMPL_RCD = TMP.EMPL_RCD
 AND SPCL.PAYGROUP = TMP.PAYGROUP
 WHERE SPCL.PAY_END_DT >=  CASE WHEN X.MAX_PENS_EFFDT >= '01-01-2013' THEN  X.MAX_PENS_EFFDT ELSE  '01-01-2013' END
 AND SPCL.PAY_END_DT <= '03-23-2013'
 AND TMP.EMPLID = X.EMPLID
 AND TMP.EMPL_RCD = X.EMPL_RCD
 group by TMP.EMPLID, TMP.EMPL_RCD)
 )
 FROM PS_PY_CAAT_CALC  X


Any assistance would be greatly appreciated.   I don't know how to encase this statement in a case statement.

Thank you,
Nigluc
0
Comment
Question by:Lucia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 500 total points
ID: 39622108
You may be able to use the ISNULL function:

ISNULL((SELECT Statement), 0)
0
 
LVL 40

Expert Comment

by:lcohan
ID: 39622130
Would be something like in the query below:

UPDATE X
 SET X.SPCL_EARNS =
isnull(
(
SELECT SUM(SPCL.SPCL_EARNS)
 FROM PS_CAAT_TMP TMP
 LEFT OUTER JOIN PS_SPCL_EARNS SPCL
 ON  SPCL.PAGE_NUM = TMP.PAGE_NUM
 AND SPCL.LINE_NUM = TMP.LINE_NUM
 AND SPCL.SEPCHK =   TMP.SEPCHK
 AND SPCL.OFF_CYCLE = TMP.OFF_CYCLE
 AND SPCL.PAY_END_DT = TMP.PAY_END_DT
 AND SPCL.EMPL_RCD = TMP.EMPL_RCD
 AND SPCL.PAYGROUP = TMP.PAYGROUP
 WHERE SPCL.PAY_END_DT >=  CASE WHEN X.MAX_PENS_EFFDT >= '01-01-2013' THEN  X.MAX_PENS_EFFDT ELSE  '01-01-2013' END
 AND SPCL.PAY_END_DT <= '03-23-2013'
 AND TMP.EMPLID = X.EMPLID
 AND TMP.EMPL_RCD = X.EMPL_RCD
 group by TMP.EMPLID, TMP.EMPL_RCD
)
,0)

FROM PS_PY_CAAT_CALC X
0
 

Author Closing Comment

by:Lucia
ID: 39622136
You are great !

Thanks,
Nigluc
0
 

Author Comment

by:Lucia
ID: 39622137
Thanks everyone.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question