Rewrite an analytical query

[dharma]

I have a requrement in where I have been able to write the query using analytical query as the database is 9i, but unfortunately that wont run in reports 6i.

Heres a sample table(not exact one tho) and the query. Can anyone suggest a alternate for this query in ver 8 (without analytics)

The table emp_add_det

SYS_ID CLIENT_ID EMP_ID ADDTP_CODE ADD_DETAIL
------ --------- ------ ---------- -----------------------------------
     1       100      1 O          100 X ST, LB, CA 90001
     2       100      1 M          100 X ST, LB, CA 90001
     3       100      1 M          100 X ST, LB, CA 90001
     4       100      2 O          100 X ST, LB, CA 90001
     5       100      2 O          100 X ST, LB, CA 90001
     6       100      3 M          100 X ST, LB, CA 90001
     7       100      4 O          100 X ST, LB, CA 90001
     8       100      5 B          100 X ST, LB, CA 90001
     9       100      5 O          100 X ST, LB, CA 90001
    10       100      6 B          100 X ST, LB, CA 90001
    11       100      7 O          100 X ST, LB, CA 90001
    12       100      7 M          100 X ST, LB, CA 90001
    13       100      7 B          100 X ST, LB, CA 90001

13 rows selected.

Retrieve [B]only one row [/B] for each emp_id with addtp_code in O or M and if there is a M for an emp_id the M should show else a row from O should show. 
Also note that each emp_id can have multiple rows with addtp_code = M or O.

the query using analytics is

SELECT *
  FROM (SELECT d.*,
               MIN (sys_id) OVER (PARTITION BY client_id, emp_id ORDER BY addtp_code)
                                                                   min_sys_id,
               MIN (addtp_code) OVER (PARTITION BY client_id, emp_id ORDER BY addtp_code)
                                                               min_addtp_code
          FROM EMP_ADD_DET d
         WHERE d.client_id = 100 
                                      AND d.addtp_code IN ('M', 'O'))
 WHERE min_sys_id = sys_id AND min_addtp_code = addtp_code 

The above query works fine. I couldnt find an alternate without the analytics. BTW any other analytic query faster than this is also appreciated for academics.


Also Is there anyother way than the one below to get emp_ids who have ONLY addtp_code='O'.

SELECT client_id,emp_id,
       SUM(DECODE(addtp_code,'O',0,1)),
                            SUM(DECODE(addtp_code,'O',1,0))
  FROM EMP_ADD_DET
    GROUP BY client_id,emp_id
HAVING SUM(DECODE(addtp_code,'O',0,1)) = 0
   AND SUM(DECODE(addtp_code,'O',1,0)) > 0 

Any help or suggestions is appreciated.
Thanks,
-dharma

[dharma]

Here is the way to go without analytics.
Thanks to asktom.oracle.com

Concatenate the columns and use substr to split them

SELECT client_id, emp_id, SUBSTR(constr,1,1) addtp_code,
       TO_NUMBER(SUBSTR(constr,2,6)) sys_id, SUBSTR(constr,8) addr 
  FROM (SELECT client_id,emp_id,MIN(addtp_code||TO_CHAR(sys_id,'fm000009')||add_detail)     constr 
          FROM emp_add_det
         WHERE addtp_code  IN ('M','O')                      
         GROUP BY client_id,emp_id) t 

-dharma