Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: combining records using case

    Hi,

    Hope someone can help me.
    In the database I'm using a descriptions of areas, but some have more than one description and I want to combine them into one if possible. One example is the following:

    Agriculture
    Agriculture Full Time
    Agriculture Part Time

    As much as I would like to change the names to all Agriculture, the descriptions with the suffixes Full Time and Part Time are from last years data and are rolled over to this year.

    So I created a select query with a case statement to try and combine them all to say Agriculture. This is the query:

    Code:
    SELECT DISTINCT
    (CASE WHEN DEPARTMENT.DESCRIPTION LIKE '%Full Time' THEN REPLACE(DEPARTMENT.DESCRIPTION, 'Full Time')
    WHEN DEPARTMENT.DESCRIPTION LIKE '%Part Time' THEN REPLACE(DEPARTMENT.DESCRIPTION, 'Part Time')
    ELSE DEPARTMENT.DESCRIPTION END) DEPARTMENT
    FROM COURSE, COURSE_INSTANCE, COURSE_LEVEL, DEPARTMENT
    WHERE COURSE.OBJECT_ID = COURSE_INSTANCE.COURSE AND COURSE.COURSE_LEVEL = COURSE_LEVEL.OBJECT_ID
    AND COURSE.DEPARTMENT = DEPARTMENT.OBJECT_ID AND (COURSE_LEVEL.COURSE_LEVEL IN('2','3'))
    AND (COURSE_INSTANCE.ACADEMIC_YEAR = 2010)
    ORDER BY DEPARTMENT
    And this is the output:

    http://www.hexasp.net/qoe/images/result.gif

    AS you can see the result produces Agriculture twice, I figure it is because the descriptions with the suffixes are combined into one, but the single description isn't.

    Is there any way around this?

    Thanks
    <- Hides behind a rock.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As you are hard-coding it anyway, here's one option:
    Code:
    SQL> select * from test;
    
    DESCRIPTION
    ------------------------------
    Agriculture
    Agriculture Full Time
    Agriculture Part Time
    Animal Care
    Construction
    
    SQL> select distinct
      2    case when description like 'Agriculture%' then 'Agriculture'
      3         else description
      4    end descr
      5  from test;
    
    DESCR
    ------------------------------
    Construction
    Agriculture
    Animal Care
    
    SQL>

  3. #3
    Join Date
    Sep 2005
    Posts
    240
    Fantastic, just what a wanted to do.

    Cheers
    <- Hides behind a rock.

Posting Permissions

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