Translating code numbers to item descriptions in PostgreSQL query

Question

For each itemId I need to select a value in my PostgreSQL query. I am doing it like this:

select itemId,
 CASE
-- Revenue
 WHEN itemId in (339,28,29)
 THEN 'Revenues'
 -- Rev CHG calculated on the back-end
--Expenditures
 WHEN itemId = 102
 THEN 'SG'||CHR(38)||'A'
 -- SGA CHG calculated on the back-end
 WHEN itemId = 4375
 THEN 'SG'||CHR(38)||'A MARGIN'
 WHEN itemId = 100
 THEN 'R'||CHR(38)||'D EXPENSE'
 -- RD CHG calculated on the back-end
 WHEN itemId in (2021, 2114)
 THEN 'CAPEX'
 --CAPEX CHG calculated on the back-end
 WHEN itemId = 34
 THEN 'COGS'
--Income
 WHEN itemId = 379
 THEN 'Net Income'
 WHEN itemId = 7
 THEN 'Earnings from Cont. Ops'
 WHEN itemId = 83
 THEN 'Minority Interest'
 WHEN itemId = 280
 THEN 'Preferred Stock Dividend'
 -- NI CHG calculated on the back-end
 WHEN itemId = 400
 THEN 'EBIT'
 WHEN itemId = 4197
 THEN 'EBIT CHG 1 YR'
 WHEN itemId = 4210
 THEN 'EBIT CHG 2 YR'
 WHEN itemId = 4223
 THEN 'EBIT CHG 3 YR'
 WHEN itemId = 4236
 THEN 'EBIT CHG 5 YR'
 WHEN itemId = 4249
 THEN 'EBIT CHG 7 YR'
 WHEN itemId = 4262
 THEN 'EBIT CHG 10 YR'
 WHEN itemId = 4051
 THEN 'EBITDA'
 WHEN itemId = 4196
 THEN 'EBITDA CHG 1 YR'
 WHEN itemId = 4209
 THEN 'EBITDA CHG 2 YR'
 WHEN itemId = 4222
 THEN 'EBITDA CHG 3 YR'
 WHEN itemId = 4235
 THEN 'EBITDA CHG 5 YR'
 WHEN itemId = 4248
 THEN 'EBITDA CHG 7 YR'
 WHEN itemId = 4261
 THEN 'EBITDA CHG 10 YR'
 WHEN itemId = 4047
 THEN 'EBITDA MARGIN'
 WHEN itemId = 3064
 THEN 'EPS-BASIC'
 --EPS-BASIC CHG calculated on the back-end
 WHEN itemId = 142
 THEN 'EPS-DIL'
 --EPS-DIL CHG calculated on the back-end
 WHEN itemId = 10
 THEN 'GROSS PROFIT'
 WHEN itemId = 4195
 THEN 'GP CHG 1 YR'
 WHEN itemId = 4208
 THEN 'GP CHG 2 YR'
 WHEN itemId = 4221
 THEN 'GP CHG 3 YR'
 WHEN itemId = 4234
 THEN 'GP CHG 5 YR'
 WHEN itemId = 4247
 THEN 'GP CHG 7 YR'
 WHEN itemId = 4260
 THEN 'GP CHG 10 YR'
 WHEN itemId = 4074
 THEN 'GP MARGIN'
 --GP MARGIN CHG calculated on the back-end
 WHEN itemId in (21,5,356,22)
 THEN 'OP PROFIT'
 WHEN itemId = 373
 THEN 'Total Operating Exp'
 --OP PROFIT CHG calculated on the back-end
--Cash Flow
 WHEN itemId = 2207
 THEN 'Cash Flow'
 --CF CHG calculated on the back-end
 WHEN itemId = 4423
 THEN 'UNLEVERED FREE CASH FLOW'
 WHEN itemId = 4430
 THEN 'UCF CHG 1 YR'
 WHEN itemId = 4431
 THEN 'UCF CHG 2 YR'
 WHEN itemId = 4432
 THEN 'UCF CHG 3 YR'
 WHEN itemId = 4433
 THEN 'UCF CHG 5 YR'
 WHEN itemId = 4434
 THEN 'UCF CHG 7 YR'
 WHEN itemId = 4435
 THEN 'UCF CHG 10 YR'
 WHEN itemId = 2006
 THEN 'CASH OPS'
 WHEN itemId = 2081
 THEN 'Net Cash From Discont. Ops'
 --Cash Ops CHG calculated on the back-end
--Return
 WHEN itemId = 4178
 THEN 'ROA'
 WHEN itemId = 4363
 THEN 'ROC'
 WHEN itemId = 4128
 THEN 'ROE'
--Working Capital
 WHEN itemId = 4030
 THEN 'CURRENT RATIO'
 WHEN itemId = 4121
 THEN 'QUICK RATIO'
 WHEN itemId = 4177
 THEN 'ASSET TURNOVER'
 WHEN itemId = 4082
 THEN 'INVENTORY TURNOVER'
 END itemname in
 from item i
 join itemcode ic on i.itemcode = ic.itemcode
 WHERE ic.dataItemId IN (.................);

Can I do it in a better way?

Answer 1

You should put all the mapping between itemId values and whatever constant strings in a separate table (2 columns) and do a join with it. This allows you to update the mapping (by changing the table content) without even changing the query later on. (and this is not specific to PostgreSQL, will work with any RDBMS)