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)