oracle单行子查询返回多个行 order by,单行子查询返回多个行 Issue分析求助

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 了,并且查询的列中并没有子查询,为什么会发生这种错误?

请大家帮忙分析下,实在没有分析出来。

问题描述2对应结果图片:

5c5cb106eee805c40cdaaee7d3b5c846.png

问题描述3对应结果图片:

95b5a4bc215266884ac1cc6195c1777f.png

参与评论 您还未登录,请先 登录 后发表或查看评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:深蓝海洋 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值