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'('.

Partha Mandayam

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>

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

arnold

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 Mandayam

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

newknew

ASKER

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'('.

arnold

Why do you have {}

You need the complete query syntax

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

arnold

Ref https://docs.microsoft.com/en-us/sql/t-sql/functions/logical-functions-iif-transact-sql?view=sql-server-ver15

Partha Mandayam

Should be

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

Ryan Chong

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.

newknew

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

newknew

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.