SQL HELP!!!!

Question

SQL HELP!!!!

s c-244333

Newbie

Points: 2
More actions
July 7, 2005 at 12:52 pm

#89942

Can someone please help me out here... i need to write a SQL statement as such:
SELECT
value1 AS 'VALUE1', value2/value1 AS 'VALUE2', value 3 AS 'VALUE3', value2/value3 AS 'VALUE4'
FROM dbo.tblValues VL
WHERE VL.ID = @ID
BUT I need to put in some kind of statement to avoid a divide by 0 so that if value1 is 0 then dont do the calculation and put a null or N/A in the table instead and same for value3
any help would be greatly appreciated by email address is schoudhr@hotmail.com

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

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

K. Brian Kelley SSC Guru Points: 114704 More actions · Answer 1

Use a CASE statement. For instance:


SELECT
  value1 AS 'VALUE1',
  CASE
    WHEN VALUE1 = 0 THEN value2
    ELSE value2/value1
  END 'VALUE2',
  value3 AS 'VALUE3',
  CASE
    WHEN VALUE3 = 0 THEN value2
    ELSE value2/value3
  END 'VALUE4'
FROM dbo.tblvalues VL
WHERE VL.ID = @ID

K. Brian Kelley
@kbriankelley

noeld SSC Guru Points: 96590 More actions · Answer 2

OR use NULLIF

SELECT value1 AS 'VALUE1',

value2/NULLIF(value1,0) 'VALUE2',

value3 AS 'VALUE3',

value2/NULLIF(value3,0) 'VALUE4'

FROM dbo.tblvalues VL

WHERE VL.ID = @ID

* Noel

s c-244333 Newbie Points: 2 More actions · Answer 3

problem solved!

thanks a whole lot guys

i am most grateful indeed