asked on
IIF Statement - SSMS keeps replacing expression with quotes
What I think it should be:
(as part of a SELECT statement, this is one of the columns):
IIF(1=1, 'MX', 'NA') AS shippercountrymapcode
Note: 1=1 will be changed to correct test after issue is resolved
What SSMS keeps suggesting is should be:
Select IIF('1=1', 'MX', 'NA') AS shippercountrymapcode
It adds the quotes on 1=1 and produces an error
Execution Error:
An expression of non-boolean type specified in a context where a condition is expected, near'('.
(as part of a SELECT statement, this is one of the columns):
IIF(1=1, 'MX', 'NA') AS shippercountrymapcode
Note: 1=1 will be changed to correct test after issue is resolved
What SSMS keeps suggesting is should be:
Select IIF('1=1', 'MX', 'NA') AS shippercountrymapcode
It adds the quotes on 1=1 and produces an error
Execution Error:
An expression of non-boolean type specified in a context where a condition is expected, near'('.
Microsoft SQL Server
Last Comment
newknew
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Not sure I understand the purpose for the iif
Try
Select Iif ('1'='1','MX','NA') as test
Try == instead if the above error.
1=1 have not check and might be confusing among the various DB ............ Might be seen/interpreted as an assignment which is invalid, trying to assign a constant with a value.
1==1 will be seen as a comparison versus assignment.
Try
Select Iif ('1'='1','MX','NA') as test
Try == instead if the above error.
1=1 have not check and might be confusing among the various DB ............ Might be seen/interpreted as an assignment which is invalid, trying to assign a constant with a value.
1==1 will be seen as a comparison versus assignment.
@ryan chong 1=1 is always true
Then why use it in an IIF
IIF is only for true-false condition
Then why use it in an IIF
IIF is only for true-false condition
ASKER
The actual expression that is not working is:
IIF([shippercountry]='mexi
I replaced this with 1=1 to simplify for troubleshooting. It produces the same error: An expression of non-boolean type specified in a context where a condition is expected, near'('.
IIF([shippercountry]='mexi
I replaced this with 1=1 to simplify for troubleshooting. It produces the same error: An expression of non-boolean type specified in a context where a condition is expected, near'('.
Why do you have {}
You need the complete query syntax
IIF(shippercountry='mexico
You need the complete query syntax
IIF(shippercountry='mexico
Should be
IIF(shippercountry='mexico
IIF(shippercountry='mexico
Then why use it in an IIFwell, that part the user will change accordingly. that's just to show the expression is actually working fine.
The actual expression that is not working is:
IIF([shippercountry]='mexico', 'MX', 'NA') AS shippercountrymapcode
what's the data type of field: [shippercountry] ? varchar, nvarchar, etc?
simply put the field: [shippercountry] into the Where clause for troubleshooting.
Where [shippercountry]='mexico'
the issue could reside in your data.
ASKER
Partha, the answers to all three of your questions/replies were in the original post, please read fully before replying. 1) I stated that 1=1 was for testing purposes. 2) Yes, I know that 1=1 will always equal true, that's why it's in there for testing as I already stated. 3) I also stated (the title actually) that SSMS kept putting the single quotes around the test expression - so I already know they don't belong there.
https://www.experts-exchange.com/questions/29169342/IIF-Statement-SSMS-keeps-replacing-expression-with-quotes.html?anchorAnswerId=43009722#a43009722
https://www.experts-exchange.com/questions/29169342/IIF-Statement-SSMS-keeps-replacing-expression-with-quotes.html?anchorAnswerId=43009736#a43009736
https://www.experts-exchange.com/questions/29169342/IIF-Statement-SSMS-keeps-replacing-expression-with-quotes.html?anchorAnswerId=43009777#a43009777
https://www.experts-exchange.com/questions/29169342/IIF-Statement-SSMS-keeps-replacing-expression-with-quotes.html?anchorAnswerId=43009722#a43009722
https://www.experts-exchange.com/questions/29169342/IIF-Statement-SSMS-keeps-replacing-expression-with-quotes.html?anchorAnswerId=43009736#a43009736
https://www.experts-exchange.com/questions/29169342/IIF-Statement-SSMS-keeps-replacing-expression-with-quotes.html?anchorAnswerId=43009777#a43009777
ASKER
Per Ryan's suggestion, I changed to a CASE statement and it worked:
CASE WHEN [states].[country] = 'mexico' THEN 'MX' ELSE 'NA' END AS shippercountrymapcode
However, ultimately I added the shippercountrymapcode value to the states table itself for obvious table level value.
CASE WHEN [states].[country] = 'mexico' THEN 'MX' ELSE 'NA' END AS shippercountrymapcode
However, ultimately I added the shippercountrymapcode value to the states table itself for obvious table level value.
1=1 is always true so you don't need IIF
the condition should be something like <variable>=<value>