IIF syntax - 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 2000 Forums

Transact-SQL (2000)

IIF syntax

Author

Topic

mawan
Starting Member

4 Posts

Posted - 2007-07-03 : 00:21:54

I have a table named "students". It contains data like this:


code | name 
-----+------
AF1  | Alice
AM2  | Bob
AF3  | Cindy
AM4  | Dave
AF5  | Erica

I want to access that table using PHP. The syntax select is like this:

select * from view_students

The output must be like this:


code | name  | Sex
-----+-------+-------
AF1  | Alice | Female
AM2  | Bob   | Male
AF3  | Cindy | Female
AM4  | Dave  | Male
AF5  | Erica | Female

Sex is taken from 2nd character of code. If F, it means Female. If M, it means Male. How to create View for this?

I have try to using this, but not work.

select *, (iif(substring(code, 2, 1)) = 'F', 'Female', 'Male') as sex from students

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-03 : 00:33:22

There is no IIF() in T-SQL, you should use CASE statement:

Select
 *,
 Case substring(code, 2, 1)
   When 'F' Then 'Female'
   When 'M' Then 'Male'
 End as Sex
From Students

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mawan
Starting Member

4 Posts

Posted - 2007-07-03 : 00:38:29

Thanks. I have try that code, but an error accured:

SQL Syntax Errors Encountered.
The following errors were encountered while parsing the contents of the SQL pane:
The Query Designer does not support the CASE SQL construct.

Excuse me. I'm very begginer in MS SQL Server 2000.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-03 : 00:40:36

It would be helpful if you tell us which database you are using? SQL Server or MS Access? or something else?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-03 : 00:51:37

1. Which tool you use to execute your queries?
2. What is the compatibility mode of your database? You can check it using following command:

EXEC sp_dbcmptlevel 'database-name'

note: Replace 'database-name' with desired database name

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mawan
Starting Member

4 Posts

Posted - 2007-07-03 : 00:52:29

The database and table is stored in MS SQL Server 2000. I use SQL Server Enterprise Manager. Now I work in "Views" (not in Diagrams, Tables, Stored Procedures).

EXEC sp_dbcmptlevel 'smea'
The current compatibility level is 80.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-03 : 01:11:25

Even though Enterprise manager flags it as error, it is a valid T-SQL statement. You can go ahead and save the view. It looks like a bug in EM.

Also, I will suggest to use Query Analyzer for writing SQL statements. It's much lightweight and efficient that EM.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources