Home » SQL & PL/SQL » SQL & PL/SQL » Error in creating Materialized view (Oracle SQL Developer(3.1.07) , Windows 7)
| Error in creating Materialized view [message #567404] |
Mon, 01 October 2012 02:28  |
 |
radhuu
Messages: 40
Registered: August 2012
Location: India,Chennai
|
Member |
|
|
Hi all,
Am trying to create a materialized view as below
SQL> CREATE MATERIALIZED VIEW emp_view_73 BUILD IMMEDIATE REFRESH COMPLETE ON COMMIT
2 AS
3 SELECT em.employee_id,
4 em.employee_fname
5 ||' '
6 ||em.employee_lname employee_name,
7 em.date_of_joining,
8 tab.dept_id,
9 d.dept_name,
10 tab.designation,
11 tab.salary
12 FROM emp_master em,
13 department_masters d,
14 (SELECT ed.dept_id,
15 ed.designation,
16 MAX(em.date_of_joining) date_of_joining,
17 MAX(ed.salary) salary
18 FROM emp_details2 ed,
19 emp_master em
20 WHERE em.employee_id=ed.employee_id
21 GROUP BY ed.dept_id,
22 ed.designation
23 HAVING SUBSTR(MAX(em.date_of_joining),8)='12'
24 ) tab
25 WHERE em.date_of_joining=tab.date_of_joining
26 AND d.dept_id =tab.dept_id;
it gives the error as
FROM emp_master em,
*
ERROR at line 12:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
Thanks in advance.
Regards,
Radhu.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Error in creating Materialized view [message #567431 is a reply to message #567426] |
Mon, 01 October 2012 05:18  |
|
radhuu
Messages: 40
Registered: August 2012
Location: India,Chennai
|
Member |
|
|
Hi MIchel,
What i actually need is to create materilalized view to find an employee who joined recently and got more salary than others who joined before in the same deptartment and in same designation(role).
I have two tables one is emp_master and other one emp_details2.
SQL> desc emp_master;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER
EMPLOYEE_FNAME VARCHAR2(20)
EMPLOYEE_LNAME VARCHAR2(20)
GENDER VARCHAR2(2)
DATE_OF_BIRTH DATE
DATE_OF_JOINING DATE
OFFICIAL_MAIL_ID VARCHAR2(30)
PERSONAL_MAIL_ID VARCHAR2(30)
BLOOD_GROUP VARCHAR2(10)
MOBILE_NO NUMBER(18)
CITY VARCHAR2(15)
SQL> desc emp_details2;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER
START_DATE NOT NULL DATE
END_DATE DATE
DESIGNATION VARCHAR2(20)
DEPT_ID NUMBER
MANAGER_ID NUMBER
PROJECT_ID VARCHAR2(15)
EMPLOYEE_ADRESS VARCHAR2(200)
SALARY NUMBER
For this i write the below query but i didn't get correct result
CREATE MATERIALIZED VIEW emp_view_73 BUILD IMMEDIATE REFRESH COMPLETE ON COMMIT
AS
SELECT em.employee_id,em.employee_fname
||' '
||em.employee_lname employee_name,
em.date_of_joining,
tab.dept_id,
d.dept_name,
tab.designation,
tab.salary
FROM emp_master em,
department_masters d,
(SELECT ed.dept_id,
ed.designation,
MAX(em.date_of_joining) date_of_joining,
MAX(ed.salary) salary
FROM emp_details2 ed,
emp_master em
WHERE em.employee_id=ed.employee_id
GROUP BY ed.dept_id,
ed.designation
HAVING SUBSTR(MAX(em.date_of_joining),8)='12'
) tab
WHERE em.date_of_joining=tab.date_of_joining
and d.dept_id=tab.dept_id
This gives duplicate rows if two employees are joined in same date.
Regards
Radhu.
[Updated on: Mon, 01 October 2012 05:21] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Error in creating Materialized view [message #567444 is a reply to message #567435] |
Mon, 01 October 2012 06:15 |
|
radhuu
Messages: 40
Registered: August 2012
Location: India,Chennai
|
Member |
|
|
Hi Michel,
The test case of 2 tables is given.
CREATE TABLE emp_master
(
EMPLOYEE_ID NUMBER,
EMPLOYEE_FNAME VARCHAR2(20),
EMPLOYEE_LNAME VARCHAR2(20),
GENDER VARCHAR2(2),
DATE_OF_BIRTH DATE,
DATE_OF_JOINING DATE,
OFFICIAL_MAIL_ID VARCHAR2(30),
PERSONAL_MAIL_ID VARCHAR2(30),
BLOOD_GROUP VARCHAR2(10),
MOBILE_NO NUMBER(18),
CITY VARCHAR2(15)
)
Insert into EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) values (119,'SABARI','G','M',to_date('06-SEP-85','DD-MON-RR'),
to_date('14-APR-07','DD-MON-RR'),'SABARI@xyz.COM','SABARI@GAMIL.COM','O+VE',9988772133,'Chennai');
Insert into EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) values (216,'AMALA','J','F',to_date('19-JAN-81','DD-MON-RR'),
to_date('11-SEP-04','DD-MON-RR'),'AMALA@xyz.COM','AMALA@YAHOO.COM','AB+VE',9900234383,'Chennai');
Insert into EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) values (222,'PRAKESH','D','M',to_date('22-AUG-80','DD-MON-RR'),
to_date('13-SEP-02','DD-MON-RR'),'PRAKESH@xyz.COM','PRAKESH@GAMIL.COM','A+VE',9894012376,'Chennai');
Insert into EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) values (234,'VIRAT','K','M',to_date('12-JAN-87','DD-MON-RR'),
to_date('12-JAN-12','DD-MON-RR'),'VIRAT,K@xyz.COM','VIRATK9@GMAIL.COM','AB-VE',8939890540,'Chennai');
Insert into EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) values (336,'KAVIYA','S','F',to_date('22-JUL-80','DD-MON-RR'),
to_date('31-AUG-02','DD-MON-RR'),'KAVIYA@xyz.COM','KAVIYA@GAMIL.COM','AB+VE',9966012134,'Chennai');
Insert into EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) values (422,'SARATHI','K','M',to_date('08-JUL-80','DD-MON-RR'),
to_date('31-AUG-02','DD-MON-RR'),'SARATHI@xyz.COM','SARATHI@GAMIL.COM','B+VE',9001213426,'Chennai');
Insert into EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) values (431,'SANTHOSH','S','M',to_date('17-FEB-78','DD-MON-RR'),
to_date('21-JAN-00','DD-MON-RR'),'SANTHOSH@xyz.COM','SANTHOSH@GAMIL.COM','B+VE',8131245611,'Chennai');
Insert into EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) values (444,'KRISHNAN','R','M',to_date('07-OCT-85','DD-MON-RR'),
to_date('14-APR-07','DD-MON-RR'),'KRISHNAN@xyz.COM','KRISHNAN@GAMIL.COM','O-VE',8767123451,'Chennai');
Insert into EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) values (511,'MANI','V','M',to_date('30-MAR-85','DD-MON-RR'),
to_date('24-APR-07','DD-MON-RR'),'MANI@xyz.COM','MANI@GAMIL.COM','AB+VE',9787156641,'Chennai');
Insert into EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) values (534,'PABBIN','S S','M',to_date('22-NOV-89','DD-MON-RR'),
to_date('28-JAN-12','DD-MON-RR'),'PABBIN@xyz.COM','PABBIN@GAMIL.COM','B+VE',9178314423,'Chennai');
Insert into EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) values (566,'IRFAN','D','M',to_date('04-DEC-88','DD-MON-RR'),
to_date('16-JAN-10','DD-MON-RR'),'IRFAN@xyz.COM','IRFAN@GAMIL.COM','AB+VE',8873242411,'Chennai');
Insert into EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) values (568,'KARTHIK','N','M',to_date('21-JAN-85','DD-MON-RR'),
to_date('19-FEB-07','DD-MON-RR'),'KARTHIK@xyz.COM','KARTHIK@GAMIL.COM','O+VE',9181713242,'Chennai');
Insert into EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) values (576,'SUGANTHI','S','F',to_date('14-OCT-89','DD-MON-RR'),
to_date('27-JAN-12','DD-MON-RR'),'SUGANTHI@xyz.COM','SUGANTHI@GAMIL.COM','O+VE',8797685717,'Chennai');
Insert into EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) values (588,'SARANYA','R','F',to_date('12-MAR-88','DD-MON-RR'),
to_date('22-DEC-10','DD-MON-RR'),'SARANYA@xyz.COM','SARANYA@GAMIL.COM','O+VE',9966653242,'Chennai');
Insert into EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) values (611,'RANJITH','A','M',to_date('22-JUN-79','DD-MON-RR'),
to_date('03-AUG-01','DD-MON-RR'),'RANJITH@xyz.COM','RANJITH@GAMIL.COM','O+VE',9787123456,'Chennai');
Insert into EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) values (666,'MUTHU','R','M',to_date('02-MAR-84','DD-MON-RR'),
to_date('02-JUN-06','DD-MON-RR'),'MUTHU@xyz.COM','MUTHU@GAMIL.COM','B+VE',9992141516,'Chennai');
Insert into EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) values (701,'JOEYEE','B','M',to_date('15-AUG-77','DD-MON-RR'),
to_date('30-APR-99','DD-MON-RR'),'JOE@xyz.COM','JOE@GMAIL.COM','AB+VE',7687981921,'Bangloreu');
Insert into EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) values (708,'BANU','G','F',to_date('14-NOV-85','DD-MON-RR'),
to_date('31-DEC-07','DD-MON-RR'),'BANU@xyz.COM','BANU@GMAIL.COM','O-VE',8393041425,'Bangloreu');
Insert into EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) values (777,'ANUSHA','K','F',to_date('14-FEB-89','DD-MON-RR'),
to_date('22-DEC-11','DD-MON-RR'),'ANUSHA@xyz.COM','ANUSHA@GAMIL.COM','B+VE',8877866431,'Chennai');
Insert into EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) values (822,'JASMEET','C','M',to_date('17-JAN-83','DD-MON-RR'),
to_date('14-FEB-05','DD-MON-RR'),'JASMEET@xyz.COM','JASMEET@GMAIL.COM','B+VE',9273663454,'Pune');
Insert into EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) values (836,'ANWAR','H','M',to_date('29-FEB-84','DD-MON-RR'),
to_date('01-MAR-04','DD-MON-RR'),'ANWAR@xyz.COM','ANWAR@GMAIL.COM','B+VE',7723568912,'Hydrabad');
Insert into EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) values (891,'ACHINTYA','P','M',to_date('18-NOV-80','DD-MON-RR'),
to_date('11-JAN-02','DD-MON-RR'),'ACHINTYA@xyz.COM','ACHINTYA@GMAIL.COM','AB-VE',9282113473,'Pune');
Insert into EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) values (901,'VALAMBURI','S','M',to_date('15-MAY-89','DD-MON-RR'),
to_date('23-APR-12','DD-MON-RR'),'VALAMBURI@xyz.COM ','VALAMBURI@GMAIL.COM','B+VE',9787161038,'Hydrabad');
Insert into EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) values (927,'SURENDRA','R','M',to_date('21-MAR-87','DD-MON-RR'),
to_date('31-MAY-09','DD-MON-RR'),'SURI@xyz.COM','SURI@GMAIL.COM','0+VE',8871915674,'Hydrabad');
Insert into EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) values (966,'SELVAM','D','M',to_date('12-JUL-87','DD-MON-RR'),
to_date('12-FEB-12','DD-MON-RR'),'SELVAM@xyz.COM','SELVA@GMAIL.COM','O+VE',7788234212,'Hydrabad');
Insert into EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) values (969,'YACOOB','M','M',to_date('30-APR-86','DD-MON-RR'),
to_date('13-MAR-12','DD-MON-RR'),'YAC@xyz.COM','YAC@GMAIL.COM','B+VE',9889457427,'Bangloreu');
Insert into EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) values (973,'VENKAT','R','M',to_date('21-MAY-87','DD-MON-RR'),
to_date('14-FEB-12','DD-MON-RR'),'VENKAT@xyz.COM','VANKAT@GMAIL.COM','AB+VE',9442957121,'HYDRABAD');
INSERT INTO EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) VALUES (981,'SHARMA','B','M',to_date('13-MAY-87','DD-MON-RR'),
to_date('22-APR-12','DD-MON-RR'),'SHARMA2@xyz.COM','SHARMA@GMAIL.COM','A+',7821438910,'Pune');
INSERT INTO EMP_MASTER (EMPLOYEE_ID,EMPLOYEE_FNAME,EMPLOYEE_LNAME,GENDER,DATE_OF_BIRTH,DATE_OF_JOINING,
OFFICIAL_MAIL_ID,
PERSONAL_MAIL_ID,BLOOD_GROUP,MOBILE_NO,CITY) VALUES (993,'ANUSHA','T','F',to_date('10-NOV-86','DD-MON-RR'),
to_date('04-AUG-12','DD-MON-RR'),'ANUSHA@xyz.COM','ANUSHA@GMAIL.COM','B+VE',7887346512,'Hydrabad');
CREATE TABLE emp_details2
(
EMPLOYEE_ID NUMBER,
START_DATE DATE,
END_DATE DATE,
DESIGNATION VARCHAR2(20),
DEPT_ID NUMBER,
MANAGER_ID NUMBER,
PROJECT_ID VARCHAR2(15),
EMPLOYEE_ADRESS VARCHAR2(200),
SALARY NUMBER
)
Insert into EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) values (119,to_date('18-APR-10',
'DD-MON-RR'),null,'ASSOCIATE',26,336,'58C','56, cross street, Tripattur',25000);
Insert into EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) values (216,to_date('24-AUG-06',
'DD-MON-RR'),null,'MANAGER',36,431,'51C','32g,second st,Adayar,Chennai',35000);
Insert into EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) values (222,to_date('23-SEP-06',
'DD-MON-RR'),null,'MANAGER',42,431,'56C','32,Sanakaran koil st, kanchipuram',35000);
Insert into EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) values (234,to_date('12-JAN-12',
'DD-MON-RR'),null,'ASSOCIATE',11,336,'58C','12/3,Bhramin street,Saidapet-15',27000);
Insert into EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) values (336,to_date('06-SEP-07',
'DD-MON-RR'),null,'MANAGER',26,431,'58C','32/7,Vallabai patel st,Madurai',35000);
Insert into EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) values (422,to_date('07-SEP-07',
'DD-MON-RR'),null,'MANAGER',36,431,'54C','21a,Kalpakkam,Chennai',35000);
Insert into EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) values (431,to_date('10-JAN-10',
'DD-MON-RR'),null,'SR.MANAGER',22,701,'51C','3b,cross st,Kudiyatham',42000);
Insert into EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) values (444,to_date('02-SEP-12',
'DD-MON-RR'),null,'SR.ASSOCIATE',36,336,'52C','17/9,koil st,Saidapet,Chennai',30000);
Insert into EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) values (511,to_date('29-APR-10',
'DD-MON-RR'),null,'ASSOCIATE',11,611,'52C','21l,Lasturibai st, Porur',25000);
Insert into EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) values (534,to_date('28-JAN-12',
'DD-MON-RR'),null,'PAT',11,611,'52C','211, gurukulam st, Tiruthani',18000);
Insert into EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) values (566,to_date('18-JAN-11',
'DD-MON-RR'),null,'PA',22,422,'54C','31/4,frist st,Risivanthiyam',21000);
Insert into EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) values (568,to_date('23-FEB-10',
'DD-MON-RR'),null,'ASSOCIATE',26,336,'58C','27/1,Gandhi nagar,Vellore',25000);
Insert into EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) values (576,to_date('29-MAY-12',
'DD-MON-RR'),null,'PAT',42,222,'56C','22/8,Anna nagar,Chennai',18000);
Insert into EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) values (588,to_date('23-DEC-11',
'DD-MON-RR'),null,'PA',42,222,'56C','22,Agragaram st,Perambular',21000);
Insert into EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) values (611,to_date('10-AUG-06',
'DD-MON-RR'),null,'MANAGER',11,431,'52C','112, sankaran st,Tripattur',35000);
Insert into EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) values (666,to_date('23-DEC-09',
'DD-MON-RR'),null,'ASSOCIATE',22,216,'51C','9a/3,Mattuthavani,Madurai.',25000);
Insert into EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) values (701,to_date('08-JUL-08',
'DD-MON-RR'),null,'SR.MANAGER',22,null,'58B','13A,PARK ST,BANGLORE',42000);
Insert into EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) values (708,to_date('02-JUN-11',
'DD-MON-RR'),null,'ASSOCIATE',22,701,'56B','2A/12,CDX, BANGLORE',25000);
Insert into EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) values (777,to_date('22-DEC-11',
'DD-MON-RR'),null,'PAT',36,422,'54C','3/12,mambalam,Chennai',18000);
Insert into EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) values (822,to_date('02-JAN-12',
'DD-MON-RR'),null,'MANAGER',42,891,'51P','56B,RRK,PUNE22',35000);
Insert into EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) values (836,to_date('01-FEB-10',
'DD-MON-RR'),null,'MANAGER',11,891,'58H','41A,ZYX,HYDRABAD-40',35000);
Insert into EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) values (891,to_date('23-APR-11',
'DD-MON-RR'),null,'SR.MANAGER',22,431,'52P','62/2A,TTH,PUNE-21',42000);
Insert into EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) values (901,to_date('23-APR-12',
'DD-MON-RR'),null,'PAT',11,836,'58H','113,sethur village,Hydrabad-31',18000);
Insert into EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) values (927,to_date('12-DEC-11',
'DD-MON-RR'),null,'PA',22,836,'54H','43G,BXA,HYDRABAD-67',21000);
Insert into EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) values (966,to_date('12-FEB-10',
'DD-MON-RR'),null,'ASSOCIATE',26,336,'58H','11A,3rd street,HYDRABAD-12',27300);
Insert into EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) values (969,to_date('13-MAR-12',
'DD-MON-RR'),null,'ASSOCIATE',22,221,'58B','FIRST CROSS STREET,BANGLOREU',27210);
Insert into EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) values (973,to_date('14-FEB-12',
'DD-MON-RR'),null,'ASSOCIATE',11,836,'58H','4G,836,XEXIS BLOCK,HYDRABAD-23',27110);
INSERT INTO EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) VALUES (981,to_date('22-APR-12',
'DD-MON-RR'),NULL,'PA',42,891,'51P','32S,BIOO COLONY,PUNE-31',23101);
Insert into EMP_DETAILS2(EMPLOYEE_ID,START_DATE,END_DATE,DESIGNATION,DEPT_ID,MANAGER_ID,PROJECT_ID,
EMPLOYEE_ADRESS,SALARY) values (993,to_date('04-AUG-12',
'DD-MON-RR'),null,'PA',36,836,'54H','234,VANGAYA STREET,HYDRABAD-12',23201);
Regards
Radhu.
[Updated on: Wed, 03 October 2012 04:15] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Error in creating Materialized view [message #567531 is a reply to message #567526] |
Wed, 03 October 2012 02:40 |
|
radhuu
Messages: 40
Registered: August 2012
Location: India,Chennai
|
Member |
|
|
Michel,
I created a view as you said as below
CREATE VIEW emp_curr_view
AS
SELECT em.employee_id,
em.employee_fname
||' '
||em.employee_lname employee_name,
em.date_of_joining,
tab.dept_id,
d.dept_name,
tab.designation,
tab.salary
FROM emp_master em,
department_masters d,
(SELECT ed.dept_id,
ed.designation,
MAX(em.date_of_joining) date_of_joining,
MAX(ed.salary) salary
FROM emp_details2 ed,
emp_master em
WHERE em.employee_id=ed.employee_id
GROUP BY ed.dept_id,
ed.designation
HAVING SUBSTR(MAX(em.date_of_joining),8)='12'
) tab
WHERE em.date_of_joining=tab.date_of_joining
AND d.dept_id =tab.dept_id
but if 2 employees are joined in a same day their details are getting cross join.
Regards
Radhu
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Error in creating Materialized view [message #567565 is a reply to message #567556] |
Wed, 03 October 2012 06:10 |
John Watson
Messages: 3102
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Radhu, you now need to explain "newly".
But the problem seems fairly simple, and nothing to do with a materialized view. It is a correlated sub-query. Working the SCOTT demo schema, this does what you want:
orcl> select ename,deptno from emp e where hiredate > to_date('01-10-1981','dd-mm-yyyy')
2 and sal > (select max(sal) from emp f
3 where hiredate <= to_date('01-10-1981','dd-mm-yyyy') and e.deptno=f.deptno);
ENAME DEPTNO
---------- ----------
SCOTT 20
KING 10
FORD 20
orcl>
|
|
|
|
|
|
| Re: Error in creating Materialized view [message #567570 is a reply to message #567569] |
Wed, 03 October 2012 06:36 |
John Watson
Messages: 3102
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
OK,orcl> select * from emp order by deptno,hiredate;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-06-81 00:00:00 2450 10
7839 KING PRESIDENT 17-11-81 00:00:00 5000 10
7934 MILLER CLERK 7782 23-01-82 00:00:00 1300 10
7369 SMITH CLERK 7902 17-12-80 00:00:00 800 20
7566 JONES MANAGER 7839 02-04-81 00:00:00 2975 20
7902 FORD ANALYST 7566 03-12-81 00:00:00 3000 20
7788 SCOTT ANALYST 7566 19-04-87 00:00:00 3000 20
7876 ADAMS CLERK 7788 23-05-87 00:00:00 10000 20
7499 ALLEN SALESMAN 7698 20-02-81 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 22-02-81 00:00:00 1250 500 30
7698 BLAKE MANAGER 7839 01-05-81 00:00:00 2850 30
7844 TURNER SALESMAN 7698 08-09-81 00:00:00 1500 0 30
7654 MARTIN SALESMAN 7698 28-09-81 00:00:00 1250 1400 30
7900 JAMES CLERK 7698 03-12-81 00:00:00 950 30
14 rows selected.
orcl> select ename,deptno,sal from emp e
2 where hiredate = (select max(hiredate) from emp f where e.deptno=f.deptno)
3 and sal >= all (select sal from emp f where e.deptno=f.deptno)
4
orcl>
orcl> /
ENAME DEPTNO SAL
---------- ---------- ----------
ADAMS 20 10000
orcl> |
|
|
|
| Re: Error in creating Materialized view [message #567572 is a reply to message #567569] |
Wed, 03 October 2012 06:54 |
 |
Michel Cadot
Messages: 54127
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Assuming you want only the employees that are still in the department and designation (that is with a null end_date):
SQL> col nop noprint
SQL> with
2 latest_dates as (
3 select dept_id, designation, max(start_date) start_date
4 from emp_details2
5 where end_date is null
6 group by dept_id, designation
7 ),
8 latest_employees as (
9 select e.employee_id, e.dept_id, e.designation, e.salary
10 from emp_details2 e, latest_dates d
11 where e.dept_id = d.dept_id
12 and e.designation = d.designation
13 and e.start_date = d.start_date
14 and e.end_date is null
15 and exists (select null from emp_details2 e2
16 where e2.dept_id = e.dept_id
17 and e2.designation = e.designation
18 and e2.end_date is null
19 and e2.salary < e.salary)
20 )
21 select l.employee_id nop, 1 nop, l.employee_id,
22 cast(m.employee_fname||' '||m.employee_lname as varchar2(13)) employee_name,
23 m.date_of_joining, l.dept_id, l.designation, l.salary
24 from latest_employees l, emp_master m
25 where m.employee_id = l.employee_id
26 union all
27 select l.employee_id nop, 2 nop, d.employee_id,
28 cast(' '||m.employee_fname||' '||m.employee_lname as varchar2(13))
29 employee_name,
30 m.date_of_joining, d.dept_id, d.designation, d.salary
31 from latest_employees l, emp_details2 d, emp_master m
32 where d.dept_id = l.dept_id
33 and d.designation = l.designation
34 and d.end_date is null
35 and d.salary < l.salary
36 and m.employee_id = d.employee_id
37 order by 1, 2, 3
38 /
EMPLOYEE_ID EMPLOYEE_NAME DATE_OF_JOI DEPT_ID DESIGNATION SALARY
----------- ------------- ----------- ---------- -------------------- ----------
969 YACOOB M 13-MAR-2012 22 ASSOCIATE 27210
666 MUTHU R 02-JUN-2006 22 ASSOCIATE 25000
708 BANU G 31-DEC-2007 22 ASSOCIATE 25000
973 VENKAT R 14-FEB-2012 11 ASSOCIATE 27110
234 VIRAT K 12-JAN-2012 11 ASSOCIATE 27000
511 MANI V 24-APR-2007 11 ASSOCIATE 25000
981 SHARMA B 22-APR-2012 42 PA 23101
588 SARANYA R 22-DEC-2010 42 PA 21000
8 rows selected.
Regards
Michel
[Updated on: Wed, 03 October 2012 06:58] Report message to a moderator |
|
|
|
| Re: Error in creating Materialized view [message #567575 is a reply to message #567570] |
Wed, 03 October 2012 06:59 |
|
radhuu
Messages: 40
Registered: August 2012
Location: India,Chennai
|
Member |
|
|
Hi Watson,
I tried as you said and got no rows selected.
SQL> SELECT e.employee_id,
2 e.dept_id,
3 e.salary
4 FROM emp_details2 e
5 WHERE e.start_date=
6 (SELECT MAX(f.start_date) FROM emp_details2 f WHERE e.dept_id=f.dept_id
7 )
8 AND e.salary>=ALL
9 (SELECT f.salary FROM emp_details2 f WHERE e.dept_id=f.dept_id
10 );
no rows selected
this is my table structure and details
SQL> select * from emp_details2 order by dept_id,start_date;
EMPLOYEE_ID START_DAT END_DATE DESIGNATION DEPT_ID MANAGER_ID PROJECT_ID EMPLOYEE_ADRESS
----------- --------- --------- -------------------- ---------- ---------- --------------- -------------------------------------------
611 10-AUG-06 MANAGER 11 431 52C 112, sankaran st,Tripattur
836 01-FEB-10 MANAGER 11 891 58H 41A,ZYX,HYDRABAD-40
511 29-APR-10 ASSOCIATE 11 611 52C 21l,Lasturibai st, Porur
234 12-JAN-12 ASSOCIATE 11 336 58C 12/3,Bhramin street,Saidapet-15
534 27-JAN-12 PAT 11 611 52C 211, gurukulam st, Tiruthani
973 14-FEB-12 ASSOCIATE 11 836 58H 4G,836,XEXIS BLOCK,HYDRABAD-23
901 23-APR-12 PAT 11 836 58H 113,sethur village,Hydrabad-31
701 08-JUL-08 SR.MANAGER 22 58B 13A,PARK ST,BANGLORE
666 23-DEC-09 ASSOCIATE 22 216 51C 9a/3,Mattuthavani,Madurai.
431 10-JAN-10 SR.MANAGER 22 701 51C 3b,cross st,Kudiyatham
566 18-JAN-11 PA 22 422 54C 31/4,frist st,Risivanthiyam
891 23-APR-11 SR.MANAGER 22 431 52P 62/2A,TTH,PUNE-21
708 02-JUN-11 ASSOCIATE 22 701 56B 2A/12,CDX, BANGLORE
927 12-DEC-11 PA 22 836 54H 43G,BXA,HYDRABAD-67
969 13-MAR-12 ASSOCIATE 22 221 58B FIRST CROSS STREET,BANGLOREU
336 06-SEP-07 MANAGER 26 431 58C 32/7,Vallabai patel st,Madurai
966 12-FEB-10 ASSOCIATE 26 336 58H 11A,3rd street,HYDRABAD-12
568 23-FEB-10 ASSOCIATE 26 336 58C 27/1,Gandhi nagar,Vellore
119 18-APR-10 ASSOCIATE 26 336 58C 56, cross street, Tripattur
216 24-AUG-06 MANAGER 36 431 51C 32g,second st,Adayar,Chennai
422 07-SEP-07 MANAGER 36 431 54C 21a,Kalpakkam,Chennai
777 22-DEC-11 PAT 36 422 54C 3/12,mambalam,Chennai
993 04-AUG-12 PA 36 836 54H 234,VANGAYA STREET,HYDRABAD-12
444 02-SEP-12 SR.ASSOCIATE 36 336 52C 17/9,koil st,Saidapet,Chennai
222 23-SEP-06 MANAGER 42 431 56C 32,Sanakaran koil st, kanchipuram
588 23-DEC-11 PA 42 222 56C 22,Agragaram st,Perambular
822 02-JAN-12 MANAGER 42 891 51P 56B,RRK,PUNE22
981 22-APR-12 PA 42 891 51P 32S,BIOO COLONY,PUNE-31
576 29-MAY-12 PAT 42 222 56C 22/8,Anna nagar,Chennai
Help me to get the result
Regards
Radhu
|
|
|
|
|
|
|
|
| Re: Error in creating Materialized view [message #567583 is a reply to message #567581] |
Wed, 03 October 2012 07:30 |
|
Michel Cadot
Messages: 54127
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Explain each line of your result and explain why they are in the result.
If in my query the only thing you don't like is that you have people from any year then restrict the result to those that have joined in the current year, no big deal to add a condition in the WHERE clause(s).
Regards
Michel
[Updated on: Wed, 03 October 2012 07:32] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Error in creating Materialized view [message #567594 is a reply to message #567590] |
Wed, 03 October 2012 07:54 |
cookiemonster
Messages: 9135
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
So you want the employee with the highest salary per combination of dept_id and designation as long as that employee joined this year?
What if the dept_id/designation combo has no employees who joined this year?
What if the dept_id/designation combo has an employee(s) who joined this year but the employee with the max salary joined in a previous year?
|
|
|
|
|
|
| Re: Error in creating Materialized view [message #567692 is a reply to message #567614] |
Thu, 04 October 2012 06:47 |
|
radhuu
Messages: 40
Registered: August 2012
Location: India,Chennai
|
Member |
|
|
Hi all,
Finally i got the result. I posted the query below.
Thank you all very much .
SQL> WITH latest_joinees AS
2 (
3 SELECT DISTINCT ed.dept_id,
4 ed.designation,
5 MAX(em.date_of_joining) joined_date,
6 MAX(ed.salary) salary
7 FROM emp_details2 ed,
8 emp_master em
9 WHERE em.employee_id=ed.employee_id
10 and ed.designation !='PAT'
11 GROUP BY ed.dept_id,
12 ed.designation
13 HAVING SUBSTR(MAX(em.date_of_joining),8)='12'
14 ),
15 latest_emp AS
16 (
17 SELECT e.employee_id,
18 e.start_date,
19 e.dept_id,
20 e.designation,
21 e.salary
22 FROM emp_details2 e,
23 latest_joinees d
24 WHERE e.dept_id = d.dept_id
25 AND e.designation = d.designation
26 AND e.start_date = d.joined_date
27 AND e.salary=d.salary
28 )
29 SELECT
30 l.employee_id,
31 CAST(m.employee_fname
32 ||' '
33 ||m.employee_lname AS VARCHAR2(13)) employee_name,
34 m.date_of_joining,
35 l.dept_id,
36 l.designation,
37 l.salary
38 FROM latest_emp l,
39 emp_master m
40 WHERE m.employee_id = l.employee_id
41 UNION ALL
42 SELECT
43 d.employee_id,
44 CAST(' '
45 ||m.employee_fname
46 ||' '
47 ||m.employee_lname AS VARCHAR2(13)) employee_name,
48 m.date_of_joining,
49 d.dept_id,
50 d.designation,
51 d.salary
52 FROM latest_emp l,
53 emp_details2 d,
54 emp_master m
55 WHERE d.dept_id = l.dept_id
56 AND d.designation = l.designation
57 AND d.start_date=l.start_date
58 AND d.end_date IS NULL
59 AND d.salary != l.salary
60 AND m.employee_id = d.employee_id
61 ORDER BY 4;
EMPLOYEE_ID EMPLOYEE_NAME DATE_OF_J DEPT_ID DESIGNATION SALARY
----------- ------------- --------- ---------- -------------------- ----------
973 VENKAT R 14-FEB-12 11 ASSOCIATE 27110
969 YACOOB M 13-MAR-12 22 ASSOCIATE 27210
966 SELVAM D 12-FEB-12 26 ASSOCIATE 27350
993 ANUSHA T 04-AUG-12 36 PA 23201
981 SHARMA B 22-APR-12 42 PA 23101
Regards
Radhu
|
|
|
|
|
|
|
|
| Re: Error in creating Materialized view [message #567707 is a reply to message #567700] |
Thu, 04 October 2012 07:56 |
|
radhuu
Messages: 40
Registered: August 2012
Location: India,Chennai
|
Member |
|
|
Cookiemonster,
Quote:
If you change your default date format that'll break.
I suggest you use:
HAVING MAX(em.date_of_joining) > trunc(sysdate, 'YYYY')
I think its better to use greater than or equal to operator in having clause to also include the employees who are joined in '01-jan-2012',because they are also come under the conditions (employees who are joined in current year).
HAVING MAX(em.date_of_joining) >= trunc(sysdate, 'YYYY')
Regards
Radhu
[Updated on: Thu, 04 October 2012 08:02] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Sun May 19 04:54:40 CDT 2013
Total time taken to generate the page: 0.10110 seconds
|
] 
Members
Search
Help
Register
Login
Home
