Convert Values Using Decode Function

We use decode() function to convert a value to other values. We have the following table.

SQL> select * from tbl_test order by id;

     ID MESSAGE
------- --------------------------------
      1 A
      2 B
      3 C
      4 D

The following query transforms characters values into numbers or weights.

SQL> select a.*, decode(message, 'A', 0.3, 'B',0.4, 0.35) weight from tbl_test a order by id;

     ID MESSAGE                              WEIGHT
------- -------------------------------- ----------
      1 A                                        .3
      2 B                                        .4
      3 C                                       .35
      4 D                                       .35

In the above query, A is mapped to 0.3, B to 0.4. All other values are mapped to the default value of 0.35. If the default value is not defined, other characters where the mapping is not defined will be given a NULL value.

SQL> select a.*, decode(message, 'A', 0.3, 'B',0.4) weight from tbl_test a order by id;

     ID MESSAGE                              WEIGHT
------- -------------------------------- ----------
      1 A                                        .3
      2 B                                        .4
      3 C
      4 D

I have used decode function to convert categorical values into weights that are derived from a logistic regression model. For example, I used the following query to convert transaction code into weights. Before decode function is applied, CARD_TXN_CDE is cleaned up using trim, substr and nvl functions. The trim function removes leading and trailing blanks. The substr function extracts the first 18 characters of the string. The nvl function converts NULL values to blank.

decode(nvl(substr( trim(CARD_TXN_CDE),1,18),' '),
' ',-.660841,
'01',-.518927,
'1',-.076546,
'10',-.294631,
'12',.077699,
'14',-.709884,
'25',-.30619,
'40',.021855,
'51',-.004593,
'52',-.069521,
'53',-.000344,
'57',.570421,
'59',.858444,
'67',1.481654,
'81',.29988,
'91',-.004755,
'96',.02628,
'AD',-.001036,
'AK',-.150162,
'CB',-.001588,
'D5',.364975,
'G',-.015795,
'H',-.274374,
'I',-1.065177,
'J',-.027991,
'N',.157622,
'NK',.763406,
'NR',.080558,
'P',-.961133,
'PE',-.19558,
'PH',.479081,
'PR',.134741,
'S',-.239287,
'SV',.475934,
'T1',.241061,
'T2',-.277572,
'T3',.901487,
'T4',-.0137,
'TS',.01362,
'U2',-.186914,
'X',1.301152,
'XX',-.11462,
'Z0',-.7141,
'Z4',-.004642,
'Z6',-.014541,
0)