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)
成功了...
