This content has been marked as final. Show 3 replies
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?
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
1 person found this helpful
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
summer as (
SELECT branch_no, sum(quantity) TOTAL FROM viw_whs_items_movement GROUP BY branch_no
FROM viw_whs_items_movement V, summer
WHERE SUMMER.BRANCH_NO = V.BRANCH_NO