Error in retrieving Data

Iâ'm running the script below, but get the following error message;
Error Message: â'<eb1>Divide by zero error encountered.
State:22012,Native:8134,Origin:[Microsoft][ODBC SQL Server Driver][SQL
Server]</eb1>â'
CREATE View vw_GMB__Lapsers_Decliners_05_to_06 AS
(
SELECT a.BuyerID,
a.SiteName,
a.BuyerSegment as Buyers_05,
b.BuyerSegment as Buyers_06,
CASE WHEN ((b.GMB)-(a.GMB)) = 0 THEN 'No Change'
WHEN ((b.GMB)-(a.GMB))/(a.GMB)*100>=-49.99 THEN 'Neither'
WHEN ((b.GMB)-(a.GMB))/(a.GMB)*100<=-50 AND
((b.GMB)-(a.GMB))/(a.GMB)*100>=-89.99 THEN 'Decliner'
WHEN ((b.GMB)-(a.GMB))/(a.GMB)*100>-90 THEN 'Lapser'
ELSE 'Unknown' END AS Decliners_Lapsers
FROM Lapsers_Decliners_05 AS a LEFT JOIN Lapsers_Decliners_05_to_06 AS b
ON a.BuyerID=b.BuyerID
)On Mar 20, 11:58 am, hiteshiyer <hiteshi...@.discussions.microsoft.com>
wrote:
> I'm running the script below, but get the following error message;
> Error Message: "<eb1>Divide by zero error encountered.
> State:22012,Native:8134,Origin:[Microsoft][ODBC SQL Server Driver][SQL
> Server]</eb1>"
> CREATE View vw_GMB__Lapsers_Decliners_05_to_06 AS
> (
> SELECT a.BuyerID,
> a.SiteName,
> a.BuyerSegment as Buyers_05,
> b.BuyerSegment as Buyers_06,
> CASE WHEN ((b.GMB)-(a.GMB)) = 0 THEN 'No Change'
> WHEN ((b.GMB)-(a.GMB))/(a.GMB)*100>=-49.99 THEN 'Neither'
> WHEN ((b.GMB)-(a.GMB))/(a.GMB)*100<=-50 AND
> ((b.GMB)-(a.GMB))/(a.GMB)*100>=-89.99 THEN 'Decliner'
> WHEN ((b.GMB)-(a.GMB))/(a.GMB)*100>-90 THEN 'Lapser'
> ELSE 'Unknown' END AS Decliners_Lapsers
> FROM Lapsers_Decliners_05 AS a LEFT JOIN Lapsers_Decliners_05_to_06 AS b
> ON a.BuyerID=b.BuyerID
> )
It seems like a.GMB is zero in some cases. I would suggest using a
try...catch block, if you are using SQL Server 2005 (@.@.error if using
SQL Server 2000), to capture the error and maybe have an alternative
query. The syntax for SQL Server's try...catch block is:
begin try
...(create view...)
end try
begin catch
--
end catch
Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer