Filtering Rows
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Filtering Rows

  1. #1
    Join Date
    May 2005
    Posts
    1

    Filtering Rows

    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

  2. #2
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    You can use distinct.
    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.

    Oracle DBA

Posting Permissions

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


Click Here to Expand Forum to Full Width