-
Rewrite an analytical query
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
PHP Code:
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'.
PHP Code:
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
-
Here is the way to go without analytics.
Thanks to asktom.oracle.com
Concatenate the columns and use substr to split them
PHP Code:
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|