| Multiple/Single Line Query [message #224164] |
Tue, 13 March 2007 05:48  |
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  |
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", ORA-01427: single-row subquery returns more than one row |
|
|
|
|
|
|
|
| Re: Multiple/Single Line Query [message #224197 is a reply to message #224164] |
Tue, 13 March 2007 06:49 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
