- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- ORA-01427: single-row subquery returns more than o...
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-02-2008 09:55 AM
тАО03-02-2008 09:55 AM
I am trying to update the price changes on table if there is a change in another table.I searched the forum but could not get any satisfying result.The query returns multiple rows and even though i used where..in or where exists clause i am not able to update multiple rows.This is using multiple tables to satisfy various criteria.Any suggestions?
update SQ_PRODUCT_PRICE set (
net_price
,list_price
,update_date
,update_source
)=
(
select distinct ifp.netprice
,ifp.pricepmt
,sysdate
,'UPD'
from if_ezs_price ifp
, sq_currency scur
, sq_price_term sprterm
, sq_country_price_descriptor cpd
,sq_product_price sqpp
where sqpp.product_code=ifp.product_code
and ifp.country = cpd.pd_geography_code
and scur.iso_code = cpd.pd_currency_code
and sprterm.standard_code = cpd.pd_term_code
and ifp.currency = scur.hp_code)
where (sqpp.PRODUCT_CODE,sqpp.COUNTRY_CODE,sqpp.PD_GEOGRAPHY_CODE,sqpp.PD_CURRENCY_CODE,sqpp.PD_TERM_CODE) IN
(select distinct sqp2.PRODUCT_CODE,sqp2.COUNTRY_CODE,sqp2.PD_GEOGRAPHY_CODE,sqp2.PD_CURRENCY_CODE,sqp2.PD_TERM_CODE
from sq_product_price sqp2
, sq_currency scur
, sq_price_term sprterm
, sq_country_price_descriptor cpd
,if_ezs_price ifp
where sqp2.product_code=ifp.product_code
and ifp.country = cpd.pd_geography_code
and scur.iso_code = cpd.pd_currency_code
and sprterm.standard_code = cpd.pd_term_code
and ifp.currency = scur.hp_code
and (ifp.netprice <> sqp2.net_price
or ifp.pricepmt <> sqp2.list_price
));
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-02-2008 03:12 PM
тАО03-02-2008 03:12 PM
Re: ORA-01427: single-row subquery returns more than one row
Its always better to use primary key in where clause because it ensure that only required rows would be output.
For particular case query is too short for a unix guy to understand.I would suggest to rewrite the query you may find the problem.
Hope this help.
Kapil
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-02-2008 09:41 PM
тАО03-02-2008 09:41 PM
Re: ORA-01427: single-row subquery returns more than one row
You must ensure that the subquery below returns a SINGLE ROW!:
(SELECT DISTINCT ifp.netprice,
ifp.pricepmt,
sysdate,
'UPD'
FROM if_ezs_price ifp,
sq_currency scur,
sq_price_term sprterm,
sq_country_price_descriptor cpd,
sq_product_price sqpp
WHERE sqpp.product_code = ifp.product_code
AND ifp.country = cpd.pd_geography_code
AND scur.iso_code = cpd.pd_currency_code
AND sprterm.standard_code = cpd.pd_term_code
AND ifp.currency = scur.hp_code)
This is the only part that is failing depending, of course, on the constraints on the underlying tables (UNIQUE, PRIMARY KEY).
Note that DISTINCT does not mean a SINGLE ROW will be returned.
Have a look at the constraints and data.
hope this helps!
kind regards
yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-02-2008 09:47 PM
тАО03-02-2008 09:47 PM
Re: ORA-01427: single-row subquery returns more than one row
You can verify if the query is returning a SINGLE ROW by running the following query:
SELECT DISTINCT ifp.netprice,
ifp.pricepmt,
sysdate,
'UPD', count(*)
FROM if_ezs_price ifp,
sq_currency scur,
sq_price_term sprterm,
sq_country_price_descriptor cpd,
sq_product_price sqpp
WHERE sqpp.product_code = ifp.product_code
AND ifp.country = cpd.pd_geography_code
AND scur.iso_code = cpd.pd_currency_code
AND sprterm.standard_code = cpd.pd_term_code
AND ifp.currency = scur.hp_code
group by 1,2,3,4
having count(*) > 1;
Let us know if you need any further assistance.
kind regards,
yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-03-2008 08:52 AM
тАО03-03-2008 08:52 AM
Solutionupdate table_1 set (
column_list_1
) =
(select distinct
column_list_2
from table_list_1
where criteria_list_1
)
where (column_list_3) IN
(select distinct column_list_4
from table_list_2
where criteria_list_2
)
;
The problem is on criteria_list_1.
The distinct cannot guarantee the single result.
Good luck.
Xiaogang