with order_base as --获取订单基础情况
(
select ou.order_key order_key, ou.order_quantity_i, ood.dispatch_time_t, ou.part_number_s, 'I032ZZ01' pline_name_s
from order_uv ou left join at_as_om_orderdispatchstatus ood on ou.order_key = ood.order_54
union all
select mo.atr_key order_key, mo.order_quantity_i, mo.dispatch_time_t, mo.part_number_s, to_char(mo.pline_name_s) pline_name_s
from at_as_mm_order_plan mo
)
,
plan_quantity_base as --根据订单派发时间确定订单派发的班次
(
select to_char(ob.dispatch_time_t,'yyyymmdd') work_day, dispatch_time_t,
(
select su.description shift_name
from at_as_sm_calendar sc, shift_uv su
where su.shift_key = sc.shift_198
and category_s = 'MFG' and target_s = ob.pline_name_s and target_type_s = 'Production Line'
and ob.dispatch_time_t >= sc.start_time_t and ob.dispatch_time_t <= sc.end_time_t
and to_char(ob.dispatch_time_t,'yyyymmdd') = to_char(sc.start_time_t,'yyyymmdd')
) shift_name,
ob.pline_name_s, ob.order_quantity_i
from order_base ob
)
--select * from plan_quantity_base;
--Select To_Char(Pqb.Dispatch_Time_T,'yyyymmdd') Work_Day, Pqb.Shift_Name, Pqb.Pline_Name_S
--From Plan_Quantity_Base Pqb
--group By to_char(Pqb.dispatch_time_t,'yyyymmdd'), Pqb.Shift_Name, Pqb.Pline_Name_S;
--,
--plan_quantity as ( --根据订单派发时间计算生产线的班次计划产量
select to_char(Pqb.dispatch_time_t,'yyyymmdd') work_day, pqb.shift_name, pqb.pline_name_s, sum(pqb.order_quantity_i) plan_quantity
From Plan_Quantity_Base Pqb
--Where Pqb.Pline_Name_S Like Decode(roduction_Line,'ALL','%%',Null,'%%',roduction_Line)
--and pqb.shift_name like decode(:shift,'ALL','%%',:shift)
group By to_char(Pqb.dispatch_time_t,'yyyymmdd'), Pqb.Shift_Name, Pqb.Pline_Name_S
--)
问题描述：
sql运行到 plan_quantity 时报错：单行子查询返回多个行
1、单独对每个语句块写sql语句查询，排查下来发现问题发生地方在 plan_quantity 上；
2、运行语句（Select To_Char(Pqb.Dispatch_Time_T,'yyyymmdd') Work_Day, Pqb.Shift_Name, Pqb.Pline_Name_S
From Plan_Quantity_Base Pqb）和（select * from plan_quantity_base）都没问题；
3、运行语句
（Select To_Char(Pqb.Dispatch_Time_T,'yyyymmdd') Work_Day, Pqb.Shift_Name, Pqb.Pline_Name_S
From Plan_Quantity_Base Pqb
group By to_char(Pqb.dispatch_time_t,'yyyymmdd'), Pqb.Shift_Name, Pqb.Pline_Name_S）
和
（ select to_char(Pqb.dispatch_time_t,'yyyymmdd') work_day, pqb.shift_name, pqb.pline_name_s, sum(pqb.order_quantity_i) plan_quantity
From Plan_Quantity_Base Pqb
--Where Pqb.Pline_Name_S Like Decode(roduction_Line,'ALL','%%',Null,'%%',roduction_Line)
--and pqb.shift_name like decode(:shift,'ALL','%%',:shift)
group By to_char(Pqb.dispatch_time_t,'yyyymmdd'), Pqb.Shift_Name, Pqb.Pline_Name_S）
报错：单行子查询返回多个行；
问题疑问：
1、在 plan_quantity 语句块中，已经group by 了，并且查询的列中并没有子查询，为什么会发生这种错误？
请大家帮忙分析下，实在没有分析出来。
