3 Replies Latest reply: Oct 7, 2012 9:58 AM by sb92075 RSS

    01427. 00000 -  "single-row subquery returns more than one row"

    u0597684
      hello all,
      how can i solve it
      SELECT 
             v.code
            ,v.bar_code
            ,v.arb_name
            ,v.eng_name
            ,v.branch_no
            ,(SELECT sum(quantity) FROM viw_whs_items_movement GROUP BY branch_no)
      FROM  viw_whs_items_movement V
      this code give me this error
      01427. 00000 - "single-row subquery returns more than one row"
      i know i have more value but i need all this value

      group by result
      SELECT sum(v.quantity) 
      FROM viw_whs_items_movement v 
      GROUP BY v.branch_no
      ##########################
      SUM(V.QUANTITY)
      ---------------
               114453 
                 2501 
                 8137 
                13270 
                15230 
               120626 
                22536 
                 2926 
                12848 
                37509 
                20911 
                 7503 
                 6248 
                17221 
                  462 
                   24 
                15041 
                10307 
                12524 
               526627 
                10564 
                 8230 
                13204 
                 2840 
                80769 
              1418238 
               215694 
                15238 
                 5063 
               104187 
                  913 
               147079 
                11293 
                12678 
                18855 
      
       35 rows selected 
        • 1. Re: 01427. 00000 -  "single-row subquery returns more than one row"
          515111
          The message is quite obvious. You can not have multiple records in your sub-select. If you really need all these values, how do you want to display them? All in one row? Multiple row? How should other attributes be displayed??

          Besides, what version of Oracle Database are you using? 10g? 11g?
          • 2. Re: 01427. 00000 -  "single-row subquery returns more than one row"
            APC
            Sub-queries must be scalar. As you demonstrate your sub-query returns more than one row. Hence the error.

            One solution would be to correlate the results of the sub-query by joining the branch_id with the branch_id of the outer query. However, as both queries use the same table you can just use an analytic SUM() instead:
            SELECT 
                   v.code
                  ,v.bar_code
                  ,v.arb_name
                  ,v.eng_name
                  ,v.branch_no
                  ,sum(quantity) over (partition by branch_no) as branch_tot
            FROM  viw_whs_items_movement V      
            Cheers, APC
            • 3. Re: 01427. 00000 -  "single-row subquery returns more than one row"
              sb92075
              Arafa wrote:
              hello all,
              how can i solve it
              SELECT 
              v.code
              ,v.bar_code
              ,v.arb_name
              ,v.eng_name
              ,v.branch_no
              ,(SELECT sum(quantity) FROM viw_whs_items_movement GROUP BY branch_no)
              FROM  viw_whs_items_movement V
              with
              summer as (
              SELECT branch_no, sum(quantity) TOTAL FROM viw_whs_items_movement GROUP BY branch_no
              )
              SELECT
              v.code
              ,v.bar_code
              ,v.arb_name
              ,v.eng_name
              ,v.branch_no
              , summer.total
              FROM viw_whs_items_movement V, summer
              WHERE SUMMER.BRANCH_NO = V.BRANCH_NO
              /