Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    7

    Unanswered: Divide by 0 in query

    Hi, I want to something very simple...

    a query that divides two fields and does not crap out when it gets a divide by 0 error.

    this is my query...

    select
    nodes.shortname,
    nodes.inviteecount,
    cache.respondentcount,
    (cache.respondentcount/nodes.inviteecount * 100) as percentage
    from cpd_orgnodes13 nodes, cpd_rollupcache102 cache
    where
    cache.functioncode = nodes.functioncode
    and nodes.depth = 1
    order by shortname

    PLEASE HELP!!

  2. #2
    Join Date
    Nov 2003
    Location
    denver
    Posts
    11
    CREATE FUNCTION returnNullIf0 (@Num Varchar(100))
    RETURNS Varchar(100) AS
    --if the passed value is 0, the function returns null
    --needs to be used whereever the value is used as denominator
    BEGIN
    DECLARE @NewNum as varchar(100)
    if isnumeric(@Num) = 1
    Begin
    IF round(@Num, 5)= 0
    BEGIN
    Select @NewNum = null
    END
    Else
    Select @NewNum = @Num
    END
    ELSE
    BEGIN
    Select @NewNum = @Num
    END
    Return(@NewNum)
    END

    go


    select
    nodes.shortname,
    nodes.inviteecount,
    cache.respondentcount,
    (returnNullIf0(cache.respondentcount)/returnNullIf0(nodes.inviteecount) * 100) as percentage
    from
    cpd_orgnodes13 nodes, cpd_rollupcache102 cache
    where
    cache.functioncode = nodes.functioncode
    and nodes.depth = 1
    order by shortname

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: Divide by 0 in query

    Originally posted by ngillis
    Hi, I want to something very simple...

    a query that divides two fields and does not crap out when it gets a divide by 0 error.

    this is my query...

    select
    nodes.shortname,
    nodes.inviteecount,
    cache.respondentcount,
    (cache.respondentcount/nodes.inviteecount * 100) as percentage
    from cpd_orgnodes13 nodes, cpd_rollupcache102 cache
    where
    cache.functioncode = nodes.functioncode
    and nodes.depth = 1
    order by shortname

    PLEASE HELP!!
    Also, you could use case:

    (cache.respondentcount/case when isnnodes.inviteecount=0
    then 1 -- or whatever you want
    else isnnodes.inviteecount
    end * 100)

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It somewhat depends on what you want to return when the divisor is zero. Zero? Null? A message?

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •