Abnormal Error : Divison Zero with OLEDB Provider

Question

Abnormal Error : Divison Zero with OLEDB Provider

Ragesh Chavarattil

Old Hand

Points: 328
More actions
July 5, 2005 at 11:45 am

#362999

Hi Friends,
My asp page is returning Divizion by zero error from the OLEDB Provider. I can open the recordset. But it occurs only while I loop through the records. The Query runs in SQL Query Analyzer very well, I can open the recordset through OLEDB Provider. But it prompts Division by zero error only after 3 records while issuing the Recordset.Movenext command.
I am using SQL Server 2000 in Win 2003 envirnment.
My Query:
--------------------------------------------------------------------------
SELECT DATEPART(mm, K.[Date]) AS [Month], K.[KPI_Code], K.[Field_Code], K.[Field_Desc], K.[KPI_Value], K.[SubValue_1], CASE WHEN M.[MasterValue] IS NULL THEN NULL ELSE CONVERT(DECIMAL(5,2), (K.[SubValue_1] * 100) /M.[MasterValue]) END AS [ReturnPercent]
FROM [OPS_KPI_Volume_Test] AS K
LEFT OUTER JOIN
(
SELECT [Field_Code], [SubValue_1] AS [MasterValue] FROM [OPS_KPI_Volume_Test]
WHERE [KPI_Code] = 7 and DATEPART(mm, [Date]) = 6
) AS M
ON K.[ReturnFieldCode] = M.[Field_Code]
WHERE K.[KPI_Code] = 7 and DATEPART(mm, K.[Date]) = 6
ORDER BY K.DisplayOrder
---------------------------------------------------------------------------
Is anyone there can help me? My email is cragesh@gmail.com
Regards,
Ragesh Chavarattil

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

This should work.

SELECT DATEPART(mm, K.[Date]) AS [Month], K.[KPI_Code], K.[Field_Code], K.[Field_Desc], K.[KPI_Value], K.[SubValue_1], CASE WHEN ISNULL(M.[MasterValue], 0) = 0 THEN NULL ELSE K.[SubValue_1] * 100.0 /M.[MasterValue] END AS [ReturnPercent]

FROM [OPS_KPI_Volume_Test] AS K

LEFT OUTER JOIN

(

SELECT [Field_Code], [SubValue_1] AS [MasterValue] FROM [OPS_KPI_Volume_Test]

WHERE [KPI_Code] = 7 and DATEPART(mm, [Date]) = 6

) AS M

ON K.[ReturnFieldCode] = M.[Field_Code]

WHERE K.[KPI_Code] = 7 and DATEPART(mm, K.[Date]) = 6

ORDER BY K.DisplayOrder

noeld SSC Guru Points: 96590 More actions · Answer 2

OR:

SELECT DATEPART(mm, K.[Date]) AS [Month], K.[KPI_Code], K.[Field_Code], K.[Field_Desc], K.[KPI_Value], K.[SubValue_1], K.[SubValue_1] * 100.0 /NULLIF(M.[MasterValue],0) AS [ReturnPercent]

FROM [OPS_KPI_Volume_Test] AS K

LEFT OUTER JOIN

(

SELECT [Field_Code], [SubValue_1] AS [MasterValue] FROM [OPS_KPI_Volume_Test]

WHERE [KPI_Code] = 7 and DATEPART(mm, [Date]) = 6

) AS M

ON K.[ReturnFieldCode] = M.[Field_Code]

WHERE K.[KPI_Code] = 7 and DATEPART(mm, K.[Date]) = 6

ORDER BY K.DisplayOrder

* Noel

Ragesh Chavarattil Old Hand Points: 328 More actions · Answer 3

Thanks Remi Gregoire. It is working. But shall I know why my code is returning error even after opening the recordset. Also, I don't have zeros in my table.

Regards,

Ragesh

Ragesh Chavarattil Old Hand Points: 328 More actions · Answer 4

Ragesh Chavarattil

Old Hand

Points: 328

July 6, 2005 at 12:41 am

#571109

Thanks. It is working.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

I can only guess that the calculated part is done client side... but that doesn't make much sens. Maybe the set options are different on query analyser (can't go much further in details than that).