Home » SQL & PL/SQL » SQL & PL/SQL » Having a problem with inline query with dblink (oracle 10gR2)
Having a problem with inline query with dblink [message #418866] Tue, 18 August 2009 23:41 Go to next message
kashifchughtai
Messages: 113
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 #418873 is a reply to message #418866] Wed, 19 August 2009 00:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
How can you tell for sure that the inline query will only be executed for uma.act_no = 600?
Re: Having a problem with inline query with dblink [message #418887 is a reply to message #418866] Wed, 19 August 2009 01:30 Go to previous messageGo to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
Frank,

If you see this query :

select * from UPLOADED_MEMBER_ACTIVITIES uma
WHERE  
card_no like '00190000075'


it return me only one record with flt_number= 600 as i mentioned in my previous post also..
Re: Having a problem with inline query with dblink [message #418894 is a reply to message #418887] Wed, 19 August 2009 01:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Yes, but there is no guarantee that combining the two will result in the order of execution you presume.
Re: Having a problem with inline query with dblink [message #418897 is a reply to message #418894] Wed, 19 August 2009 01:59 Go to previous messageGo to next message
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 #418901 is a reply to message #418897] Wed, 19 August 2009 02:10 Go to previous messageGo to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
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?
Re: Having a problem with inline query with dblink [message #418910 is a reply to message #418901] Wed, 19 August 2009 02:33 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
If it is date type then you should not compare date with strings
[B]
you should code it as fshe.flight_date = to_date('22apr2009','ddmonyyyy')


If you rewrite as inline query the does it 1 return row or more than 1 row?
Re: Having a problem with inline query with dblink [message #418912 is a reply to message #418901] Wed, 19 August 2009 02:44 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
kashifchughtai
Messages: 113
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 Go to previous messageGo to next message
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
Re: Having a problem with inline query with dblink [message #418969 is a reply to message #418866] Wed, 19 August 2009 07:28 Go to previous message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
thanks Raj for explanation. so it means that not necessarily its getting the multiple records for flight600 on 22apr2009 but may be well before for some other flights and date, as we may have duplicate values for "same date and flight number".

regards,
Kashif
Previous Topic: polling in oracle
Next Topic: how to get the procedure name inside the package body
Goto Forum:
  


Current Time: Thu Dec 08 20:13:22 CST 2016

Total time taken to generate the page: 0.13622 seconds