Home » SQL & PL/SQL » SQL & PL/SQL » Multiple/Single Line Query
Multiple/Single Line Query [message #224164] Tue, 13 March 2007 05:48 Go to next message
Eddie Excel
Messages: 3
Registered: March 2007
Junior Member
Hi all,

I am trying to return multiple lines from this query but it says the sub query is single line.

If i enter a list of ad id's it will return the results.

The query should bring back a good few thousand results each time.

Im a bit new to SQL so any help would be appreciated

select distinct
ad.AD_ID,
ad.LISTING_ID,
li.AB_ID,
ad.CUST_ID,
ad.LASTMODBY,
ad.LASTMODDT,
ad.PUBRESERVATIONVALUETYPE_ID,
ad.ADSTATUS_ID,
he.DESCR "Classification",
(SELECT COPYTEXT FROM btdprod.V_ADCOMPONENT WHERE ADTYPECOMP_ID = 'PRIMARYNAM' AND ad_id =ad.ad_id) "Company Name",
(SELECT COPYTEXT FROM btdprod.V_ADCOMPONENT WHERE ADTYPECOMP_ID = 'STREET' AND ad_id =ad.ad_id) "Address Line 1",
(SELECT COPYTEXT FROM btdprod.V_ADCOMPONENT WHERE ADTYPECOMP_ID = 'LOCALITY' AND ad_id =ad.ad_id) "Address Line 2",
(SELECT COPYTEXT FROM btdprod.V_ADCOMPONENT WHERE ADTYPECOMP_ID = 'POSTTOWN' AND ad_id =ad.ad_id) "Town/City",
(SELECT COPYTEXT FROM btdprod.V_ADCOMPONENT WHERE ADTYPECOMP_ID = 'AREACODE' AND ad_id =ad.ad_id) "Area Code",
(SELECT COPYTEXT FROM btdprod.V_ADCOMPONENT WHERE ADTYPECOMP_ID = 'NUMBER' AND ad_id =ad.ad_id) "Telephone",
(SELECT COPYTEXT FROM btdprod.V_ADCOMPONENT WHERE ADTYPECOMP_ID = 'POSTCODE' AND ad_id =ad.ad_id) "Post Code"
--Continue for the rest of the address 
from btdprod.ad
join btdprod.listing li on li.LISTING_ID = ad.LISTING_ID
join btdprod.V_ADCOMPONENT va on va.AD_ID = ad.AD_ID
join btdprod.heading he on he.HEAD_ID = ad.HEAD_ID
where ad.ADSTATUS_ID <> 'O'
and ad.PUBL_ID like 'YORK'
and ad.EDITION_ID like '0708X'
and va.COPYTEXT is not null
GROUP BY Telephone
Re: Multiple/Single Line Query [message #224171 is a reply to message #224164] Tue, 13 March 2007 06:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you use an inline Select statement in the list of columns selected (like this one:
(SELECT COPYTEXT FROM btdprod.V_ADCOMPONENT WHERE ADTYPECOMP_ID = 'PRIMARYNAM' AND ad_id =ad.ad_id) "Company Name",
) then they have to return only one row. I'm guessing that the error you're getting is
ORA-01427: single-row subquery returns more than one row
Re: Multiple/Single Line Query [message #224172 is a reply to message #224171] Tue, 13 March 2007 06:09 Go to previous messageGo to next message
Eddie Excel
Messages: 3
Registered: March 2007
Junior Member
IS there a way to get multiple rows?

I dont really want to have to insert a list of AD_ID's for it to search
Re: Multiple/Single Line Query [message #224194 is a reply to message #224172] Tue, 13 March 2007 06:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What results are you looking for here?
Are you expecting multiple rows back from each of these sub-queries?
Re: Multiple/Single Line Query [message #224197 is a reply to message #224164] Tue, 13 March 2007 06:49 Go to previous message
Eddie Excel
Messages: 3
Registered: March 2007
Junior Member
the subquery brings back 1 row for each ad_id instead of 7 rows. it creates a column instead of having it as rows.

If i did search for all ad_ids in that book it should bring back around 7000 results and i want it to display the sub query results for each line. But i dont want to have to enter a list of ad_ids
Previous Topic: how to copy data from one DB to another DB
Next Topic: How to invoke multiple sessions of sql*plus thru pl/sql program.
Goto Forum:
  


Current Time: Thu Dec 08 10:46:40 CST 2016

Total time taken to generate the page: 0.06240 seconds