CODEWITHSUNDEEP
sqloracle-database
Mark Buffalo
Mark Buffalo

Reputation: 776

Oracle SQL: Alternative to nested REPLACE() in Select statement?

So I have a query that looks something like this:

SELECT IncidentNumber, 
    ToiletType, 
    ToiletDangers,
    IncidentDate
FROM Core.LostLawsuits

...which returns the following results sort of like this:

+----------------+------------+---------------------------+---------------+
| IncidentNumber | ToiletType | ToiletDangers             | Incident Date |
+----------------+------------+---------------------------+---------------+
| 2100           | A          | Spontaneous Combustion    | 2/1/2016      |
+----------------+------------+---------------------------+---------------+
| 2101           | P          | Attracts Bear Stampede    | 2/1/2016      |
+----------------+------------+---------------------------+---------------+

What I'd like to do is get the results, but change the ToiletType column result when outputting it. Currently, I am using a nested REPLACE(), and would like to know if there's a better / different way of doing this, while maintaining a one column result for ToiletType:

SELECT IncidentNumber, 
    REPLACE(REPLACE(ToiletType, 'A', 'Automatic Standard'), 'P', 'Portable Potty') as ToiletType,
    ToiletDangers,
    IncidentDate
FROM Core.LostLawsuits

Upvotes: 3

Views: 2424

Answers (5)

Tony Andrews
Tony Andrews

Reputation: 132710

A CASE expression would work for your example:

case ToiletType
   when 'A' then 'Automatic Standard'
   when 'P' then 'Portable Potty'
   end

i.e.

SELECT IncidentNumber, 
       case ToiletType
          when 'A' then 'Automatic Standard'
          when 'P' then 'Portable Potty'
       end as ToiletType,
       ToiletDangers,
       IncidentDate
FROM   Core.LostLawsuits

Perhaps better though would be to join to a ToiletTypes table?

Upvotes: 4

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

Just for completeness: In Oracle, there is also the decode function that could be used, as in

select decode(ToiletType, 'A', 'Automatic Standard', 
                          'P', 'Portable Potty', 
                          'Unknown Type '||ToiletType) ...

which causes a bit less typing than CASE... WHEN... END but I prefer CASE anyway because it's ANSI SQL compatible.

Upvotes: 2

Emil Moise
Emil Moise

Reputation: 393

You could also try decode function but I doubt you will have any performance or readability improvements:

select 
  incidentnumber, 
  decode(ToiletteType, 'A', 'Automatic Standard', 'P', 'Portable Potty', ToiletteType),
  toiletdangers,
  incidentdate
from 
  core.lostlawsuits;

Upvotes: 3

Tom H
Tom H

Reputation: 47402

The ToiletType should be a foreign key to a lookup table. The lookup table would contain at least two columns - a ToiletTypeCode and a ToiletTypeDescription. You would want a foreign key between your existing table and the new lookup table to ensure referential integrity.

Once that's in place, the query becomes trivial:

SELECT
    LL.IncidentNumber,
    TT.ToiletTypeDescription AS ToiletType,
    LL.ToiletDangers,
    LL.IncidentDate
FROM Core.LostLawsuits LL
INNER JOIN Core.ToiletTypes TT ON TT.ToiletTypeCode = LL.ToiletType

This also ensures that only valid Toilet Types are used in your LostLawsuits table.

Finally, it means that if a new ToiletType is added in the future it simply becomes a single INSERT statement to the ToiletTypes table instead of trying to hunt down every bit of code where you used some CASE or REPLACE statement to get descriptions.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270993

In this case, I think you want CASE:

SELECT IncidentNumber, 
       (CASE ToiletType
            WHEN 'A' THEN 'Automatic Standard'
            WHEN 'P' THEN 'Portable Potty'
            ELSE ToiletType
        END) as ToiletType,
      ToiletDangers,
      IncidentDate
FROM Core.LostLawsuits;

Upvotes: 3

Related Questions