SQL ROUND function error

zintech
zintech used Ask the Experts™
on
I have the following SQL code that I would like to execute, but it says there is an error near "(" and I can't figure out what it is.  I am guessing it has something to do with the ROUND function.

UPDATE EVM
SET PercentComplete = @PercentComplete,
ROUND((EV = case when Budget > 0 then  
(PercentComplete / 100) * Budget
else
0
end), 2)
 WHERE ID = @ID
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
I presume you want this?
UPDATE EVM 
   SET PercentComplete = @PercentComplete
     , EV = ROUND((case when Budget > 0 then (PercentComplete / 100) * Budget  else 0 end), 2)
 WHERE ID = @ID 

Open in new window

Think you are trying populate value of EV based on the condition.
This is how it should work.

UPDATE EVM
SET PercentComplete = @PercentComplete,
EV = CASE when Budget > 0 then
ROUND((PercentComplete / 100) * Budget,2)
else
0
end
WHERE ID = @ID
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
angelIII has the right syntax.
I doubt you will ever have negative budget, so this alone works the same.
Note I changed 100 to 100.0 to force it to go to decimals.

Also, did you mean to include the MySQL zone?

UPDATE EVM SET
  PercentComplete = @PercentComplete,
  EV = ROUND(PercentComplete / 100.0 * Budget, 2)
WHERE ID = @ID

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial