Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    54

    Question Unanswered: If, Then (case) statement

    I have a table in Oracle called item with the following data:

    item_code brand_code
    ------------ -------------
    0000899 NI
    0002121 CS
    0002134 LE


    and another table called item_info with the following data:

    item_code brand_name
    ------------ ---------------
    0000899
    0002121
    0002134


    ...what I'd like to do is create a statement with an if/then (or even a "case" statement) that would look at the value of "brand_code" in the "item" table then enter another value like "NIKE" in the "brand_name" field in the "item_info" table. I would give the statement 10 or 15 possible entries for "brand_code" and "brand_name"?

  2. #2
    Join Date
    Aug 2003
    Posts
    32
    Instead of using if...then, how about using another table that matches brand codes with brand names?

    create table item (item_code varchar(10), brand_code varchar(10));
    insert into item values ('0000899', 'NI');
    insert into item values ('0002121', 'CS');
    insert into item values ('0002134', 'LE');

    create table item_info (item_code varchar(10), brand_name varchar(20));
    insert into item_info values ('0000899', '');
    insert into item_info values ('0002121', '');
    insert into item_info values ('0002134', '');

    create table brand (brand_code varchar(10), brand_name varchar(20));
    insert into brand values ('NI', 'Nike');
    insert into brand values ('CS', 'Cosmo');
    insert into brand values ('LE', 'Levi');

    Now we can update the brand name from the brand code using that table:

    update
    item_info
    set
    brand_name = (
    select
    brand.brand_name
    from
    brand inner join
    item on brand.brand_code = item.brand_code
    where
    item.item_code = item_info.item_code
    )

    select
    *
    from
    item_info

    item_code brand_name
    ---------- --------------------
    0000899 Nike
    0002121 Cosmo
    0002134 Levi
    Last edited by stacey_richards; 01-28-04 at 17:06.

  3. #3
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    I'm in agreement with Stacey on most of solution offered... except, if you are using a relational database system (which I am assuming) you would be replicating data.

    Create your "brand" table with a code designation ("NI") and a full description ("Nike"), then in your item table reference only the brand code. If you need to find the description, join your item table to the brand code table.

    This way, you are taking steps toward normalizing your data and building a better RDBMS

    JoeB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •