Home » SQL & PL/SQL » SQL & PL/SQL » Query not working in 10g.
Query not working in 10g. [message #247633] Tue, 26 June 2007 10:59 Go to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
problem with running the following query.

select k.user_SID, k.USER_LAN_NAME, k.USER_LAST_NAME, k.USER_FIRST_NAME,k.USER_MI_NAME, 
k.USER_STATUS,k.USER_EMAIL_ADDR,k.USER_PHONE,k.review_status, k.initiator_user_sid, k.auth_user_sid, k.initiator_lan_name, 
k.auth_lan_name, l.user_sid as wo_user_sid, l.ounit_code, l.role_code, 
l.wo_review_status, l.wo_initiator_user_sid, l.wo_auth_user_sid, 
l.ounit_short_desc, l.role_desc, l.wo_initiator_lan_name, l.wo_auth_lan_name 
from 
(
select x.user_SID, x.USER_LAN_NAME, x.USER_LAST_NAME, x.USER_FIRST_NAME,x.USER_MI_NAME, 
x.USER_STATUS,x.USER_EMAIL_ADDR,x.USER_PHONE,x.review_status, x.initiator_user_sid, x.auth_user_sid, 
p.user_lan_name as initiator_lan_name, q.user_lan_name as auth_lan_name 
from work_user x join user p on (x.initiator_user_sid = p.user_sid) 
left outer join user q on (x.auth_user_sid = q.user_sid) 
where x.user_sid not in 
(select user_sid from user 
) and x.review_status in ('P')
) k 
full outer join 
(select y.user_sid, y.ounit_code, y.role_code, y.review_status as wo_review_status, 
y.initiator_user_sid as wo_initiator_user_sid, y.auth_user_sid as wo_auth_user_sid, 
o.ounit_short_desc, rr.role_desc, p.user_lan_name as wo_initiator_lan_name, 
q.user_lan_name as wo_auth_lan_name 
from work_ounit_member y join ounit o on (y.ounit_code = o.ounit_code) 
join ref_role rr on (y.role_code = rr.role_code) 
join user p on (y.initiator_user_sid = p.user_sid) 
left outer join user q on (y.auth_user_sid = q.user_sid) 
where y.user_sid not in
(select user_sid from user) and 
y.review_status in ('P')
) l 
on k.user_sid = l.user_sid 
order by UPPER(k.user_last_name), UPPER(k.user_first_name), l.ounit_short_desc, l.role_code;




this query worked fine in 9i. but not in 10g. It even results in 'end of channel communication error'.

Re: Query not working in 10g. [message #247636 is a reply to message #247633] Tue, 26 June 2007 11:03 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hmm, use 9i or open a SR.

Regards
Michel
Previous Topic: Edit an Existing Stored Procedure/Trigger in Notepad
Next Topic: Table Join on Same Table
Goto Forum:
  


Current Time: Sun Dec 11 02:09:31 CST 2016

Total time taken to generate the page: 0.14726 seconds