Post reply

divide by zero error

  • September 24, 2004 at 3:27 am

    #89434

    Can anyone help me with this. I am using Winnt 4 server and SQL 7. I input this query statement:

    "SELECT QNumber, Bulletin, Issue, Locale, Installed, Applicable, Installed + Applicable as Total, (100 * Installed / (Applicable + Installed)) as '% Compliant' from (select QNumbers0 as QNumber, ID0 as Bulletin, Title0 as Issue, LocaleID0 as Locale, sum (case when patch.Status0 = 'Installed' then 1 else 0 end) as Installed, sum (case when patch.Status0 = 'Applicable' then 1 else 0 end) as Applicable from v_GS_PatchState as patch join v_GS_WORKSTATION_STATUS WS on patch.ResourceID=WS.ResourceID where WS.LastHWScan >= (getdate()-3) Group By QNumbers0, ID0, Title0, LocaleID0) as ps Order BY '% Compliant' desc"

    It worked fine for some time, now all I get is divide by zero error. I do not have any arith.. as an option

  • September 24, 2004 at 4:36 am

    #524304

    Change the query to this and you should see the records that are causing the problem.

    If zero values are valid for the "Installed" and "Applicable" fields, then wrap your division in a case statement.

    SELECT 
QNumber, Bulletin, Issue, Locale, Installed, Applicable, 
--Installed + Applicable as Total, (100 * Installed /(Applicable + Installed)) as '% Compliant' 
from ( select QNumbers0 as QNumber
, ID0 as Bulletin
, Title0 as Issue
, LocaleID0 as Locale
, sum (case when patch.Status0 = 'Installed' then 1 else 0 end) as Installed
, sum (case when patch.Status0 = 'Applicable' then 1 else 0 end) as Applicable 
from v_GS_PatchState as patch 
join v_GS_WORKSTATION_STATUS WS 
on patch.ResourceID=WS.ResourceID 
where WS.LastHWScan >= (getdate()-3) 
Group By QNumbers0, ID0, Title0, LocaleID0
) as ps 
Where (Installed + Applicable) = 0
Order BY '% Compliant' desc"

    --------------------
    Colt 45 - the original point and click interface

  • September 27, 2004 at 8:14 am

    #524520

    Thanks but, thant did not work.

    I was able to able to get a response from Query Analyzer by opening the query connection option, deselect the "use Ansi nulls, padding and warning options".

     

    However, the problem still persist running the query from an ASP webpage.

    I have changed the connection setting for the database using Enterprise Manager and with sp_configure and still no go

    Thanks again

    Tony: 

  • September 27, 2004 at 9:27 am

    #524537

    Phil's query will give errors, I think this what he meant.

    SELECT

    QNumber, Bulletin, Issue, Locale, Installed, Applicable,

    Installed + Applicable as Total

    from ( select QNumbers0 as QNumber

    , ID0 as Bulletin

    , Title0 as Issue

    , LocaleID0 as Locale

    , sum (case when patch.Status0 = 'Installed' then 1 else 0 end) as Installed

    , sum (case when patch.Status0 = 'Applicable' then 1 else 0 end) as Applicable

    from v_GS_PatchState as patch

    join v_GS_WORKSTATION_STATUS WS

    on patch.ResourceID=WS.ResourceID

    where WS.LastHWScan >= (getdate()-3)

    Group By QNumbers0, ID0, Title0, LocaleID0

    ) as ps

    Where (Installed + Applicable) = 0

    This should tell you what data is causing the problem. Also make sure this query is run on the same database/server as the ASP page !

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply