can any one please help me to filter the rows.
I have a Table with the different codes which the staff have to have them as mandatory and optional.
The lo_code must be UNIC, TT, DWD, WORKA (mandatory)
And any two other lo_codes of either ASS, GT, DSO, LPT.
I need to display the employee which have the mandatory and have 2 of the optional codes. But if the employee has 3 optional codes, the following query returns 2 records, but i need to display only one record for each employee discarding other records which have the combinations of the optional codes.
QUERY AS FOLLOWS:
select m1.employee# empno, e.surname name1 ,e.first_name name2 ,
s.job# jobno, s.occup_pos_title tit, substr(s.clevel,9,2) clevel1,substr(s.clevel,15,2) clevel2,c.description desc1,
m1.lo_code lo1, m1.assess_code ass1, m2.lo_code lo2 , m2.assess_code ass2 , m3.lo_code lo3, m3.assess_code ass3,
m4.lo_code lo4 , m4.assess_code ass4,
m5.lo_code lo5, m5.assess_code ass5,
m6.lo_code lo6, m6.assess_code ass6,
'COMPETENTCOMPETENTCOMPETENTCOMPETENTCOMPETENTCOMPETENT', 'COMPLETE','NOT COMPLETE') done, count(m5.assess_code) cnt
from employee e, substantive s , codes c,
where m1.lo_code = 'UNIC'
and m2.lo_code = 'TT'
and m3.lo_code = 'DWD'
and m4.lo_code = 'WORKA'
and m1.employee# = m2.employee#
and m1.employee# = m3.employee#
and m1.employee# = m4.employee#
and m1.employee# = m5.employee#
and m1.employee# = m6.employee#
and m5.LO_CODE in ('ASS', 'GT', 'DSO', 'LPT')
and (m6.lo_code in('ASS', 'GT', 'DSO', 'LPT') and m5.lo_code!=!=m6.lo_code)
and m5.assess_code = 'COMPETENT'
and m1.employee# = e.employee#
and m1.employee# = s.employee#
and c.code = s.clevel
and c.kind = 'CLEVEL8'
and s.occup_pos_cat = 'ATUT'
and sysdate between s.commence_date and s.occup_term_date
group by m1.employee#, e.surname,e.first_name,
s.job#, s.occup_pos_title, substr(s.clevel,9,2),substr(s.clevel,15,2),c.description,
m1.lo_code, m1.assess_code, m2.lo_code , m2.assess_code , m3.lo_code , m3.assess_code , m4.lo_code , m4.assess_code,
'COMPETENTCOMPETENTCOMPETENTCOMPETENTCOMPETENTCOMPETENT', 'COMPLETE','NOT COMPLETE') having count(*)=1
order by done, m1.assess_code, m2.assess_code
I would appreciate your help.
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Click Here to Expand Forum to Full Width