We help IT Professionals succeed at work.

IIF Statement - SSMS keeps replacing expression with quotes

newknew
newknew asked
on
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'('.
Comment
Watch Question

Partha MandayamTechnical Director

Commented:
in iif, first condition should always be boolean
1=1 is always true so you don't need IIF
the condition should be something like  <variable>=<value>
Software Team Lead
Commented:
is your SQL complex enough that you have missed out some checkings?

it's fine to have expression of 1 = 1  in IIF function.

I believe you may still encounter an error if you replace the IIF function with Case... When clause.

try start debug by doing a simple comparison:

Select 
IIF(1=1, 'MX', 'NA') AS shippercountrymapcode, 
case when 1=1 then 'MX' else 'NA' end AS shippercountrymapcode2
from yourObject

Open in new window

Distinguished Expert 2019

Commented:
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.
Partha MandayamTechnical Director

Commented:
@ryan chong 1=1 is always true
Then why use it in an IIF
IIF is only for true-false condition

Author

Commented:
The actual expression that is not working is:
IIF([shippercountry]='mexico', 'MX', 'NA') AS shippercountrymapcode

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'('.
Distinguished Expert 2019

Commented:
Why do you have {}

You need the complete query syntax

IIF(shippercountry='mexico', 'MX', 'NA') AS shippercountrymapcode
Distinguished Expert 2019

Commented:
Partha MandayamTechnical Director

Commented:
Should be

IIF(shippercountry='mexico', 'MX', 'NA') AS shippercountrymapcode
Ryan ChongSoftware Team Lead

Commented:
Then why use it in an IIF
well, 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'

Open in new window


the issue could reside in your data.

Author

Commented:
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#a43009722
https://www.experts-exchange.com/questions/29169342/IIF-Statement-SSMS-keeps-replacing-expression-with-quotes.html#a43009736
https://www.experts-exchange.com/questions/29169342/IIF-Statement-SSMS-keeps-replacing-expression-with-quotes.html#a43009777

Author

Commented:
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.