首页 新闻 搜索 专区 学院

单行子查询返回多个行 Issue分析求助

0
悬赏园豆:20 [待解决问题]

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对应结果图片:

问题描述2对应图片

 

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

Mr-qing的主页 Mr-qing | 初学一级 | 园豆:184
提问于:2018-01-06 12:56
< >
分享
所有回答(2)
0
dudu | 园豆:37128 (高人七级) | 2018-01-06 13:36

感谢回答;

由于在我都sql中,我都查询项里面其实并没有子查询项,在group by里面也没有子查询项,只是from 用的子查询,而我的from后面的子查询其实是可以正常运行的

支持(0) 反对(0) Mr-qing | 园豆:184 (初学一级) | 2018-01-06 14:55

@Mr-qing: group by 中加上 pqb.order_quantity_i 试试

支持(0) 反对(0) dudu | 园豆:37128 (高人七级) | 2018-01-06 15:07
0

应该是外部的查询报错了吧,plan_quantity整个查询单独拿出来查应该不会报错吧,你最后的截图不是单独拿出来查的截图。请提供完整的sql

nicky0227 | 园豆:1069 (小虾三级) | 2018-01-08 10:28
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册