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

SQL Server 2000 Forums

Transact-SQL (2000)

IIF.....

Author

Topic

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2002-05-17 : 08:53:16

I have to mimic the line below (from an Access query) in a SQL Server query where this line is the Score column. I notice in BOL that there is an IIF statement in SQL but it appears to work differently than the Access IIF. Am i better off using some sort of CASE statement or can the IIF in SQL do the job ? If using a CASE statement how do i present it as one of the columns in the overall query ?

IIf([fi_score]=3,"1st",IIf([fi_score]=2,"2nd",IIf([fi_score]=1,"3rd","Error")))

All the above line is trying to say is if the score is 3 then display 1st, 2 display 2nd and 1 display 3rd.

The query this column goes in is something like this.....

SELECT
Broker_Employee.fa_brokerEmployeeCode,
Broker_Employee.fs_forename + ' ' + Broker_Employee.fs_surname AS Name, Brokers_in_UID.fs_BrokerNameinUID,
FMG_Dealing_Bemco_Ranking.fi_score, -- THIS LINE NEEDS TO BE SOMETHING LIKE THE ABOVE IIF STATEMENT
Broker_Employees_in_UID.fs_UID,
FMG_Dealing_Bemco_Ranking.fi_fmgQaire
FROM Broker_Employees_in_UID INNER JOIN
Brokers_in_UID ON
Broker_Employees_in_UID.fs_UID = Brokers_in_UID.fs_UID AND
Broker_Employees_in_UID.fl_brokerCode = Brokers_in_UID.fl_brokerCode
INNER JOIN
Broker_Employee ON
Broker_Employees_in_UID.f_brokerEmployeeCode = Broker_Employee.fa_brokerEmployeeCode
INNER JOIN
FMG_Dealing_Bemco_Ranking ON
Broker_Employee.fa_brokerEmployeeCode = FMG_Dealing_Bemco_Ranking.fi_bemcoCode
ORDER BY FMG_Dealing_Bemco_Ranking.fi_score DESC

regards

Paul

Edited by - knookie on 05/17/2002 09:01:20

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-17 : 09:03:17

SELECT CASE fi_score
WHEN 1 THEN '3rd'
WHEN 2 THEN '2nd'
WHEN 3 THEN '1st'
ELSE 'Error' END

From what I found in Books Online, I think the IIF function only works with Analysis Services, not in T-SQL.

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-05-20 : 22:20:41

quote:
From what I found in Books Online, I think the IIF function only works with Analysis Services, not in T-SQL.

I don't think, I know. IIF won't run. It's way up on my wish list.

Sarah Berger MCSD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources