insert into...calculated field based on another field 1

[HajdukSplit]

Hi all,

I'm a newbie here. I did search for what I'm going to ask and couldn't find anything similar, so here it is:

For data analysis purpose I have to create one table that contains data from several tables. SQL to create it is rather complex, but after the table is created all consequent SQL's on it are rather simple.
My table needs to have one calculated field that is based on the value of another field.

For example one of the fields in my table will be "product" field. I need a calculated field "product_type" that depending on "product" value can have 3 different values, lets say TypeA, TypeB and TypeC. So, I have a mapping list for this field.
Now I don't know how to implement this during the creation of the table? Is there some simple syntax to achieve this? Both fields are character fields.

I need something like:
IF "product" in (<list of products for TypeA>) then "product_type" = TypeA
...but don't know how to do it within "inser into..."

Many thanks in advance!

 

[r937]

INSERT
  INTO ...
     ( product
     , product_type
     , price )
SELECT product
     , CASE WHEN product IN ('widget','doodad')
            THEN 'typeA'
            WHEN product IN ('gizmo','thingum')
            THEN 'typeB'
            ELSE 'typeC' END
     , price
  FROM ...

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[HajdukSplit]

Thanks r937!

This was exactly what I needed!