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  |
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  |
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 |
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 #330154 is a reply to message #330142] |
Fri, 27 June 2008 12:23 |
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 #330332 is a reply to message #330126] |
Sun, 29 June 2008 13:25 |
flyboy
Messages: 1903
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).
|
|
|
|
Goto Forum:
Current Time: Sun Aug 24 06:43:19 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
