尝试创建快速刷新物化视图报错

张沛

对于两张表的查询，分别在两张表上基于列和rowid创建日志

1. CREATE MATERIALIZED VIEW LOG ON ar.ar_distributions_all
   
2.    WITH ROWID, SEQUENCE(source_id, org_id, source_table) INCLUDING NEW VALUES;
   
3. 
   
4. CREATE MATERIALIZED VIEW LOG ON ar.ar_receivable_applications_all
   
5.    WITH ROWID, SEQUENCE(receivable_application_id
   
6. , org_id, application_type,postable) INCLUDING NEW VALUES;
   

复制代码

日志创建成功，之后创建物化视图语句报错

1. CREATE MATERIALIZED VIEW mv_ard_ra
   
2. TABLESPACE  APPS_TS_TX_DATA       
   
3. BUILD IMMEDIATE
   
4. REFRESH FAST ON COMMIT AS
   
5. Select ARD.source_type,
   
6.        ARD.source_type,
   
7.        ARD.code_combination_id,
   
8.        ARD.currency_code,
   
9.        ARD.source_type,
   
10.        ARD.currency_code,
    
11.        ARD.amount_dr                ENTERED_DR,
    
12.        ARD.amount_cr                ENTERED_CR,
    
13.        ARD.acctd_amount_dr          ACCOUNTED_DR,
    
14.        ARD.acctd_amount_cr          ACCOUNTED_CR,
    
15.        ARD.currency_conversion_date CURRENCY_CONVERSION_DATE,
    
16.        ARD.currency_conversion_type CURRENCY_CONVERSION_TYPE,
    
17.        ARD.currency_conversion_rate CURRENCY_CONVERSION_RATE,
    
18.        ARD.third_party_id           THIRD_PARTY_ID,
    
19.        ARD.third_party_sub_id       THIRD_PARTY_SUB_ID,
    
20.        ARD.source_table             SOURCE_TABLE,
    
21.        ARD.source_id                SOURCE_ID,
    
22.        ARD.line_id                  AEL_ID,
    
23.        ARD.last_update_date         LAST_UPDATE_DATE,
    
24.        ARD.last_updated_by          LAST_UPDATED_BY,
    
25.        ARD.creation_date            CREATION_DATE,
    
26.        ARD.created_by               CREATED_BY,
    
27.        ARD.last_update_login        LAST_UPDATE_LOGIN,
    
28.        ARD.tax_code_id              TAX_CODE_ID,
    
29.        ARD.tax_link_id              TAX_LINK_ID,
    
30.        ARD.taxable_entered_dr       TAXABLE_ENTERED_DR,
    
31.        ARD.taxable_entered_cr       TAXABLE_ENTERED_CR,
    
32.        ARD.taxable_accounted_dr     TAXABLE_ACCOUNTED_DR,
    
33.        ARD.taxable_accounted_cr     TAXABLE_ACCOUNTED_CR,
    
34.        ra.application_type,
    
35.        ra.status,
    
36.        RA.gl_date,
    
37.        RA.posting_control_id,
    
38.        RA.posting_control_id,
    
39.        RA.applied_customer_trx_id,
    
40.        RA.applied_customer_trx_id,
    
41.        RA.apply_date                APPLICATION_DATE,
    
42.        RA.applied_customer_trx_id   APPLIED_TO_TRX_HDR_ID
    
43. 
    
44.   From ar_distributions_all ARD, ar_receivable_applications_all RA
    
45. where ARD.source_id = RA.receivable_application_id
    
46.    AND nvl(ARD.org_id, -99) = nvl(RA.org_id, -99)
    
47.    AND ARD.source_table = 'RA'
    
48.    AND RA.application_type = 'CASH'
    
49.    AND nvl(RA.postable, 'Y') = 'Y'
    

复制代码

ora-12052:cannot fast refresh materialized view APPS.MV_ARD_RA
我在底下做实验的时候参考官档

1. CREATE MATERIALIZED VIEW LOG ON times
   
2.    WITH ROWID, SEQUENCE (time_id, calendar_year)
   
3.    INCLUDING NEW VALUES;
   
4. 
   
5. CREATE MATERIALIZED VIEW LOG ON products
   
6.    WITH ROWID, SEQUENCE (prod_id)
   
7.    INCLUDING NEW VALUES;
   
8. 
   
9. CREATE MATERIALIZED VIEW sales_mv
   
10.    BUILD IMMEDIATE
    
11.    REFRESH FAST ON COMMIT
    
12.    AS SELECT t.calendar_year, p.prod_id,
    
13.       SUM(s.amount_sold) AS sum_sales
    
14.       FROM times t, products p, sales s
    
15.       WHERE t.time_id = s.time_id AND p.prod_id = s.prod_id
    
16.       GROUP BY t.calendar_year, p.prod_id;
    

复制代码

我应该根据那几列使用group by 来让该视图能快速刷新（快速刷新我理解应该是有group by吧，不知道对不对）
望大师们指点一二

张沛

在上述基础上添加了group by

1.    group by ARD.source_id,ARD.org_id,RA.application_type,RA.postable

复制代码

报错ora-00979:not a group by expression