Re: Mighty Morphin' Job Descriptions.

3srf_at_qlink.queensu.ca (Frampton Steve R) writes:

>Hello:
 

>I have a view which selects a variety of information from joined tables,
>including employee ID #, work location, hours-per-week, name, and job
>description.
 

>All this information is grouped, so that if the employee has more than
>one position, but all the information is the same (this would happen when
>an employee has the same position, but split into multiple G/L accounts,
>for one example) only one record would be returned.
 

>But if everything is the same, but the job description is different for
>each position, each record is returned. Thus:
 

>Emp. ID Location Hours/week Name Job Description
>------- -------- ---------- --------------- -----------------
>11201 15 20 Smith Classroom Teacher
>11201 15 20 Smith Vice-Principal
 

>In the above example, every field is the same except for the job description.
>Therefore, I would like to "morph" (ahem) them into something like:
>'Classroo/Vice-Pri' (the first 8 characters of each description) or perhaps
>'Multiple Posn.' would suffice. Then I would only like *one* record
>returned with the "morphed" description, and the hours-per-week totalled.
>In keeping with our example:
 

>Emp. ID Location Hours/week Name Job Description
>------- -------- ---------- --------------- -----------------
 

>11201 15 40 Smith Classroo/Vice-Pri
> ^^^^ (sum of hours)
 

>How do I accomplish this? Remember, if any one or more field differs
>then I truly wish to see all the records returned.
 

>Any information would be greatly appreciated. Thanks in advance.

Well, it's easy enough to get a 'Multiple Posn.' out, if you don't mind a one-fold increase in processing time:

select emp_id "Emp. ID", location, hours "Hours/week", name, job_desc "Job Description"
from emp_job
group by emp_id, location
having count(*) = 1
union
select emp_id, location, sum(hours), name, 'Multiple Posn.' from emp_job
group by emp_id, location
having count(*) > 1
/

You could get the concat of substr going if you knew the maximum number of positions held by any one person, and joined emp_job to itself that many times with an appropriate ordering forced in the where clause, but the query is ugly and resource-hungry. If you really need that level, though, feel free to e-mail me if you need more information about it.

L. Scott Johnson
Ex-ORACLE programmer
Graphics Specialist
USCarolina Received on Wed Oct 19 1994 - 14:46:00 CET