Group values: CASE text, ELSE field value

Question

I have a ROAD table:

+----+------------+
| ID | ROAD_CLASS |
+----+------------+
|  1 | ARTERIAL A |
|  2 | ARTERIAL B |
|  3 | ARTERIAL B |
|  4 | ARTERIAL C |
|  5 | ARTERIAL C |
|  6 | ARTERIAL C |
|  7 | COLLECTOR  |
|  8 | COLLECTOR  |
|  9 | LOCAL      |
| 10 | LOCAL      |
+----+------------+

The data type for the ROAD_CLASS field is NVARCHAR2.

I would like to create a view that groups all arterial roads in to a single ARTERIAL category, but leaves the other road classes as-is:

+------------+
| ROAD_CLASS |
+------------+
| ARTERIAL   |
| COLLECTOR  |
| LOCAL      |
+------------+

How can I do this?

What I've tried:

I've successfully done part of it:

SELECT
      (CASE 
          WHEN ROAD_CLASS = 'ARTERIAL A' THEN 'ARTERIAL'
          WHEN ROAD_CLASS = 'ARTERIAL B' THEN 'ARTERIAL'
          WHEN ROAD_CLASS = 'ARTERIAL C' THEN 'ARTERIAL'
          --ELSE ROAD_CLASS
      END) AS ROAD_CLASS_GROUPED
FROM
    USER.ROAD
GROUP BY
      CASE 
          WHEN ROAD_CLASS = 'ARTERIAL A' THEN 'ARTERIAL'
          WHEN ROAD_CLASS = 'ARTERIAL B' THEN 'ARTERIAL'
          WHEN ROAD_CLASS = 'ARTERIAL C' THEN 'ARTERIAL'
      END

+--------------------+
| ROAD_CLASS_GROUPED |
+--------------------+
| null               |
| ARTERIAL           |
+--------------------+

However, instead of the other road classes being output as null, I'd like to get their field values (COLLECTOR and LOCAL).

When I try adding ELSE ROAD_CLASS to the CASE statement (as demonstrated in the SQL comment) I get an ORA-12704: character set mismatch error.

Answer 1

How about ...

select distinct 
   (case
       when road_class in('ARTERIAL A','ARTERIAL B','ARTERIAL C') then n'ARTERIAL'
       else road_class
     end ) as ROAD_CLASS_GROUPED
from user.road;

Due to the fact that the datatype of the column road_class is NVARCHAR, we need to write a N or n in front of the string literal 'ARTERIAL' in order to prevent the error message. (see also: https://docs.oracle.com/database/121/SQLRF/sql_elements003.htm#SQLRF00218 )