CODEWITHSUNDEEP
sql
Garrick Brim
Garrick Brim

Reputation: 87

SQL Adding a column to a table, with case statement

I have my case statement but would like to add the new column "Unit_Type" to the table...

Unit_type =(case [INSP_UNIT_TYPE_ID] 
    when '9' then 'Semi Trailer'
    when '7' then 'Pole Trailer'
    when '6' then 'Other'
    when '5' then 'Motor Carrier'
    when '3' then 'Full Trailer'
    when '2' then 'Dolly Converter'
    when '14' then 'Intermodal Chassis'
    when '12' then 'Van'
    when '11' then 'Truck Tractor'
    when '10' then 'Straight Truck'
    else 'Invalid'
        end)    from [dbo].[Insp_Unit_Pub_01012015_05282015];

Upvotes: 2

Views: 55214

Answers (2)

Paolo
Paolo

Reputation: 2254

don't do that and use a view.

the above suggestion is the result of a wild guess because you provide neither context nor database structure so it may be plain wrong or not suitable for your situation.

your untold requirement looks like some kind of visualization of the table with human readable data and that's what views are good for.

create a view adding the required joins or including the CASE statement you are trying to put in the table (check the syntax ^^):

CREATE VIEW [name of the view here]
AS
SELECT field1,
       field2,
       field3,
       Unit_type = CASE [INSP_UNIT_TYPE_ID] 
        WHEN '9' THEN 'Semi Trailer'
        WHEN '7' THEN 'Pole Trailer'
        WHEN '6' THEN 'Other'
        WHEN '5' THEN 'Motor Carrier'
        WHEN '3' THEN 'Full Trailer'
        WHEN '2' THEN 'Dolly Converter'
        WHEN '14' THEN 'Intermodal Chassis'
        WHEN '12' THEN 'Van'
        WHEN '11' THEN 'Truck Tractor'
        WHEN '10' THEN 'Straight Truck'
        ELSE 'Invalid'
       END
FROM [dbo].[Insp_Unit_Pub_01012015_05282015];

as suggested by some comment you may put a JOIN in the query instead of the CASE statement if the human readable text comes from a lookup table.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You seem to be using SQL Server. Just add a computed column:

alter table Insp_Unit_Pub_01012015_05282015
    add Unit_Type as (case [INSP_UNIT_TYPE_ID] 
                        when '9' then 'Semi Trailer'
                        when '7' then 'Pole Trailer'
                        when '6' then 'Other'
                        when '5' then 'Motor Carrier'
                        when '3' then 'Full Trailer'
                        when '2' then 'Dolly Converter'
                        when '14' then 'Intermodal Chassis'
                        when '12' then 'Van'
                        when '11' then 'Truck Tractor'
                        when '10' then 'Straight Truck'
                        else 'Invalid'
                     end);

This does the calculation when you fetch the value. The advantage is that you don't have to write a trigger to change the value when INSP_UNIT_TYPE_ID changes.

By the way, this question suggests that you need a reference table for INSP_UNIT_TYPE, with INSP_UNIT_TYPE_ID as the primary key. A JOIN is actually a better way to get the value (although using a computed column with a JOIN is rather cumbersome).

Upvotes: 14

Related Questions