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

Changing NULL values within SQL query

Changing NULL values within SQL query

(OP)
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

RE: Changing NULL values within SQL query

hi,
you use isnull function in query

example:
ISNULL(ColumnName, 0)
if in columnname value in null then this process convert into 0.

Regards,

SHERAZ AHMED

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