Link to home
Start Free TrialLog in
Avatar of zimmer9
zimmer9Flag for United States of America

asked on

How to correct an IIF statement in SQL Server 2005?

I am developing a C# Web site application. I use VS2010 and SQL Server 2005.

Why does the following SQL statement give me an error as follows:
Incorrect syntax near '='.

The offending statement is:

IIf(sign_='-',CDbl(tbl_SMR_OI_DRS.amount*(-1)/100),CDbl(amount)/100),

----------------------------------------------------------------------------------------------------
The entire SQL statement is as follows:
SELECT tbl_SMR_OI_DRS.bankNum, tbl_SMR_OI_DRS.officeNum, checknum, tbl_SMR_OI_DRS.refnum,
tbl_SMR_OI_DRS.transDTMnth + '/' + tbl_SMR_OI_DRS.transDTday + '/' + tbl_SMR_OI_DRS.transDtYR,
tbl_SMR_OI_DRS.tranType,
tbl_SMR_OI_DRS.processDTMnth + '/' + tbl_SMR_OI_DRS.processDtDay + '/' + tbl_SMR_OI_DRS.processDTYear,
tbl_SMR_OI_DRS.pending, tbl_SMR_OI_DRS.age*1, tbl_SMR_OI_DRS.cr_db, tbl_SMR_OI_DRS.descr,
IIf(sign_='-',CDbl(tbl_SMR_OI_DRS.amount*(-1)/100),CDbl(amount)/100),
tbl_SMR_OI_DRS.userID,
tbl_SMR_OI_DRS.auditDate, ' ', ' ', tbl_SMR_Banks.rptID, tbl_SMR_Banks.[REPORT NAME]
FROM tbl_SMR_OI_DRS INNER JOIN tbl_SMR_Banks ON tbl_SMR_OI_DRS.bankNum=tbl_SMR_Banks.[Bank Code];
Avatar of plusone3055
plusone3055
Flag of United States of America image

probably a result in null values
and ther is no try/catch statement to display the error

here is a definition of IIF statememnt
http://technet.microsoft.com/en-us/library/hh213574.aspx
ASKER CERTIFIED SOLUTION
Avatar of DcpKing
DcpKing
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of zimmer9

ASKER

CASE when sign_ = '-' then cast(tbl_SMR_OI_DRS.amount as float) * -0.01 else cast(tbl_SMR_OI_DRS.amount as float) / 100.0 END,
That's it!