CODEWITHSUNDEEP
sql-servert-sqlsql-server-2005
Dr. Andrew
Dr. Andrew

Reputation: 2621

SQL Server varchar equality test gives syntax error

I have a simple table running in SQL Server 2005 defined as

CREATE TABLE [dbo].[ap_purchases_tax_det](
[invoice_s] [int] NOT NULL,
[line_num] [smallint] NOT NULL,
[tax_code] [varchar](10) NOT NULL,
[tax_type] [varchar](2) NULL,
[tax_det_s] [int] IDENTITY(1,1) NOT NULL,
[old_entity] [varchar](14) NULL,
[old_cc_code] [varchar](5) NULL,
[old_vendor] [varchar](15) NULL,
[old_invoice] [varchar](20) NULL,
[citi] [varchar](1) NULL,
CONSTRAINT [aaaaaap_purchases_tax_det_PK] PRIMARY KEY NONCLUSTERED 
(
    [invoice_s] ASC,
    [line_num] ASC,
    [tax_code] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

The tax_type field is filled with either 'W', 'V', 'B", or '' (this one is not NULL).

I need to get a boolean flag if tax_type is 'W', so I have the SQL:

select iif(tax_type = 'W',1,0) FROM dbo.ap_purchases_tax_det;

when I run this I get "Incorrect syntax near '='.". This occurs even for a simple tax_type = 'W'. The test for equality seems to work in the WHERE clause, but I need a flag, which means I would have to UNION together 2 selects. This is actually part of a join that is a subquery in much larger statement so I don't want to do this. I have tried excplicitly casting tax_type = cast('W' as varchar(2)) to no avail. Same results if I use ISNULL(tax_type,'x').

I have no idea why the test for equality is not working, can someone provide guidance?

Upvotes: 2

Views: 676

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

Generally, write the IIF like this prior to SQL Server 2012

select case when tax_type = 'W' then 1 else 0 end
FROM dbo.ap_purchases_tax_det;

The CASE statement has another form, but that's like a SWITCH statement in C#/Java or SELECT CASE in MS Access, which is not really a general conditional.

Two important notes about IIF (Transact-SQL)

IIF is a shorthand way for writing a CASE statement.
The fact that IIF is translated into CASE...

So when you write

IIF ( {condition} , {true} , {false} )

The SQL Server query optimizer literally sees

CASE WHEN {condition} THEN {true} ELSE {false} END

FYI You can avoid the CASE statement completely by using this

SELECT 1-sign(abs(ascii('W')-ascii(tax_type+'.')))

Just a thought.

Upvotes: 1

podiluska
podiluska

Reputation: 51494

You can't use IIF in SQL 2005 because IIF is a SQL 2012 function only

You need to use CASE

 SELECT CASE tax_type WHEN 'W' THEN 1 ELSE 0 END FROM dbo.ap_purchases_tax_det

Upvotes: 3

Related Questions