zimmer9
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.am ount*(-1)/ 100),CDbl( amount)/10 0),
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----
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.processDTMn th + '/' + tbl_SMR_OI_DRS.processDtDa y + '/' + tbl_SMR_OI_DRS.processDTYe ar,
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.am ount*(-1)/ 100),CDbl( amount)/10 0),
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];
Why does the following SQL statement give me an error as follows:
Incorrect syntax near '='.
The offending statement is:
IIf(sign_='-',CDbl(tbl_SMR
--------------------------
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.tranType,
tbl_SMR_OI_DRS.processDTMn
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
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