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
dBforums
>
combining records using case
09-09-10, 10:31
Linear Mode
