Filtering Rows

[sandya.G]

Hi,
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,
s.occup_pos_cat poscat,
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,
decode(m1.assess_code||m2.assess_code||m3.assess_code||m4.assess_code||m5.assess_code||m6.assess_cod e,
'COMPETENTCOMPETENTCOMPETENTCOMPETENTCOMPETENTCOMPETENT', 'COMPLETE','NOT COMPLETE') done, count(m5.assess_code) cnt
from employee e, substantive s , codes c,
employee_module_competency m1,
employee_module_competency m2,
employee_module_competency m3,
employee_module_competency m4,
employee_module_competency m5,
employee_module_competency m6
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,
s.occup_pos_cat,
m1.lo_code, m1.assess_code, m2.lo_code , m2.assess_code , m3.lo_code , m3.assess_code , m4.lo_code , m4.assess_code,
m5.lo_code,
m5.assess_code,m6.lo_code,
m6.assess_code,
decode(m1.assess_code||m2.assess_code||m3.assess_code||m4.assess_code||m5.assess_code||m6.assess_cod e,
'COMPETENTCOMPETENTCOMPETENTCOMPETENTCOMPETENTCOMPETENT', 'COMPLETE','NOT COMPLETE') having count(*)=1
order by done, m1.assess_code, m2.assess_code

I would appreciate your help.
Thanks
Sandy

[simply_dba]

You can use distinct.