SQL & PL/SQL

Hi,

I am working On Oracle9i , Why the below query is throwing an error called ,

ORA - 03001: unimplemented feature.

-- Nested table of another type ,
create or replace type my_type_a as table of My_type;


select my_type(call_id ,
caller_name ,
org_desc ,
env_id ,
env_desc ,
appl_id ,
appl_desc ,
module_id ,
module_desc ,
call_type_id ,
call_type_desc ,
priority ,
upduserid ,
lastupdated_username ,
call_desc ,
mode_desc ,
received_time ,
assignment_team ,
status ,
elapsed_time ,
resolved_min ,
region ,
stat_upddate ,
team_desc ,
eta_date ,
caller_contact ,
email ,
affected_users ,
outage_time ,
QA_DONE ,
LAST_ACTION_TEAM ,
LAST_ACTION_USER ,
last_action_username ,
last_action_date ,
last_action_teamdesc ,
refid ,
logged_name ,
pmreview,
main_status)
bulk collect into my_type_a
from
(SELECT /*+ FIRST_ROWS(30) */
a.call_id, a.entry_date, NVL (INITCAP (b.full_name)
,caller_name) AS caller_name
,c.description AS org_desc
,a.env_id
,i.env_desc
,a.appl_id
,d.appl_desc
,a.module_id
,e.module_desc
,a.call_type_id
,f.call_type_desc
,a.priority
,a.upduserid
,INITCAP (g.full_name) AS lastupdated_username
,a.call_desc
,h.mode_desc
,a.received_time
,a.assignment_team
,CASE WHEN (a.status = 0 OR a.status = 1 OR a.status = 7 OR a.status IS NULL)
AND ((a.priority = 0 AND ROUND (lcc.pkg_com.fn_datediff ('MI',a.entry_date,lcc.pkg_com.fn_getgmt (24))) > 300)
OR (a.priority = 1 AND ROUND (lcc.pkg_com.fn_datediff ('MI',a.entry_date,lcc.pkg_com.fn_getgmt (24))) > 2880)
OR (a.priority = 2 AND ROUND (lcc.pkg_com.fn_datediff ('MI',a.entry_date,lcc.pkg_com.fn_getgmt (24))) > 10080)
OR (a.priority = 3 AND ROUND (lcc.pkg_com.fn_datediff ('MI',a.entry_date,lcc.pkg_com.fn_getgmt (24))) > 21600)) THEN 3
WHEN a.status = 7 THEN 1
WHEN a.status = 12 THEN 4
ELSE NVL (a.status, 1)
END AS status
,CASE WHEN a.status = 2 THEN ROUND(lcc.pkg_com.fn_datediff ('MI',a.entry_date,a.status_date))
ELSE ROUND (lcc.pkg_com.fn_datediff ('MI',a.entry_date,lcc.pkg_com.fn_getgmt (24)))
END AS elapsed_time
,CASE WHEN a.status in (2, 4) THEN ROUND(lcc.pkg_com.fn_datediff ('MI',a.entry_date,
(SELECT MAX (upddate) FROM lcc.vhd_callstatus z WHERE z.status = 4 AND z.call_id = a.call_id GROUP BY z.call_id)))
ELSE 0
END AS resolved_min
,CASE WHEN a.orgid in (1,100,200) THEN a.orgid
ELSE j.regionorgid
END AS region
,(SELECT coalesce(MAX(upddate),a.upddate) FROM lcc.vhd_callstatus stat WHERE stat.call_id = a.call_id) as stat_upddate
,(SELECT team_desc from lcc.vhd_teams t where t.team_id = a.assignment_team) as team_desc
,a.eta_date
,coalesce(a.caller_contact, b.telephone) AS caller_contact
,coalesce(a.caller_email, b.email) as email
,a.affected_users
,a.outage_time
,a.QA_DONE
,a.LAST_ACTION_TEAM
,a.LAST_ACTION_USER
,INITCAP (k.full_name) AS last_action_username
,a.last_action_date
,l.team_desc as last_action_teamdesc
,a.refid
,INITCAP (lu.full_name) AS logged_name
,a.pmreview
,a.status as main_status
FROM lcc.vhd_calldesk a
LEFT OUTER JOIN lcc.lcc_userinfo_details b ON b.user_name = a.caller_id
INNER JOIN lcc.com_organization c ON c.code = a.orgid
INNER JOIN lcc.vhd_applications d ON d.appl_id = a.appl_id
INNER JOIN lcc.vhd_modules e ON e.appl_id = a.appl_id AND e.module_id = a.module_id
INNER JOIN lcc.com_rptorganization j ON j.orgid = a.orgid AND j.tree = 'HLPDK'
INNER JOIN lcc.vhd_calltypes f ON f.call_type_id = a.call_type_id
LEFT OUTER JOIN lcc.lcc_userinfo_details g ON g.user_name = a.upduserid
LEFT OUTER JOIN lcc.vhd_callmode h ON h.mode_id = a.mode_id
LEFT OUTER JOIN lcc.vhd_environment i ON i.appl_id = a.appl_id AND i.env_id = a.env_id
LEFT OUTER JOIN lcc.lcc_userinfo_details k ON k.user_name = a.last_action_user
LEFT OUTER JOIN lcc.vhd_teams l ON l.team_id = a.last_action_team
LEFT OUTER JOIN
(select CALL_ID,upduserid
FROM lcc.VHD_CALLDESK_HISTORY P where upddate in ( select min(upddate) from lcc.VHD_CALLDESK_HISTORY Q
WHERE P.CALL_ID = Q.CALL_ID
group by call_id)) ku
ON ku.call_id = a.call_id
LEFT OUTER JOIN lcc.lcc_userinfo_details lu ON lu.user_name = NVL(ku.upduserid,A.upduserid))


Please suggest ...

Raj