oracle union all sum,Oracle DBA-ERP10.7 DB-Error: ORA-01789-Union all bug

今天收到mail说下面这个SQL无法在ERP10.7中查询，出现Error:

ORA-01789 查询区块的结果数据域位数目不正确

SELECT a.net_qty

FROM mkl_fp_fne_net_aps_v a

WHERE SUBSTR(a.part_number, 5, 4) = '1667'

AND a.organization_id = 433

AND a.compile_designator = 'F0103P2025'

测试了一下果然如此。观察了一下view结构，没有发现字段数目不一致的现象，view内容如下:

CREATE OR REPLACE FORCE VIEW APPS.MKL_FP_FNE_NET_APS_V

(FCST_ID, PART_NUMBER, ARRIVAL_TIME, NET_QTY, GROSS_QTY,

CUSTOMER_ID, CUSTOMER_NAME,

COMPILE_DESIGNATOR, ORGANIZATION_ID)

AS

SELECT

a.po_id_inv_buffer AS FCST_ID,

a.PART_NUMBER,

a.arrival_time,

a.part_quantity - NVL(b.ASSIGNED_QUANTITY,0)

AS NET_QTY,

a.part_quantity AS GROSS_QTY,

a.customer_id,

a.customer_name,

a.compile_designator,

a.organization_id

FROM mkl_fp_fne_gross_aps a,

(SELECT

pia.po_id_inv_buffer,SUM(pia.assigned_qauntity) AS

ASSIGNED_QUANTITY,

pia.organization_id,pia.compile_designator

FROM

mkl_fp_fne_out_pia pia

GROUP BY

pia.po_id_inv_buffer,pia.organization_id,pia.compile_designator)

b

WHERE a.po_id_inv_buffer = b.po_id_inv_buffer (+)

AND a.part_quantity -

NVL(b.ASSIGNED_QUANTITY,0 ) > 0

AND a.organization_id = b.organization_id

(+)

AND a.compile_designator =

b.compile_designator (+)

UNION ALL

SELECT 'APS_'||msi.segment1||TO_CHAR(mfd.forecast_date,'_MMDD')

AS FCST_ID,

msi.segment1 PART_NUMBER,

mfd.forecast_date arrival_time,

SUM(mfd.current_forecast_quantity) AS NET_QTY,--part_quantity,

SUM(mfd.current_forecast_quantity) AS GROSS_QTY,

TO_NUMBER('') customer_id,

TO_CHAR('') customer_name,

mfd.forecast_designator compile_designator,

mfd.organization_id organization_id

FROM mtl_system_items msi,

mrp_forecast_dates mfd

WHERE msi.inventory_item_id =

mfd.inventory_item_id

AND msi.organization_id =

mfd.organization_id

AND msi.item_type NOT

IN('P','SI')

AND

LTRIM(RTRIM(NVL(mfd.attribute3,'N'))) = 'Y'--items using others'

BOM appear in this view

AND mfd.forecast_date >=

--demands that were not earlier than last month

(SELECT ADD_MONTHS(TRUNC(SYSDATE,'MONTH'),-qq.data_cut_month)

FROM mkl_fp_parameters qq

WHERE qq.organization_id = mfd.organization_id

AND ROWNUM = 1)

GROUP BY

mfd.organization_id,mfd.forecast_designator,msi.segment1,mfd.forecast_date;

从user反映来看，查询单个栏位不可以，Select

*可以查询。很是奇怪。

以前没有遇到类似问题，上网络搜索，大多是使用union

all时前后两个块字段不一致导致,但是，这个并不是这个SQL的原因.偶然,也发现网络有人遇到和这个SQL类似的问题，但是都是无解。

后来经过多次测试，发现将Union

all更改union就可以了，原因不明，但是user说他们需要重复数据，不能这么更改。但是，user说将上面语句改成如下就可以：

SELECT a.net_qty

FROM (

SELECT *

FROM mkl_fp_fne_net_aps_v a) a

WHERE SUBSTR(a.part_number, 5, 4) = '1667'

AND a.organization_id = 433

AND a.compile_designator = 'F0103P2025'

果然如此. 原因到底在什么地方？

后来继续经过4个小时的测试(其中以为是别名的问题，后来被排除了，重新建立测试用表，继续测试)，发现在union

all的组成部分中第一个Query不能有和表同级的子查询：

(SELECT pia.po_id_inv_buffer,SUM(pia.assigned_qauntity) AS

ASSIGNED_QUANTITY,

pia.organization_id,pia.compile_designator

FROM

mkl_fp_fne_out_pia pia

GROUP BY

pia.po_id_inv_buffer,pia.organization_id,pia.compile_designator)

b

把以上语句建立成view,再将主view重新编译，即OK;

继续深入发现，将union all两部分掉换位置，也OK.

看起来是oracle7的union all bug所致!

到此问题结束。