Show 0 if recordset value is null

Discussion in 'ASP General' started by GTN170777, Jul 25, 2008.

  1. GTN170777

    GTN170777 Guest

    Just a quick question,

    I've got a recordset like --

    SELECT Count(JBESSiteID) AS TotalJBESent, M.Month, SUM(JBESReceipients) AS
    Emails
    FROM dbo.JBESent J RIGHT OUTER JOIN Months M ON DATEPART(month, JBESDate) =
    M.Month AND JBESSiteID = MMColParam AND DATEPART(year, JBESDate) =
    DATEPART(year, GETDATE()) GROUP BY Month
    ORDER BY Month

    Which works great, but I need it to return a valu of 0 if Emails
    SUM(JBESReceipients) is Null,.. any ideas?

    Thanks again
     
    GTN170777, Jul 25, 2008
    #1
    1. Advertising

  2. GTN170777 wrote:
    > Just a quick question,
    >
    > I've got a recordset like --
    >
    > SELECT Count(JBESSiteID) AS TotalJBESent, M.Month,
    > SUM(JBESReceipients) AS Emails
    > FROM dbo.JBESent J RIGHT OUTER JOIN Months M ON DATEPART(month,
    > JBESDate) = M.Month AND JBESSiteID = MMColParam AND DATEPART(year,
    > JBESDate) = DATEPART(year, GETDATE()) GROUP BY Month
    > ORDER BY Month
    >
    > Which works great, but I need it to return a valu of 0 if Emails
    > SUM(JBESReceipients) is Null,.. any ideas?
    >

    What database? Still SQL 2000? Always tell us please.

    Anyways, this is a good situation for the COALESCE function:
    , COALESCE(SUM(JBESReceipients),0) AS Emails

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Jul 25, 2008
    #2
    1. Advertising

  3. GTN170777

    Mangler Guest

    ISNULL(SUM(JBESReceipients) ,0)
     
    Mangler, Jul 30, 2008
    #3
  4. GTN170777

    Evertjan. Guest

    Mangler wrote on 30 jul 2008 in microsoft.public.inetserver.asp.general:

    > ISNULL(SUM(JBESReceipients) ,0)


    In short:

    ISNULL(SUM(JBESRecipients) ,0)

    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
     
    Evertjan., Jul 30, 2008
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    1
    Views:
    651
    Erik Funkenbusch
    Apr 9, 2006
  2. Kevin Munro

    Null columns in recordset

    Kevin Munro, Nov 7, 2003, in forum: Java
    Replies:
    5
    Views:
    4,181
    Kevin Munro
    Nov 11, 2003
  3. Replies:
    5
    Views:
    27,021
    Mike Schilling
    Mar 29, 2006
  4. Hung Huynh
    Replies:
    8
    Views:
    332
    Bob Barrows
    Sep 24, 2003
  5. avcitamer
    Replies:
    6
    Views:
    222
    Bob Barrows [MVP]
    Sep 18, 2008
Loading...

Share This Page