SQL embedded "IF" statement

[Guest]

Is there a way to embed an if statement into a SQL select? I want to make a
select output field = to one thing if a field is >1 and different if a field
is < 1. I am using Excel to perform all queries. If possible it might look
like this but I don't see if the IF is supported. If not, is there a
workaround?

SELECT F1, F2, IF(F1>1,F1*3,F1*2) FROM ATABLE

Thanks,
Dave

 

[Fredrik Wahlgren]

Is there a way to embed an if statement into a SQL select? I want to make a
select output field = to one thing if a field is >1 and different if a field
is < 1. I am using Excel to perform all queries. If possible it might look
like this but I don't see if the IF is supported. If not, is there a
workaround?

SELECT F1, F2, IF(F1>1,F1*3,F1*2) FROM ATABLE

Thanks,
Dave

It's called a CASE expression in SQl Server:
http://www.craigsmullins.com/ssu_0899.htm

Your query would end up like this:

SELECT F1, F2,
F3 = CASE F1
WHEN F1 > 1 THEN F1 * 3 ELSE F1 * 2
END,
FROM ATABLE

/Fredrik

/Fredrik

 

[Guest]

I tried this but can't get it to work. I am not sure if I have my syntax
correct or if this is not supported in Excel. I will keep trying. Do you
know if this is supported or not in Excel? Maybe only in SQL Server.

Thanks for the help.
Dave

 

[Fredrik Wahlgren]

Umm, this has nothing to do with Excel. The sample code I made was derived
from the sample in the link I gave. I think you should try out the code in
the Query Analyzer before trying it out in Excel. For more links, see Books
Online or google sql server case select
I'm not sure whether the comma is correct

/Fredrik

 

[onedaywhen]

Is there a way to embed an if statement into a SQL select?

SELECT F1, F2, IF(F1>1,F1*3,F1*2) FROM ATABLE

Your use of F1 suggests you are querying Excel using MS Jet. Jet does
not support the standard SQL CASE WHEN syntax, so try IIf (immediate
if) e.g.

SELECT F1, F2, IIF(F1>1,F1*3,F1*2) FROM ATABLE;

For more complex case statements, you could use SWITCH e.g.

SELECT F1, F2, SWITCH(
F1>1 AND F1<=2, F1*3,
F1>2 AND F1<=3, F1*4,
1=1, F1*2
) FROM ATABLE;

Jamie.

--