Overiding a Null in a Sql statement using an aggregate function

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
BrockAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Shaun KlineConnect With a Mentor Lead Software EngineerCommented:
You may be able to use the ISNULL function:

ISNULL((SELECT Statement), 0)
0
 
lcohanDatabase AnalystCommented:
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
 
BrockAuthor Commented:
You are great !

Thanks,
Nigluc
0
 
BrockAuthor Commented:
Thanks everyone.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.