avoiding divide by zero in update select statement

[lhatwwp]

Hello,

I just ran into a problem with an update statement in a stored procedure that cursors through a table. In my statement I SELECT a formula and sometime the formula can create a divide by zero fault. How can I avoid the divide by zero error?

UPDATE MyTable
SELECT [C1] = 1 / (5 - [C2]) 

-- IF [C2] value = 5 the update fails
-- IF [C2] value < 5 the update works

 

[ethorn10]

something along the lines of:

IF C2 > 4
   BEGIN
      'do something other than update here
   END
ELSE
   BEGIN
      UPDATE MyTable SELECT [C1] = 1 / (5 - [C2])
   END

 

[lhatwwp]

Thanks for the reply. I have tried a number of solutions like that. Using IF statements and CASE statements. The problem is the UPDATE statement is inside a loop iterating through the table records.

Here's my actual code, maybe this will help better define the problem I'm having. Right now the error message is related to the CASE statement. I can replace it with IF and I have tried SET ExhaustFlow = IF [O2] = 0... same problem.

Thanks again,
Lou

BEGIN
	SET NOCOUNT ON;
	DECLARE @Exh decimal(6,1)
	DECLARE OneHourTable CURSOR LOCAL
	FOR 
	SELECT DateAndTime, FuelFlow, FuelEnergy, ExhaustFlow, O2, CO, NOx from tblOneHour WHERE DateAndTime >= @StartDate Order by DateAndTime desc
	OPEN OneHourTable
	FETCH NEXT FROM OneHourTable
	WHILE @@FETCH_STATUS = 0
	BEGIN

		UPDATE tblOneHour
	        CASE [O2] 
	        WHEN 20.9 THEN  @Exh = 0
	        ELSE @Exh = 240*[FuelFlow]/35.3145*[FuelEnergy]*35.3145/948600*(20.9/(20.9-[O2])
	        END
			SET ExhaustFlow = @Exh,
			CO_24AVG = dbo.CalculateRollingCOAverage(DateAndTime,1),
			CO_720AVG = dbo.CalculateRollingCOAverage(DateAndTime,30),
			NOx_24AVG = dbo.CalculateRollingNOxAverage(DateAndTime,1),
			NOx_720AVG = dbo.CalculateRollingNOxAverage(DateAndTime,30)
		WHERE CURRENT OF OneHourTable
		FETCH NEXT FROM OneHourTable
	END
	
	CLOSE OneHourTable
	DEALLOCATE OneHourTable
END

 

[gmmastros]

        UPDATE tblOneHour
        [!]Set    ExhaustFlow =[/!]  CASE [O2]
            WHEN 20.9 THEN  0
            ELSE 240*[FuelFlow]/35.3145*[FuelEnergy]*35.3145/948600*(20.9/(20.9-[O2])
            END,
            CO_24AVG = dbo.CalculateRollingCOAverage(DateAndTime,1),
            CO_720AVG = dbo.CalculateRollingCOAverage(DateAndTime,30),
            NOx_24AVG = dbo.CalculateRollingNOxAverage(DateAndTime,1),
            NOx_720AVG = dbo.CalculateRollingNOxAverage(DateAndTime,30)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom