if in select

[sagn]

Excuse me if this has been asked and answered previously
but the keyword
search engine has been down for sometime now.
(At least when I have logged on)

I was wondering if you can use an IF stmt instead of
a case in a select statment.

for example

select
table1.a,

if (table1.b = 'y') then 'y'
else 'n'

from table1

but I get several syntax errors in doing this.
I've tried with begin and end stmts and also
getting rid of the 'then'
but to no avail


Thanks

ds

 

[tlbroadbent]

CASE is the proper ANSI-92 SQL command to do what you want. Is there a reason you don't want to use the CASE statement?

Some RDMS are not ANSI SQL compliant and do not support use of the CASE statment. One of these is Microsoft Access. In Access you would use the IIF function.

Here are two queries - the first uses the CASE statement while the other uses IIF. The 1st would work in SQL Server while the 2nd works in Access.

Select
ColA,
Case ColB
When 'Y'
Then 'Y'
Else 'N'
End As ColBNew
From tbl

Select
ColA,
IIF(ColB='Y','Y', 'N') As ColBNew
From tbl Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[Tim1]

Hiya,

If there is a specific reason that you don't want to use case, the other way to do it is to use local variables. eg:

DECLARE @variable CHAR(1)

SELECT @variable = a
FROM table1

IF @variable = 'y'
then do something
ELSE
do something else

HTH

Tim

 

[sagn]

Thanks to you for your replies.
No, there is no reason why I wanted to use
IF instead of CASE, other than I thought it would be
cool learn a new way to do it.

Would you happen to know if it is possible with
Microsoft SQL server? I thought I remembered
someone asking another why they were not using IF
when there CASE statement was so simple...

Thanks

ds

 

[tlbroadbent]

SQL Server is most;y ANSI compliant. It doesn't have IIF. You can use If... Then... Else for control of flow in SQL Scripts but not in select statements as you can the CASE statement.

May I suggest that if you are using SQL Server, the SQL Server forum will be much better for your questions than the ANSI SQL forum. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[sagn]

Yes, you are absolutely right. I should have posted
in the SQL server forum. In fact, I thought I was
posting there but then realized I was in the ANSI forum
(after the fact)

Sorry about that.

ds