Solved

Overiding a Null in a Sql statement using an aggregate function

Posted on 2013-11-04
4
297 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
  • 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 39

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

770 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