ORA-12054: cannot set the ON COM

http://web.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10736/basicmv.htm#sthref501

周五在创建物化视图时，碰到了ORA-12054，视图死活创建不成功，今天过来加班，首先解决这个问题，最终竟然是NVL函数的问题。

出错的视图创建脚本：

CREATE MATERIALIZED VIEW MV_PO_REC_INV_MATCH 
BUILD IMMEDIATE REFRESH FAST ON COMMIT
AS SELECT  a.order_id,
        a.au_id,
        a.store_code,
        a.store_name,
        a.receipt_code
       nvl(SUM(a.receipt_net_amount), 0) AS receiptNetAmount,
       nvl(SUM(a.receipt_vat_amount), 0) AS receiptVatAmount, 
       SUM(nvl(a.receipt_net_amount, 0) - nvl(a.matched_net_amount, 0)) AS matchAmount,
       nvl(SUM(a.receipt_disc_net_amount), 0) AS receiptDiscNetAmount
   FROM po_rec  a 
  WHERE a.STATUS = 1 
  AND (a.matched_net_amount IS NULL OR (nvl(a.receipt_net_amount, 0) - nvl(a.matched_net_amount, 0)) != 0)
  GROUP BY a.order_id,a.au_id, a.store_code, a.store_name, a.receipt_code

在查看这篇文章时，注意到FAST REFRESH不支持NVL函数：

Only SUM, COUNT, AVG, STDDEV, VARIANCE, MIN and MAX are upported for fast refresh.

修改视图脚本如下：

CREATE MATERIALIZED VIEW MV_PO_REC_INV_MATCH 
BUILD IMMEDIATE 
REFRESH FAST 
ON COMMIT 
AS SELECT 
order_id, 
au_id, 
store_code, 
store_name, 
receipt_code, 
SUM(nvl(receipt_net_amount, 0)) AS receiptNetAmount, 
SUM(nvl(receipt_vat_amount, 0)) AS receiptVatAmount,  
SUM(nvl(receipt_net_amount, 0) - nvl(matched_net_amount, 0)) AS matchAmount, 
SUM(nvl(receipt_disc_net_amount, 0)) AS receiptDiscNetAmount 
FROM po_rec WHERE STATUS = 1 AND (matched_net_amount IS NULL OR (nvl(receipt_net_amount, 0) - nvl(matched_net_amount, 0))  != 0)
GROUP BY (order_id,au_id, store_code, store_name, receipt_code)

成功了...