Tuesday, March 20, 2012

CASE in T-SQL

I'm upsizing Access to SQL Server 2005.
I need to convert the following query:
SELECT
tblHistory.AutoNumber,
tblHistory.InputDate,
IIf([tblHistory].[TaxType]="111" Or [tblHistory].[taxtype]="222","ABC",IIf([tblHistory].[taxtype]="AAA","AAA","BBB")) AS 1TaxType FROM tblHistory;

CASE in T SQL:
SELECT tblHistory.AutoNumber,
tblHistory.InputDate,
'1TaxType' =
CASE
WHEN [tblHistory].[TaxType] = '111' THEN 'ABC'
WHEN [tblHistory].[TaxType] = '222' THEN 'ABC'
ELSE
CASE
WHEN [tblHistory].[taxtype] = 'AAA' THEN 'AAA'
ELSE 'BBB'
END
END
FROM tblHistory;

Is this correct?

It is fine. But you can simplify it further like:

SELECT h.AutoNumber,
h.InputDate,
CASE
WHEN h.[TaxType] IN ('111', '222') THEN 'ABC'
WHEN h.[taxtype] = 'AAA' THEN 'AAA'
ELSE 'BBB'
END as [1TaxType]
FROM tblHistory as h;

I also modified the SELECT statement syntax to use table aliases which makes it easier to read and change the proprietary column alias syntax also.

No comments:

Post a Comment