语句1:单独执行是没有问题的,
select substr(proposed_location,3,2) CLASS,order_no,PART_NO,
to_char(REVISED_DUE_DATE,'yyyy-mm-dd') finish_date,revised_qty_due PLAN_QTY,
qty_complete COMPLETE_QTY,qty_complete-revised_qty_due short_qty,
decode(state,'Started',1) Started,decode(state,'Closed',1) Closed,decode(state,'Released',1) Released,
to_number('') order_qty,to_number('') s_rate,to_number('') c_rate,to_number('') r_rate
from ifsapp.shop_ord so
where part_no in (select part_no from ifsapp.inventory_part ip where contract='SKY' and
sk_part_category_db='Product')
and contract='SKY'and revised_due_date=to_date('&date','yyyy-mm-dd')
ORDER BY 1,qty_complete/revised_qty_due
语句2:单独执行也是没有问题的,
select class,'' order_no,'' part_no,'' finish_date,sum(plan_qty) plan_qty,sum(complete_qty) complete_qty,
sum(short_qty) short_qty,
sum(started) started,sum(closed) closed,sum(released) released,count(*) order_qty,
round((sum(started)+sum(closed))/count(*)*100,2)||'%' s_rate,
round(sum(closed)/count(*)*100,2)||'%' c_rate,round(sum(released)/count(*)*100,2)||'%' r_rate
from (select substr(proposed_location,3,2) CLASS,order_no,PART_NO,
to_char(REVISED_DUE_DATE,'yyyy-mm-dd') finish_date,revised_qty_due PLAN_QTY,
qty_complete COMPLETE_QTY,qty_complete-revised_qty_due short_qty,
decode(state,'Started',1) Started,decode(state,'Closed',1) Closed,
decode(state,'Released',1) Released,to_number('') order_qty,to_number('') s_rate,
to_number('') c_rate,to_number('') r_rate
from ifsapp.shop_ord so
where part_no in (select part_no from ifsapp.inventory_part ip where contract='SKY'
and sk_part_category_db='Product')
and contract='SKY'
and revised_due_date=to_date('&date','yyyy-mm-dd') )
group by class
语句1,语句2单独运行都没有问题,DATATPYE也是匹配的.
当我在中间用上UNION ALL后,却出来ORA-00933:SQL command not properly end.
后面我猜是不是UNION ALL 与语句2的GROUP BY在ORACLE处理的优先顺序造成的?
于是我在语句1修改为SELECT * FROM (语句1) UNION ALL 语句2后运行OK.
究竟是不是这个原因呢,请知者释疑.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12391917/viewspace-323042/,如需转载,请注明出处,否则将追究法律责任。