OracleBlog

I was helping a colleague recently with some SQL. He had about a dozen SQL queries that all looked something like this:

SELECT
(some stuff),
DECODE (status, 'A', 1, 'I', 0),
(more stuff)
FROM
(wherever)

I made a few observations about his DECODE statement:
1. Sometimes he used DECODE and sometimes he used CASE
2. Sometimes he would map ' ' to 0, and sometimes it would go to the default value of NULL, like in this example.

Decode vs Case

First, he asked me whether he should use DECODE or CASE. I remember getting into discussions about that with Doug Burns back in the days when I was more active in the community. Without getting into the whole debate again, let me just say that between the two, CASE is ANSI-standard, newer, easier to maintain, can do more things, and is generally the way to go.

That being said, there's no real difference between DECODE and CASE in this example. I'd just pick one and be consistent.

Use Mapping Tables

My primary suggestion was to consider eliminating the DECODE and CASE altogether. Since several SQL queries were doing the same thing, and accidentally doing so inconsistently, I recommended replacing it with a mapping table. Here's how:

1. Create a table with two columns, one of the same type as "status", the other as a number.

CREATE TABLE mapper (status VARCHAR2(1), val NUMBER(1));

2. Populate the table with all possible "status", and to which number you want it mapped.

INSERT INTO mapper (status, val) VALUES ('A', 1);
INSERT INTO mapper (status, val) VALUES ('I', 0);
INSERT INTO mapper (status, val) VALUES (' ', 0);
commit;

3. It's not always possible to modify the original table to make its status column a foreign key on this one (it might be tied to a vendor application, as it is in this case), but if it is possible, it's worth considering.
4. In the SQL queries, add a join condition on "status" to this new table you created

FROM table1 t1 JOIN mapper m USING (status)

5. Replace the DECODE/CASE statement with the "number" column of this new table.

SELECT m.value

The Pros and Cons

The advantages are that it's very simple, easy to understand, and you know it's consistent across all your queries. Should you ever want to change the mapping, you can easily update that table, and know that all your queries have been updated.

Also, you can more easily take advantage of indexes now (function-based indexes notwithstanding). That's not really applicable in this example, but it may be in other cases.

The disadvantage is having another join. But as Tom Kyte once said "joins are not evil. Databases were born to join." There are reportedly some cases (in Oracle 10, for example) where sometimes adding another table causes the optimizer to make really bad choices, and it really slows down your query, almost as if you've hit a threshold. Chances are that you can address this concern either with patches or by talking with your DBA about some of the optimizer settings. In general, you can fear the reaper, and don't fear joins.

Wrap Up

If you've got several queries that are all using DECODE and/or CASE to map one value to another, consider creating a mapping table and joining that into your queries instead. It will make them easier to maintain, and depending on your indexes it might even speed up some queries.