Is there any alternative for IIF function?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Is there any alternative for IIF function??

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-01-06 : 08:16:10

Rajesh writes "hi there..,

I need a help in coverting an Access query to SQL Server database. I'm thinking of converting all Access Queries to Views in SQL Server database. Here I'm facing a problem with IIF funtion used in Access query. Is there any way to use the same funtionality of IFF function in SQL Server? Given below is the Access query..

SELECT tblAccount.Code, tblAccount.EName, VAccountsDb.DbtTot, VAccountsCr.CrtTot,
IIf(tblAccount.OpInt='D',(tblAccount.OpBal+VAccountsDb.DbtTot)-VAccountsCr.CrtTot,(VAccountsDb.DbtTot-(tblAccount.OpBal+VAccountsCr.CrtTot))) AS Balance
FROM (tblAccount LEFT JOIN VAccountsDb ON tblAccount.Code = VAccountsDb.Code) LEFT JOIN VAccountsCr ON tblAccount.Code = VAccountsCr.Code
GROUP BY tblAccount.Code, tblAccount.EName, VAccountsDb.DbtTot, VAccountsCr.CrtTot
ORDER BY tblAccount.Code;

Please advice..

Rajesh Joy"

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-01-06 : 08:26:12

Here you go: Look-up CASE..WHEN..THEN on BOL it is very neat.

SELECT	tblAccount.Code
	, tblAccount.EName
	, VAccountsDb.DbtTot
	, VAccountsCr.CrtTot
	, CASE WHEN tblAccount.OpInt='D' THEN ((tblAccount.OpBal+VAccountsDb.DbtTot)-VAccountsCr.CrtTot)
	  ELSE (VAccountsDb.DbtTot-(tblAccount.OpBal+VAccountsCr.CrtTot))) END AS Balance
FROM	(tblAccount LEFT JOIN VAccountsDb ON tblAccount.Code = VAccountsDb.Code) LEFT JOIN VAccountsCr 
		ON tblAccount.Code = VAccountsCr.Code
GROUP BY tblAccount.Code, tblAccount.EName, VAccountsDb.DbtTot, VAccountsCr.CrtTot
ORDER BY tblAccount.Code;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources