Does SQL Server 2000 support iif(logical expression,"","") function.

i tried this query just to know whether it supports this function.
select iif(5>4,"Yes","No")

and got the error Incorrect Syntax near ">"

what's the solution. this isn't my main query. main query is quite big. but just to know how to make access query using iif() compatible with sql server query i tried this small query.
pls help!!
thanks.

use CASE instead
CASE WHEN 5>4 THEN 'Yes' ELSE 'No' END

Forgot the SELECT at the start
SELECT CASE WHEN 5>4 THEN 'Yes' ELSE 'No' END

Thanks. But then the problem is that i am refering to some field in the table with relational operator instead of simple 5>4. when the field is used then it needs to be in group by or coupled with aggregate function. if that's done i do not get the same result as before as it changes the whole logic of the query. i already have a group by clause in the main query. after adding one more column in it, it would give wrong result.what's the possible solution for this?

You'll have to give more details of your query then, because the bottom line is that SQL Server does not support IIF so if you want to do something like IIF you'll have to say EXACTLY what you want.

My original ms-access query goes like this

select sum(iif(trn.amountspend = 0, trn.budgetamount, trn.amountspend)) as budgetamount,
trneventheader.regionid , mstrg.region,count(trn.eventid) as NoOfEvents,sum(trn.EndLeadGenerated) as EndLead,
(select amountallocated from mstlaapspend where mstlaapspend.regionid=mstrg.regionid) as Amountlocated
from mstregion mstrg, mstlaapspend mstlp inner join trneventheader on mstlp.regionid=trneventheader.regionid,
trnactivity trn where trn.eventid=trneventheader.eventid and mstlp.regionid=mstrg.regionid and
mstrg.regionid=trneventheader.regionid and trn.dateofactivity > '04/01/2004' and trn.approvedstatus=1
group by trneventheader.regionid, mstrg.regionid, mstrg.region
order by mstrg.region
----

In this what used to happen is that if trn.amountspend=0 then sum of budgetamount used to get calculated else sum of amountspend used to get calculated. if we want to do the same in sql server, as iif isn't supported sql server does not consider trn.amountspend as an attribute just for the sake of boolean expression but considers it as a field which should be shown in recordset. hence it puts the message as aggregate function or group by clause should be used for that field.
hence the problem. i hope u got what i wanted to say.
thanks.

Isn't that just the same as:
select sum(CASE WHEN trn.amountspend = 0 THEN trn.budgetamount ELSE trn.amountspend END) as budgetamount, ...

GREAT!!! i got exactly the same results as for original ms-access. thanks a lot.

my query is in sql server 2005..
this is as follows:-

stat = "Select sum(CASE WHEN AmtCrDr = 0 THEN Amount ELSE -Amount END from EtAmtTransMast where Ledgerid = c.partyid)as [Bal] From PersonalDetails c where type=" & PersonType.Customers

and the error is:-

Incorrect syntax near the keyword 'from'.

i dont know what is the prob with this... plz plz help me......

thanks

You have two FROM clauses and two WHERE clauses. If you want to reference two tables, you must do some sort of JOIN between the two tables (INNER, OUTER, whatever) with an ON clause and any WHERE clauses would follow that.

__________________
--Jeff Moden