Home » SQL & PL/SQL » SQL & PL/SQL » Getting ORA-1427: single-row subquery returns more than one row
Getting ORA-1427: single-row subquery returns more than one row [message #330126] Fri, 27 June 2008 11:23 Go to next message
vishnuselva
Messages: 3
Registered: September 2006
Junior Member
I have two tables a master table (master) and a temporary table (temp).

I need to update five newly columns in master table based on the value in the
temporary table. The master table has a primary key but the temporary table dont have
any primary keys.The datas in the Master table are identical for different primary keys.

When i try to update query

I am getting ORA-1427: single-row subquery returns more than one row

since i dont have a PRIMARY key column in both the tables i am choosing columns by assumption which have more distinct values (member_nbr,prescription_nbr,claim_type,ymdeff,days_supply) for the where condition.

UPDATE master t1
SET (t1.mem_ded_cost, t1.physician_npi, t1.pharmacy_npi, t1.drug_status,
t1.catastrophic_cod, t1.gdcb, t1.gdca, t1.cpp, t1.npp, t1.troop,
t1.enradj, t1.lics, t1.pharmacy_network, t1.status) =
(SELECT t2.mem_ded_cost, t2.physician_npi, t2.pharmacy_npi, t2.drug_status,
t2.catastrophic_cod, t2.gdcb, t2.gdca, t2.cpp, t2.npp, t2.troop,
t2.enradj, t2.lics, t2.pharmacy_network, 'Y' status
FROM temp t2
where t1.member_nbr) = t2.member_nbr AND
t1.prescription_nbr) = t2.prescription_nbr AND
t1.claim_type) = t2.claim_type AND
t1.ymdeff) = t2.ymdeff and
t1.days_supply) = t2.days_supply)

select count(1) from master_d [count -- 160051]

select count(1) from temp [count -- 161123 ]

But the select query mentioned below have a count -- 207810

select count(* from
(SELECT t2.mem_ded_cost, t2.physician_npi, t2.pharmacy_npi, t2.drug_status,
t2.catastrophic_cod, t2.gdcb, t2.gdca, t2.cpp, t2.npp, t2.troop,
t2.enradj, t2.lics, t2.pharmacy_network, 'Y' status
FROM master t1, temp t2
where t1.member_nbr) = t2.member_nbr AND
t1.prescription_nbr) = t2.prescription_nbr AND
t1.claim_type) = t2.claim_type AND
t1.ymdeff) = t2.ymdeff and
t1.days_supply) = t2.days_supply)

Since the join condition doesn't meet some of the data do a cartesian product. so i am getting count 207810 instead of 160051. How to supress these duplicate values.

Please help me to sort out this problem
Re: Getting ORA-1427: single-row subquery returns more than one row [message #330128 is a reply to message #330126] Fri, 27 June 2008 11:27 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

select count(1) from master_d [count -- 160051]

select count(1) from temp [count -- 161123 ]



Why do you fancy count(1) over count(*) ? Is it because count(1) is efficient to count(*) ? Also are you sure query is giving you the error you have mentioned. Which version of Oracle are you using ? Because unfortunately none of your query are working for me. I am getting a syntax error.

Regards

Raj

[Updated on: Fri, 27 June 2008 11:31]

Report message to a moderator

Re: Getting ORA-1427: single-row subquery returns more than one row [message #330140 is a reply to message #330128] Fri, 27 June 2008 11:49 Go to previous messageGo to next message
vishnuselva
Messages: 3
Registered: September 2006
Junior Member
I am doing a count for how many recors in the select query.

select count(*) from
(SELECT t2.mem_ded_cost, t2.physician_npi, t2.pharmacy_npi, t2.drug_status,
t2.catastrophic_cod, t2.gdcb, t2.gdca, t2.cpp, t2.npp, t2.troop,
t2.enradj, t2.lics, t2.pharmacy_network, 'Y' status
FROM master t1, temp t2
where t1.member_nbr) = t2.member_nbr AND
t1.prescription_nbr) = t2.prescription_nbr AND
t1.claim_type) = t2.claim_type AND
t1.ymdeff) = t2.ymdeff and
t1.days_supply) = t2.days_supply)
Re: Getting ORA-1427: single-row subquery returns more than one row [message #330142 is a reply to message #330140] Fri, 27 June 2008 11:54 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
First and foremost please follow the guidelines. It is the very first link in this forum.

Execute the query you have posted in a sql*plus sesion and post the output like what I did.
SQL> select count(*) from cat;

  COUNT(*)
----------
        71

Regards

Raj
Re: Getting ORA-1427: single-row subquery returns more than one row [message #330154 is a reply to message #330142] Fri, 27 June 2008 12:23 Go to previous messageGo to next message
vishnuselva
Messages: 3
Registered: September 2006
Junior Member
SQL> select count(*) from master;

COUNT(*)
----------
160051

SQL> select count(*) from temp;

COUNT(*)
----------
161123

Please help me how to include ranking condition by assigning a row_number based on ranking in the join condition to get the first distinct row.

SELECT t2.mem_ded_cost, t2.physician_npi, t2.pharmacy_npi, t2.drug_status,t2.catastrophic_cod, t2.gdcb, t2.gdca, t2.cpp, t2.npp, t2.troop,t2.enradj, t2.lics, t2.pharmacy_network, 'Y' status
FROM master t1, temp t2
where trim(t1.member_nbr) = trim(t2.member_nbr) AND
trim(t1.prescription_nbr) = trim(t2.prescription_nbr) AND
trim(t1.claim_type) = trim(t2.claim_type) AND
trim(t1.ymdeff) = trim(t2.ymdeff) and
trim(t1.days_supply) = trim(t2.days_supply


Re: Getting ORA-1427: single-row subquery returns more than one row [message #330155 is a reply to message #330126] Fri, 27 June 2008 12:26 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Getting ORA-1427: single-row subquery returns more than one row [message #330332 is a reply to message #330126] Sun, 29 June 2008 13:25 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> Please help me how to include ranking condition by assigning a row_number based on ranking in the join condition to get the first distinct row.

It is impossible without knowing the "ranking" (ordering) rule. "First" row is senseless without having an order, which sorts data. If this rule would be specified, it shall not be difficult to implement it.

It is impossible to "include ranking condition" which is not defined (as your posts do not contain any detail of it).
Previous Topic: Updating table using triggers
Next Topic: rounding off column value
Goto Forum:
  


Current Time: Sun Dec 04 06:21:24 CST 2016

Total time taken to generate the page: 0.08021 seconds