|
|
|
|
| Re: How can we write this query in a better or more efficeint way? [message #304653 is a reply to message #304505] |
Thu, 06 March 2008 00:53   |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
When you specify COL = (subquery), this is called a "Scalar Sub-query".
The requirement for a scalar subquery is that it will return ZERO or ONE row. ALWAYS!
Even if your sub-queries are querying on a Unique or Primary Key, Oracle does not seem to know that it is guaranteed to return no more than one row. So it's always on the lookout for a ORA-01427: single-row subquery returns more than one row violation.
What does this mean? It means that some logical execution paths are not available. More specifically, it is not able to UNNEST the subqueries. See here for more on the perils of nesting.
You can help by either relaxing the requirement for one-and-one-only result, or by guaranteeing that one and one only row will be returned.
Try either of the following:
SELECT country
INTO l_syst_grp
FROM hr_locations
WHERE location_id IN
(SELECT location_id
FROM per_business_groups
WHERE business_group_id IN
(SELECT business_group_id
FROM org_organization_definitions
WHERE organization_id IN
(SELECT creation_organization_id
FROM gmd_recipes_b
WHERE recipe_id=l_recipe_id) ) );
SELECT country
INTO l_syst_grp
FROM hr_locations
WHERE location_id =
(SELECT MAX(location_id)
FROM per_business_groups
WHERE business_group_id =
(SELECT MAX(business_group_id)
FROM org_organization_definitions
WHERE organization_id =
(SELECT MAX(creation_organization_id)
FROM gmd_recipes_b
WHERE recipe_id=l_recipe_id) ) );
Ross Leishman
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
