| Having a problem with inline query with dblink [message #418866] |
Tue, 18 August 2009 23:41  |
kashifchughtai
Messages: 125
Registered: October 2007
|
Senior Member |
|
|
Dear All,
I am having strage issue related to inline query.
getting the error "single row subquery return more than one row"
Error is self descriptive ..but when i query outer and inner query separately with hard coded values it returns me only one row.
below are the quries
select uma.*,
(SELECT utc_etd FROM flight_list_entries fle,flight_segment_entries fshe
WHERE fshe.fle_id = fle.flt_id
AND fshe.flight_date = '22apr2009'
AND fle.flt_org_desig = 'EK'
AND fshe.seg_apt_from = 'DXB'
AND fshe.seg_apt_to = 'KHI'
AND to_char(fle.flt_numb) = uma.act_no
)
from UPLOADED_MEMBER_ACTIVITIES uma
where card_no ='00190000075'
when i run these quries with hard coded values like
outer query : select * from UPLOADED_MEMBER_ACTIVITIES uma
WHERE
card_no like '00190000075'
results : AUB_ID USR_CREATED CREATED CARD_NO PTNR_CODE ACT_TYPE_CODE ACT_NO ACT_DATE CLASS_OF_SERVICE SGMT_CITY_PAIR
45 CLP_OWNR 19/09/2001 15:15:43 00190000075 EK FLT 600 09/01/2008 K DXBKHI
and inner query like :
SELECT * FROM flight_list_entries fle,flight_segment_entries fshe
WHERE fshe.fle_id = fle.flt_id
AND fshe.flight_date = '22apr2009'
AND fle.flt_org_desig = 'EK'
AND fshe.seg_apt_from = 'DXB'
AND fshe.seg_apt_to = 'KHI'
AND to_char(fle.flt_numb) = 600
result :FLT_DTE FLT_ORG_DESIG FLT_NUMB FLT_SUFFIX APT_FROM APT_TO
22/04/2009 EK 600 - DXB KHI
both quries returns only one record I couldnt figure it out why this error is coming?..
any idea?
|
|
|
|
|
|
|
|
|
|
| Re: Having a problem with inline query with dblink [message #418897 is a reply to message #418894] |
Wed, 19 August 2009 01:59  |
bonker
Messages: 402
Registered: July 2005
|
Senior Member |
|
|
sorry to nitpick but
| Quote: | I am having strage issue related to inline query.
|
The code you have pasted is scalar subquery and not a inline query or to be more precise inline view.
For it to be inline view, you have change your query like this
select uma.*, fl_le_se.utc_etd from
UPLOADED_MEMBER_ACTIVITIES uma,
(SELECT utc_etd,to_char(fle.flt_numb) flt_numb FROM flight_list_entries fle,flight_segment_entries fshe
WHERE fshe.fle_id = fle.flt_id
AND fshe.flight_date = '22apr2009'
AND fle.flt_org_desig = 'EK'
AND fshe.seg_apt_from = 'DXB'
AND fshe.seg_apt_to = 'KHI'
) fl_le_se
where uma.card_no ='00190000075'
and fl_le_se.flt_numb = uma.act_no
And On a side note Is your flight_date column in flight_segment_entries table some thing other than date data type?
[Updated on: Wed, 19 August 2009 02:05] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Having a problem with inline query with dblink [message #418912 is a reply to message #418901] |
Wed, 19 August 2009 02:44 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| kashifchughtai wrote on Wed, 19 August 2009 09:10 | flight date is in "date" data type.
but even if it is a scalar query, it should work? how it is returning more then one row?
|
I told you.
That part of the query is not executed just for the one row you think, but also for others.
The join between the scalar subquery and table uma is done after querying (at least part of) the scalar subquery.
|
|
|
|
| Re: Having a problem with inline query with dblink [message #418925 is a reply to message #418866] |
Wed, 19 August 2009 03:47 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Run this - it should show you the values for which your Scalar subquery is returning multiple rows:
SELECT to_char(fle.flt_numb),count(*)
FROM flight_list_entries fle,flight_segment_entries fshe
WHERE fshe.fle_id = fle.flt_id
AND fshe.flight_date = '22apr2009'
AND fle.flt_org_desig = 'EK'
AND fshe.seg_apt_from = 'DXB'
AND fshe.seg_apt_to = 'KHI'
GROUP BY to_char(fle.flt_numb)
HAVING COUNT(*) > 1 |
|
|
|
| Re: Having a problem with inline query with dblink [message #418944 is a reply to message #418866] |
Wed, 19 August 2009 05:05 |
kashifchughtai
Messages: 125
Registered: October 2007
|
Senior Member |
|
|
@frank : but in the end it should return only one result
. do u mean that it is returning the rows even if records are not matched?
@bonker: yea if i rewrite the query it works fine...
@JRowbottom :
It does not return any reslult..however if i remove the having clause it returns:
FLT_NUMB COUNT(*)
604 1
606 1
600 1
602 1
|
|
|
|
| Re: Having a problem with inline query with dblink [message #418955 is a reply to message #418944] |
Wed, 19 August 2009 05:34 |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
Execute this query to find the offending record(s).
select * from
(
select uma.act_no,
(SELECT count(*) FROM flight_list_entries fle,flight_segment_entries fshe
WHERE fshe.fle_id = fle.flt_id
AND fshe.flight_date = '22apr2009'
AND fle.flt_org_desig = 'EK'
AND fshe.seg_apt_from = 'DXB'
AND fshe.seg_apt_to = 'KHI'
AND to_char(fle.flt_numb) = uma.act_no
) cnt
from UPLOADED_MEMBER_ACTIVITIES uma
where card_no ='00190000075'
)
where
cnt > 1
As @Frank, already mentioned it is executing the scalar subquery for every single record.
Execution of the above query is something similar to this
a) Get me all the records from uploaded_member_activities
b) For each record execute the scalar subquery and select the
requested column
c) Once step b is finished apply the filter card_no = <something> and display only those rows.
If you do an explain plan of the query you will understand what oracle is doing.
Remember not every single conditions in the where clause are always a join. In this example it is a filter and not a join.
Hope this explains why you are hitting that error.
Regards
Raj
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
