quick if else within case question

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 2005 Forums

Transact-SQL (2005)

quick if else within case question

Author

Topic

theroversreturn
Starting Member

12 Posts

Posted - 2012-02-13 : 10:45:18

Hi,
I have the following code snippet which keeps giving me an incorrect syntax error.

....
,ls1.start_datetime as licence_start,
case b.user_id
when null
then ls1.expiry_datetime
else
if (b.expiry_date > ls1.expiry_datetime)
b.expiry_date
else
ls1.expiry_datetime
end as licence_expiry
,s1.days_remaining as licence_days_remaining.....

Anyone know where I'm going wrong.

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-13 : 10:57:40

it is incorrect. you cant use if within select statement. it should be case itself

like


..
ls1.start_datetime as licence_start,
case  
when b.user_id is null 
then ls1.expiry_datetime 
when b.expiry_date > ls1.expiry_datetime
then b.expiry_date
else
ls1.expiry_datetime 
end as licence_expiry
,s1.days_remaining as licence_days_remaining.....

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

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-13 : 10:58:13

You mean BESIDES the logic?



CASE 
	WHEN b.user_id  IS null	                 THEN ls1.expiry_datetime 
	WHEN b.expiry_date > ls1.expiry_datetime THEN b.expiry_date 												               
                                                 ELSE ls1.expiry_datetime 
 END AS licence_expiry

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

theroversreturn
Starting Member

12 Posts

Posted - 2012-02-13 : 11:05:42

Thank you both. Works fine now.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-13 : 11:19:37

welcome

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

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-13 : 11:40:09

I guess you could actual do this



CASE 
	WHEN b.expiry_date > ls1.expiry_datetime THEN b.expiry_date 												               
                                                 ELSE ls1.expiry_datetime 
 END AS licence_expiry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources