Changing NULL values within SQL query

[OmenChild]

Hello All,
I am trying to run a comparison query on Net Pay results over a four week period. Right now the data the query returns contains NULL values for specific weeks. I would like to convert these NULL values into 0's so I can average the Net Pay for all four weeks. I have been trying to use the CASE function, but WinSQL doesn't seem to like the way I am writing it. Here is the portion of the code where I am trying to implement the CASE function. I've also included one of the temp tables I am using.


NET_TMP_THREE
(EMPLID, NET_PAY, PAY_END_DT)
AS
(
SELECT A.EMPLID, B.NET_PAY, B.PAY_END_DT

FROM NET_PAY A LEFT OUTER JOIN PS_PAY_CHECK B
ON A.EMPLID = B.EMPLID
AND B.PAY_END_DT = '2007-10-14'

)

SELECT
A.COMPANY, A.PAYGROUP, A.PAYCHECK_NBR, A.OFF_CYCLE, A.EMPLID, A.EMPL_RCD, A.NAME,
A.DEPTID, A.SSN, A.PAY_END_DT, (CASE WHEN CHAR(A.NET_PAY)= 'NULL' THEN CHAR(A.NET_PAY) = '0' END) AS WEEK_1,
B.PAY_END_DT, (CASE WHEN CHAR(B.NET_PAY)= 'NULL' THEN CHAR(B.NET_PAY) = '0' END) AS WEEK_2, C.PAY_END_DT,
(CASE WHEN CHAR(C.NET_PAY)= 'NULL' THEN CHAR(C.NET_PAY) = '0' END) AS WEEK_3, D.PAY_END_DT,
(CASE WHEN CHAR(D.NET_PAY)= 'NULL' THEN CHAR(D.NET_PAY) = '0' END) AS WEEK_4

FROM NET_PAY A LEFT OUTER JOIN NET_TMP_ONE B
ON A.EMPLID = B.EMPLID
LEFT OUTER JOIN NET_TMP_TWO C
ON A.EMPLID = C.EMPLID
LEFT OUTER JOIN NET_TMP_THREE D
ON A.EMPLID = D.EMPLID

Here is the error I am receiving when I attempt to run it:

SQL0104N An unexpected token "=" was found following "THEN CHAR(A.NET_PAY)". Expected tokens may include: "CONCAT". SQLSTATE=42601

Can anyone offer me some assistance on this. Thank you

 

[JarlH]

CASE expression is right, but COALESCE is a special version of CASE which is even better in your case(!):

COALESCE(A.NET_PAY,'0') will return the character '0' when A.NET_PAY is null.

BTW, don't check for NULL's using the = comparison, write IS NULL (or IS NOT NULL).

 

[OmenChild]

Thanks JarlH,
I went ahead and made the changes, and now I am receiving an entirely different error. Here is the way the code looks now:


NET_TMP_THREE
(EMPLID, NET_PAY, PAY_END_DT)
AS
(
SELECT A.EMPLID, B.NET_PAY, B.PAY_END_DT

FROM NET_PAY A LEFT OUTER JOIN PS_PAY_CHECK B
ON A.EMPLID = B.EMPLID
AND B.PAY_END_DT = '2007-10-14'

)

SELECT
A.COMPANY, A.PAYGROUP, A.PAYCHECK_NBR, A.OFF_CYCLE, A.EMPLID, A.EMPL_RCD, A.NAME,
A.DEPTID, A.SSN, A.PAY_END_DT, COALESCE(A.NET_PAY,'0') AS WEEK_1,
B.PAY_END_DT, COALESCE(B.NET_PAY,'0') AS WEEK_2, C.PAY_END_DT,
COALESCE(C.NET_PAY,'0') AS WEEK_3, D.PAY_END_DT,
COALESCE(D.NET_PAY,'0') AS WEEK_4

FROM NET_PAY A LEFT OUTER JOIN NET_TMP_ONE B
ON A.EMPLID = B.EMPLID
LEFT OUTER JOIN NET_TMP_TWO C
ON A.EMPLID = C.EMPLID
LEFT OUTER JOIN NET_TMP_THREE D
ON A.EMPLID = D.EMPLID

And now I am receiving this error:

SQL0171N The data type, length or value of argument "2" of routine "SYSIBM.COALESCE" is incorrect.

Thank you again for any and all assistance you can give me.

 

[notadba]

Hi OmenChild,

You have probably fixed this by now...but remove the quotes arount the 0 in your coalesce statemtns and it chould be ok. This was fine before when converting char, but now it is looking for a numeric value

COALESCE(C.NET_PAY,0)

 

[OmenChild]

Thank you all so much for your help. I finally got it to work by using a combination of CASE and COALESCE functions.
It's not the prettiest thing in the world, but it works. Oh and I had to do a bit of hardcoding for the PAY_END_DT. Take a look:


SELECT
A.COMPANY, A.PAYGROUP, A.PAYCHECK_NBR, A.OFF_CYCLE, A.EMPLID, A.EMPL_RCD, A.NAME,
A.DEPTID, A.SSN, A.PAY_END_DT,

COALESCE(A.NET_PAY,0) AS WEEK_1,

CASE WHEN B.PAY_END_DT IS NULL THEN '2007-10-28' ELSE B.PAY_END_DT END AS PAY_END_DT,

COALESCE(B.NET_PAY,0) AS WEEK_2,

CASE WHEN C.PAY_END_DT IS NULL THEN '2007-10-21' ELSE C.PAY_END_DT END AS PAY_END_DT,

COALESCE(C.NET_PAY,0) AS WEEK_3,

CASE WHEN D.PAY_END_DT IS NULL THEN '2007-10-14' ELSE D.PAY_END_DT END AS PAY_END_DT,

COALESCE(D.NET_PAY,0) AS WEEK_4



I would now like to average out the rows for each of the final NET_PAY values. Everything I've read indicates averaging can only be done on columns. Is there any way to average out the rows covering the four week period for each record? Thanks again, you all are awesome!

 

[JarlH]

I suppose you can replace the remaining CASE's with COALESCE's.

E.g.
CASE WHEN B.PAY_END_DT IS NULL THEN '2007-10-28' ELSE B.PAY_END_DT END AS PAY_END_DT,
=>
COALESCE(B.PAY_END_DT,'2007-10-28') AS PAY_END_DT

(Just make sure every COALESCE has compatible data types for all its return values. And thats also required for a regular CASE.)