DECODE/CASE vs. Mapping Tables

I was aiding a co-worker freshly with some SQL. He had about a dozen SQL queries that all looked a thing like this:

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

I made a small number sightings about his DECODE statement:
1. Sometimes he employed DECODE and at times he employed CASE
2. Sometimes he would depiction ' ' to 0, and at times it would depart to the default worth of NULL, like in this example.

Decode vs Case
First, he requested me if he should use DECODE or CASE. I consider getting into debates about that with Doug Burns back in the days when I was more energetic in the community. Without getting into the total discussion over, let me just declare that between the two, CASE is ANSI-standard, newer, less difficult to uphold, can perform more things, and is broadly chatting the way to go.

That being said, there's no actual divergence between DECODE and CASE in this example. I'd just select one and be consistent.

Use Mapping Tables
My main idea was to analyze abolishing the DECODE and CASE altogether. Since numerous SQL queries were performing the matching thing, and mistakenly performing so inconsistently, I proposed returning it with a mapping table. Here's how:

1. Create a table with two posts, one of the matching sort as "status", the other as a number.

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

2. Populate the table with all probable "status", and to which number you like 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 perpetually probable to alter the primary table to make its position post a foreign key on this one (it might be fastened to a vendor request for paid job, as it is in this case), but if it is probable, it's worth considering.
4. In the SQL queries, add a unite relative standing on "status" to this new table you created

FROM table1 t1 JOIN mapper m USING (status)

5. Replace the DECODE/CASE assertion with the "number" post of this new table.

SELECT m.value

The Pros and Cons
The superiority are that it's very not hard, not hard to appreciate, and you recognise it's dependable through all your queries. Should you ever like to change the mapping, you can without difficulty renew that table, and recognise that all your queries have been updated.

Also, you can more without difficulty take superiority of indicators now (function-based indicators notwithstanding). That's not truly applicable in this instance, but it may be in other cases.

The penalty is having another join. But as Tom Kyte one time said "joins are not evil. Databases were born to join." There are sopposedly some instances (in Oracle 10, for example) where at times augmenting another table reasons for the optimizer to make truly horrid options, and it truly reduces tempo down your query, virtually as if you've bang a threshold. Chances are that you can address this worry either with patches or by chatting with your DBA about some of the optimizer settings. In general, you can dread the reaper, and don't dread joins.

Wrap Up

If you've got numerous queries that are all employing DECODE and/or CASE to depiction one worth to another, analyze bringing ahead a mapping table and uniting that into your queries instead. It will make them less difficult to uphold, and is relying on your indicators it might even tempo up some queries.