Home » SQL & PL/SQL » SQL & PL/SQL » MATERIALIZED VIEW on commit (linux, oracle11.2)
| MATERIALIZED VIEW on commit [message #621834] |
Mon, 18 August 2014 22:18  |
zxx2403
Messages: 13
Registered: November 2006
Location: china
|
Junior Member |
|
|
create table T_01
(
grp VARCHAR2(100) not null,
asat DATE not null,
v_01 NUMBER,
v_02 NUMBER,
v_03 NUMBER
);
alter table T_01
add constraint PK_T_01 primary key (GRP, ASAT);
insert into T_01 values ('A',TO_DATE('20130101','YYYYMMDD'),10,8,4);
insert into T_01 values ('A',TO_DATE('20130201','YYYYMMDD'),11,6,6);
insert into T_01 values ('A',TO_DATE('20130301','YYYYMMDD'),14,4,9);
insert into T_01 values ('B',TO_DATE('20130101','YYYYMMDD'),10,8,4);
insert into T_01 values ('B',TO_DATE('20130201','YYYYMMDD'),11,6,6);
insert into T_01 values ('B',TO_DATE('20130301','YYYYMMDD'),14,4,9);
I want to create a MATERIALIZED VIEW
refresh complete on commit;
to return the following result
1 A 2013/3/1 14 4 9
2 B 2013/3/1 14 4 9
like the following sql
select * from
(
select a.grp,a.asat,a.v_01,a.v_02,a.v_03, row_number() over(partition by grp order by asat desc) as rn
from t_01 a
)where rn = 1
but the materialized view dont support the function,
how can i do it. thanks
|
|
|
|
|
|
| Re: MATERIALIZED VIEW on commit [message #621849 is a reply to message #621838] |
Tue, 19 August 2014 02:17  |
zxx2403
Messages: 13
Registered: November 2006
Location: china
|
Junior Member |
|
|
Hi
I mean to create a mv that the result is the seam as the following sql
select * from
(
select a.grp,a.asat,a.v_01,a.v_02,a.v_03, row_number() over(partition by grp order by asat desc) as rn
from t_01 a
)where rn = 1
|
|
|
|
| Re: MATERIALIZED VIEW on commit [message #621852 is a reply to message #621849] |
Tue, 19 August 2014 02:32 |
 |
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
I mean "what do you expect from this mview".
Explain the need and usage of it.
Anyway, you cannot do it with row_number, try to rewrite it without this (using MIN subquery) but I'm not sure it is allowed or not.
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 21:32:11 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
