Equivalent SQL statement - 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)

Equivalent SQL statement

Author

Topic

pj_rajesh
Starting Member

3 Posts

Posted - 2003-12-31 : 05:05:44

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. Is there any better approach? 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;

Is there any equivalent statement in SQL Server??

Please advice..

Rajesh Joy

Lewie
Starting Member

42 Posts

Posted - 2003-12-31 : 05:15:10

You can use a CASE in place of your IFF.
e.g.
SELECT Case when <true> then <Result 1>
when <true> then <result 2>
ELSE
<result 3>
END,
Field2,
Field3
from <Table> join ...........

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2003-12-31 : 05:16:45

Hi,
There is no IFF in sql but the other way is to try with case
replace the

IIf(tblAccount.OpInt='D',(tblAccount.OpBal+VAccountsDb.DbtTot)-VAccountsCr.CrtTot,(VAccountsDb.DbtTot-(tblAccount.OpBal+VAccountsCr.CrtTot))) AS Balance

with the following
-----------------------------------------
Case WHEN tblAccount.OpInt= 'D' THEN(tblAccount.OpBal+VAccountsDb.DbtTot)-VAccountsCr.CrtTot
ELSE (VAccountsDb.DbtTot-(tblAccount.OpBal+VAccountsCr.CrtTot))) AS Balance
end
-----------------------------------------

this should work for u

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))) AS Balance
END
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

regards,
Ganesh V.
NetAssetManagement.Com

Enjoy working

pj_rajesh
Starting Member

3 Posts

Posted - 2003-12-31 : 09:19:26

Thank you very much indeed friends. Wish U all a very Happy New Year

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources