3

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
Improve this question
4
  • 2
    I guess you don't have a ToiletTypeDescription table (ID, ToiletTypeId, Value). That would be one way to handle this with a join. Hardcoding text values is a maintenance nightmare Commented Feb 1, 2016 at 15:26
  • @kevinsky That is correct. Commented Feb 1, 2016 at 15:43
  • As these strings are not labels and they could change I call this a variety of business logic. Sure a Case expression works but every time there is a new value or if you want delivery in a different language you have to go back and change it. Tables are free, at worst you could make a view with select 'A', 'Automatic Standard from dual union..... Commented Feb 1, 2016 at 15:50
  • @kevinsky I am in full agreement. However, requirements are what they are. :b Commented Feb 1, 2016 at 15:52

5 Answers 5

Reset to default
4

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?

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

This one works well so far. You might want to update to include parentheses between CASE and END, though.
@MarkBuffalo No parentheses are needed, in fact they would make the code invalid?
Hmm, dunno. Your code doesn't work for me. In fact, only this does: (case ToiletType when 'A' then 'Automatic Standard' when 'P' then 'Portable Potty' end) as ToiletType. Keep in mind, I am selecting multiple columns, and expect this result to be a column.
I have updated my answer to show the complete code, which will work without (or indeed with) the parentheses you are adding.
Yeah, it works. I was wrong earlier. It'll work either with or without parentheses.
3

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;
Improve this answer

1 Comment

@MarkBuffalo . . . I cannot see how this would produce two such columns. Only one is named ToiletType.
3

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;
Improve this answer

Comments

2

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.

Improve this answer

2 Comments

I like this example. The 'Unknown_Type '||ToiletType part is pretty clever. (Did you include a space on purpose, though?)
@MarkBuffalo Yes, the space is intentional, to make the output more readable in that case.
1

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.

Improve this answer

1 Comment

Agreed. However, this is legacy stuff. Can't change the database. There is also no ToiletTypes tablet. I provided this as an example. Perhaps it was a bad example, then?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.