0

I am trying to create a materialized view as below and getting error, kindly advise.

Oracle version: 12.2.0.1.0

CREATE MATERIALIZED VIEW EDW_TEST_MV
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS
(
SELECT
distinct TXN_REF,
ACQ_ENT_KEY, 
ACQ_SR_BNK_KEY, 
ISS_ENT_KEY,    
ISS_SR_BNK_KEY,     
ACQ_INST_ID,        
ISS_INST_ID,   
INTMNT_MRCHNT_KEY,
TXN_MD_KEY ,           
TXN_TP_KEY, 
TXN_TRMSN_DT ,              
TXN_LCL_DT ,           
TXN_TIMEOUT,             
SRC_ACQ_MEM  ,        
TXN_AUTH_CD, 
JV_ERROR_CD,
DST_DENIAL_CD,
DST_ISS_MEM,          
STTS,
ACQ_NODE_ID ,         
ISS_NODE_ID ,
RRN,
URN,
GOV_ORGSC_URN,
TXN_AMT,
SRC_SYS_KEY
FROM EDW_TEST_F)
group by 
ACQ_ENT_KEY, 
ACQ_SR_BNK_KEY, 
ISS_ENT_KEY,    
ISS_SR_BNK_KEY,     
ACQ_INST_ID,        
ISS_INST_ID,   
INTMNT_MRCHNT_KEY,
TXN_MD_KEY ,           
TXN_TP_KEY, 
TXN_TRMSN_DT ,              
TXN_LCL_DT ,           
TXN_TIMEOUT,             
SRC_ACQ_MEM  ,        
TXN_AUTH_CD, 
JV_ERROR_CD,
DST_DENIAL_CD,
DST_ISS_MEM,          
STTS,
ACQ_NODE_ID ,         
ISS_NODE_ID,            
SRC_SYS_KEY
);

group by
*
ERROR at line 34:
ORA-00933: SQL command not properly ended
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
Senthil
  • 11
  • 1
  • 1
    Line: `FROM EDW_TEST_F)` - remove the bracket – Wernfried Domscheit Jan 18 '21 at 09:48
  • If you like to use `FAST REFRESH` with DISTINCT and/or then GROUP you need to do some more, see https://stackoverflow.com/questions/49578932/materialized-view-in-oracle-with-fast-refresh-instead-of-complete-dosnt-work/49579378#49579378 You also have to create `MATERIALIZED VIEW LOGS` on base table – Wernfried Domscheit Jan 18 '21 at 09:50
  • Thanks. I have already created the MV log. Meanwhile I will check for Fast refresh options. If I remove the bracket I get error: FROM EDW_TEST_F * ERROR at line 33: ORA-00979: not a GROUP BY expression – Senthil Jan 18 '21 at 10:02
  • Why `distinct` **and** `group by`? You can remove the `group by` entirely as you are not using any aggregate function –  Jan 18 '21 at 10:12

1 Answers1

0

Try the below. i have removed bracket after from clause.

CREATE MATERIALIZED VIEW EDW_TEST_MV
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS
(
SELECT
distinct TXN_REF,
ACQ_ENT_KEY, 
ACQ_SR_BNK_KEY, 
ISS_ENT_KEY,    
ISS_SR_BNK_KEY,     
ACQ_INST_ID,        
ISS_INST_ID,   
INTMNT_MRCHNT_KEY,
TXN_MD_KEY ,           
TXN_TP_KEY, 
TXN_TRMSN_DT ,              
TXN_LCL_DT ,           
TXN_TIMEOUT,             
SRC_ACQ_MEM  ,        
TXN_AUTH_CD, 
JV_ERROR_CD,
DST_DENIAL_CD,
DST_ISS_MEM,          
STTS,
ACQ_NODE_ID ,         
ISS_NODE_ID ,
RRN,
URN,
GOV_ORGSC_URN,
TXN_AMT,
SRC_SYS_KEY
FROM EDW_TEST_F
group by 
ACQ_ENT_KEY, 
ACQ_SR_BNK_KEY, 
ISS_ENT_KEY,    
ISS_SR_BNK_KEY,     
ACQ_INST_ID,        
ISS_INST_ID,   
INTMNT_MRCHNT_KEY,
TXN_MD_KEY ,           
TXN_TP_KEY, 
TXN_TRMSN_DT ,              
TXN_LCL_DT ,           
TXN_TIMEOUT,             
SRC_ACQ_MEM  ,        
TXN_AUTH_CD, 
JV_ERROR_CD,
DST_DENIAL_CD,
DST_ISS_MEM,          
STTS,
ACQ_NODE_ID ,         
ISS_NODE_ID,            
SRC_SYS_KEY
);
Ankit Mongia
  • 200
  • 2
  • 11
  • I have already mentioned above: removing the bracket gives error: ERROR at line 33: ORA-00979: not a GROUP BY expression – Senthil Jan 18 '21 at 12:45