ORA-01427: single-row subquery returns more than one row (exists statement)

0
votes

I'm trying to execute the next query:

 SELECT  
   PROMO_PLAN.promo_plan_id,  
   PROMO_PLAN.promo_plan_name,  
   PROMO.promo_category_code,  
   PROMO.promo_id,  
   PROMO.allow_plan_code,  
   PROMO_PLAN.event_type_code,  
   ( SELECT  
     NVL(PROMO_MAX_TIMING_DURATION_VW.duration, 900) 
    FROM  
   promo_max_timing_duration_vw 
    WHERE  
   PROMO_PLAN.promo_plan_id = PROMO_MAX_TIMING_DURATION_VW.promo_plan_id) 
  AS duration,  
   PROMO_PLAN.start_date,  
   PROMO_PLAN.end_date,  
   PROMO_PLAN.day_type_id 
  FROM  
   promo_plan,  
   promo 

 WHERE  
   PROMO_PLAN.promo_plan_id <> -1 
    AND PROMO_PLAN.promo_id = PROMO.promo_id 
    AND PROMO_PLAN.promo_plan_id = 18150
   AND '27/09/2012' BETWEEN PROMO_PLAN.start_date AND PROMO_PLAN.end_date
   AND (PROMO_PLAN.day_type_id = 1307001 

   OR PROMO_PLAN.day_type_id = -1)
   AND (PROMO_PLAN.promo_target_id = -1 
    OR  EXISTS ( 
      SELECT  
       1 
      FROM  
       promo_plan_channel 
      WHERE  
       PROMO_PLAN_CHANNEL.promo_plan_id = PROMO_PLAN.promo_plan_id 
        AND PROMO_PLAN_CHANNEL.channel_id = 10829))

and I get the error message : ORA-01427: single-row subquery returns more than one row

I tried to change the last sub-query and it changed nothing.

can someone tell me how to fix it?

thanks

3
sqloracle
(and the reason you got downvoted is because this is not how you ask a good question; you just dumped a huge SQL query on us w/o any context or attempt to simplify it to poinpoint the problem) – MK.
down vote on this one - the question is just wanting someone to fix what I assume you are getting paid to do? There is really only one place this SQL can fail with this error. Also, we don't know your data - how can we possible fix this query? – Trent
A subquery referenced through EXISTS or NOT EXISTS will never be the cause of this error. It is the subquery in the SELECT clause that is raising this. – David Aldridge
@Trent hah, that's silly. All questions on SO are about things people are getting paid to do (except for the homework questions) – MK.
@MK - not necessarily. Sometimes questions are related to things someone is trying to learn for themselves. How one discerns the difference between homework questions and self-education questions is not obvious, which may explain (in part) the demise of the homework tag. – Bob Jarvis - Reinstate Monica

3 Answers

4
votes

Most likely your problem is that 

 ( SELECT  
     NVL(PROMO_MAX_TIMING_DURATION_VW.duration, 900) 
    FROM  
   promo_max_timing_duration_vw 
    WHERE  
   PROMO_PLAN.promo_plan_id = PROMO_MAX_TIMING_DURATION_VW.promo_plan_id)

returns multiple rows.

2
votes

Does the query run if you remove this part?

   ( SELECT  
     NVL(PROMO_MAX_TIMING_DURATION_VW.duration, 900) 
    FROM  
   promo_max_timing_duration_vw 
    WHERE  
   PROMO_PLAN.promo_plan_id = PROMO_MAX_TIMING_DURATION_VW.promo_plan_id)

If so, you know the culprit. Modify this subquery to return at most one row.

0
votes

try it like this:

SELECT  
   PROMO_PLAN.promo_plan_id,  
   PROMO_PLAN.promo_plan_name,  
   PROMO.promo_category_code,  
   PROMO.promo_id,  
   PROMO.allow_plan_code,  
   PROMO_PLAN.event_type_code,  
   p.d AS duration,  
   PROMO_PLAN.start_date,  
   PROMO_PLAN.end_date,  
   PROMO_PLAN.day_type_id 
  FROM  
   promo_plan,  
   promo, 
   ( SELECT NVL(PROMO_MAX_TIMING_DURATION_VW.duration, 900) d, promo_plan_id
    FROM promo_max_timing_duration_vw 
   ) p
 WHERE  
   PROMO_PLAN.promo_plan_id <> -1 
    AND PROMO_PLAN.promo_id = PROMO.promo_id 
    AND PROMO_PLAN.promo_plan_id = 18150
    and PROMO_PLAN.promo_plan_id = p.promo_plan_id
   AND '27/09/2012' BETWEEN PROMO_PLAN.start_date AND PROMO_PLAN.end_date
   AND (PROMO_PLAN.day_type_id = 1307001 

   OR PROMO_PLAN.day_type_id = -1)
   AND (PROMO_PLAN.promo_target_id = -1 
    OR  EXISTS ( 
      SELECT  
       1 
      FROM  
       promo_plan_channel 
      WHERE  
       PROMO_PLAN_CHANNEL.promo_plan_id = PROMO_PLAN.promo_plan_id 
        AND PROMO_PLAN_CHANNEL.channel_id = 10829))