Get all french departement codes including Corsica

Question

I want to get all departement in France. They can be count from 1 to 97, except for Corsica which is 2A and 2B.

I made this query that well get all numbers (except Corsica):

WITH RECURSIVE dep as (
       SELECT 1 as id
    UNION ALL
       SELECT id + 1 FROM dep WHERE id < 99
)

But now, I want to replace the "20" by "2A" and "2B". I tried to do this:

WITH RECURSIVE dep as (
       SELECT 1 as id
    UNION ALL
       SELECT id + 1 FROM dep WHERE id < 99
    UNION ALL
       SELECT '2A'
    UNION ALL
       SELECT '2B'
)

But I get this error: #1292 - Truncated incorrect DOUBLE value: '2B'

Here is what I want:

id
1
2
3
[omitted output ...]
17
18
19
2A
2B
21
22
...

How can I fix it?

Answer 1

You can create a new derived table via a cte or subquery that holds your translated values, and then join that into your recursive cte result set:

WITH RECURSIVE dep as (
       SELECT 1 as id
    UNION ALL
       SELECT id + 1 FROM dep WHERE id < 99
)
, id_translation AS
(
  SELECT 20 as id, '2A' as alt_id
  UNION ALL
  SELECT 20, '2B'
)
SELECT COALESCE(idt.alt_id, dep.id) as id 
FROM dep
  LEFT OUTER JOIN id_translation idt 
     ON dep.id = idt.id

---

+----+
| id |
+----+
| 1  |
| 2  |
| 3  |
| 4  |
| 5  |
| 6  |
| 7  |
| 8  |
| 9  |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 2A |
| 2B |
| 21 |
| 22 |
| .. |
+----+