Help with SQL iif - 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

General SQL Server Forums

New to SQL Server Programming

Help with SQL iif

Author

Topic

mdixon44
Starting Member

26 Posts

Posted - 2008-05-15 : 02:25:42

Can anyone assist me with this one.. I would appreciate it.

Using the L_Employees table, write a query which outputs first_name, last_name, and 2 extra columns called "Exempt" and "Non-Exempt". Use the IIF function to populate these new columns with dept_code values as such: "Exempt" column should contain dept_codes "Exe" and "Sal". "Non-exempt" column should contain all other dept_codes which are "Act","Shp","Mkt"

I came up with this:

select first_name,
last_name,
iif(dept_code >'Exe', Sal') as Exempt,
iif(dept_code >'Act','Shp','Mkt') as Non_Exempt
from l_employees

What am I doing wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-15 : 02:35:55

Can we see what you've tried till now?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-15 : 02:52:25

t-sql dont have IIF. You need to use CASE instead.

select first_name,
last_name,
case when dept_code in('Exe', Sal') then 1 else 0 end as Exempt,
case when dept_code in('Act','Shp','Mkt') then 1 else 0 end as Non_Exempt
from l_employees

IIF is available only in VB , SQL Reporting services & MDX expressions

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2008-05-15 : 03:01:53

quote:
Originally posted by mdixon44

Can anyone assist me with this one.. I would appreciate it.

Using the L_Employees table, write a query which outputs first_name, last_name, and 2 extra columns called "Exempt" and "Non-Exempt". Use the IIF function to populate these new columns with dept_code values as such: "Exempt" column should contain dept_codes "Exe" and "Sal". "Non-exempt" column should contain all other dept_codes which are "Act","Shp","Mkt"

I came up with this:

select first_name,
last_name,
iif(dept_code >'Exe', Sal') as Exempt,
iif(dept_code >'Act','Shp','Mkt') as Non_Exempt
from l_employees

What am I doing wrong?

IIF is an Access function it will fail in SQL Server, you need to use an
in-line case statement instead.

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2008-05-15 : 03:07:06

[code]
iif(dept_code in ('Exe', 'Sal'),'Exempt','') as Exempt
[/code]

@visakh16

the iif function is available in Jet SQL (Access).

mdixon44
Starting Member

26 Posts

Posted - 2008-05-15 : 03:27:02

quote:
Originally posted by mdixon44

Can anyone assist me with this one.. I would appreciate it.

Using the L_Employees table, write a query which outputs first_name, last_name, and 2 extra columns called "Exempt" and "Non-Exempt". Use the IIF function to populate these new columns with dept_code values as such: "Exempt" column should contain dept_codes "Exe" and "Sal". "Non-exempt" column should contain all other dept_codes which are "Act","Shp","Mkt"

I came up with this:

select first_name,
last_name,
iif(dept_code >'Exe', Sal') as Exempt,
iif(dept_code >'Act','Shp','Mkt') as Non_Exempt
from l_employees

What am I doing wrong?

This is Access SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-15 : 03:43:52

quote:
Originally posted by mdixon44

quote:
Originally posted by mdixon44

Can anyone assist me with this one.. I would appreciate it.

Using the L_Employees table, write a query which outputs first_name, last_name, and 2 extra columns called "Exempt" and "Non-Exempt". Use the IIF function to populate these new columns with dept_code values as such: "Exempt" column should contain dept_codes "Exe" and "Sal". "Non-exempt" column should contain all other dept_codes which are "Act","Shp","Mkt"

I came up with this:

select first_name,
last_name,
iif(dept_code >'Exe', Sal') as Exempt,
iif(dept_code >'Act','Shp','Mkt') as Non_Exempt
from l_employees

What am I doing wrong?

This is Access SQL

In that case you've posted in wrong forum. This is MS SQL Server forum.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-15 : 03:46:38

quote:
Originally posted by LarsG

iif(dept_code in ('Exe', 'Sal'),'Exempt','') as Exempt

@visakh16

the iif function is available in Jet SQL (Access).

but this is not acces forum. This is MS SQL Server forum and we assume your are posting only SQL Server related questions alone. If you're using Access then suggest to post the question in MS Access forum.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources