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.
