Help with SQL IF - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help with SQL IF

Author

Topic

eljapo4
Posting Yak Master

100 Posts

Posted - 2012-05-01 : 07:35:41

Hi I've the following code as part of a select statement:


CASE WHEN D1_Factor_ID = '1' THEN
  SUM(EffectiveMinutes * D1_Factor_PC)/100
ELSE
  0
END AS [Personal_Needs],			
IF (Personal_Needs <> 0)
SUM(EffectiveMinutes) + SUM(EffectiveMinutes * D1_Factor_PC)/100 AS 'SAM Recovery + Personal Needs Total',

Can I use the created col from the CASE in my IF? At the minute I'm getting the following errors:
Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'IF'.
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near 'SUM'.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-01 : 08:25:44

You cannot use aliases you define in the select clause within the same select clause (or anywhere else in that select statement , except in the order by clause). So what you may need to do is something like this where I am repeating the logic:

CASE WHEN D1_Factor_ID = '1' THEN
  SUM(EffectiveMinutes * D1_Factor_PC)/100.0
ELSE
  0
END AS [Personal_Needs],

CASE WHEN D1_Factor_ID = '1' AND SUM(EffectiveMinutes * D1_Factor_PC)/100.0 <> 0 THEN
	SUM(EffectiveMinutes) + SUM(EffectiveMinutes * D1_Factor_PC)/100.0
ELSE
	0
END AS 'SAM Recovery + Personal Needs Total',

One other thing to keep in mind is the following: If your Effective minutes and D1_Factor_PC are integers, the resulting division will end up being an integer division, which may not be what you want. Hence my adding a ".0" (see in red) (to force it to have fractional parts) in the above query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 10:09:57

if you dont want to repeat the case then only alternative is


SELECT ...,
CASE WHEN(Personal_Needs <> 0) THEN 
SUM(EffectiveMinutes) + SUM(EffectiveMinutes * D1_Factor_PC)/100 END AS 'SAM Recovery + Personal Needs Total',
...
FROM
(

SELECT CASE WHEN D1_Factor_ID = '1' THEN
  SUM(EffectiveMinutes * D1_Factor_PC)/100
ELSE
  0
END AS [Personal_Needs],			
...

)t
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

eljapo4
Posting Yak Master

100 Posts

Posted - 2012-05-02 : 04:44:41

thank you both for your replies, they helped me solve me problem.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-02 : 19:42:27

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources