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 Go to next message
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 #567405 is a reply to message #567404] Mon, 01 October 2012 02:31 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
 *Cause:  The materialized view did not satisfy conditions for refresh at
          commit time.
 *Action: Specify only valid options.

Check the documentation to know what are the restrictions.

Regards
Michel
Re: Error in creating Materialized view [message #567421 is a reply to message #567405] Mon, 01 October 2012 03:47 Go to previous messageGo to next message
radhuu
Messages: 40
Registered: August 2012
Location: India,Chennai
Member
Hi Michel

Sorry i didnt get. Which document i want to check.

Regards
Radhu
Re: Error in creating Materialized view [message #567423 is a reply to message #567421] Mon, 01 October 2012 03:53 Go to previous messageGo to next message
Littlefoot
Messages: 19476
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Create materialized view documentation.
Re: Error in creating Materialized view [message #567424 is a reply to message #567423] Mon, 01 October 2012 04:05 Go to previous messageGo to next message
radhuu
Messages: 40
Registered: August 2012
Location: India,Chennai
Member
Thanks Littlefoot.

Regards
Radhu
Re: Error in creating Materialized view [message #567426 is a reply to message #567424] Mon, 01 October 2012 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The restrictions depend on your Oracle version you should post and didn't so I can't post the relevant link for your version.
Next time don't forget it.

Regards
Michel
Re: Error in creating Materialized view [message #567431 is a reply to message #567426] Mon, 01 October 2012 05:18 Go to previous messageGo to next message
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 #567435 is a reply to message #567431] Mon, 01 October 2012 05:46 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: Error in creating Materialized view [message #567442 is a reply to message #567431] Mon, 01 October 2012 06:12 Go to previous messageGo to next message
John Watson
Messages: 4489
Registered: January 2010
Location: Global Village
Senior Member
ON COMMIT requires that the materialized view be fast refreshable. It is not possible to fast refresh a materialized join view with an aggregation.
Re: Error in creating Materialized view [message #567444 is a reply to message #567435] Mon, 01 October 2012 06:15 Go to previous messageGo to next message
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 #567512 is a reply to message #567444] Wed, 03 October 2012 01:00 Go to previous messageGo to next message
radhuu
Messages: 40
Registered: August 2012
Location: India,Chennai
Member
Hi all,

Please help me to get out of this error.

Regards
Radhu.

[Updated on: Wed, 03 October 2012 01:01]

Report message to a moderator

Re: Error in creating Materialized view [message #567513 is a reply to message #567512] Wed, 03 October 2012 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
John Watson wrote on Mon, 01 October 2012 13:12
ON COMMIT requires that the materialized view be fast refreshable. It is not possible to fast refresh a materialized join view with an aggregation.


You have to change the design and/or requirements.

Regards
Michel
Re: Error in creating Materialized view [message #567521 is a reply to message #567513] Wed, 03 October 2012 02:05 Go to previous messageGo to next message
radhuu
Messages: 40
Registered: August 2012
Location: India,Chennai
Member
Hi Michel,

How to find the same without creating materilaized view .

Regards
Radhu
Re: Error in creating Materialized view [message #567526 is a reply to message #567521] Wed, 03 October 2012 02:15 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use a simple view.

Regards
Michel
Re: Error in creating Materialized view [message #567531 is a reply to message #567526] Wed, 03 October 2012 02:40 Go to previous messageGo to next message
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 #567537 is a reply to message #567531] Wed, 03 October 2012 03:21 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And? What do you want? What should be the result for the data you gave?

Regards
Michel
Re: Error in creating Materialized view [message #567540 is a reply to message #567537] Wed, 03 October 2012 04:04 Go to previous messageGo to next message
radhuu
Messages: 40
Registered: August 2012
Location: India,Chennai
Member
I want to get the employees details who joined recently and get more salary than others who are joined before in the same deaprtment and in same dessignation(role).

Regards
Radhu
Re: Error in creating Materialized view [message #567541 is a reply to message #567540] Wed, 03 October 2012 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And does not this what your view give?

Quote:
What should be the result for the data you gave?


Regards
Michel
Re: Error in creating Materialized view [message #567546 is a reply to message #567541] Wed, 03 October 2012 04:32 Go to previous messageGo to next message
radhuu
Messages: 40
Registered: August 2012
Location: India,Chennai
Member
This is what i got as output for the above query.


EMPLOYEE_ID EMPLOYEE_NAME                             DATE_OF_J    DEPT_ID DEPT_NAME            DESIGNATION              SALARY
----------- ----------------------------------------- --------- ---------- -------------------- -------------------- ----------
        534 PABBIN S S                                27-JAN-12         42 .NET                 PAT                       18000
        576 SUGANTHI S                                27-JAN-12         42 .NET                 PAT                       18000
        901 VALAMBURI S                               23-APR-12         11 JAVA                 PAT                       18000
        966 SELVAM D                                  12-FEB-12         26 TESTING              ASSOCIATE                 27350
        969 YACOOB M                                  13-MAR-12         22 DATA WAREHOUSING     ASSOCIATE                 27210
        973 VENKAT R                                  14-FEB-12         11 JAVA                 ASSOCIATE                 27110
        981 SHARMA B                                  22-APR-12         42 .NET                 PA                        23101
        993 ANUSHA T                                  04-AUG-12         36 MAINFRAME            PA                        23201

8 rows selected.


But here tha actual details of employee PABBIN S S is

EMPLOYEE_ID EMPLOYEE_NAME                             DATE_OF_J    DEPT_ID DEPT_NAME            DESIGNATION              SALARY
----------- ----------------------------------------- --------- ---------- -------------------- -------------------- ----------  
534         PABBIN S S                                27-JAN-12         11 JAVA                 PAT                       18000


but in the result it repeat the same details Of 576 for Pabbin ss(534).

i hope it ill help you to understand.

Regards
Radhu
Re: Error in creating Materialized view [message #567551 is a reply to message #567546] Wed, 03 October 2012 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
want to get the employees details who joined recently and get more salary than others who are joined before in the same deaprtment and in same dessignation(role).


Quantify "recently"?

Regards
Michel
Re: Error in creating Materialized view [message #567556 is a reply to message #567551] Wed, 03 October 2012 04:58 Go to previous messageGo to next message
radhuu
Messages: 40
Registered: August 2012
Location: India,Chennai
Member
Quote:

Quantify "recently"?


sorry i cant get what you asking about.

Did you asking what is 'recently' mean? It means who are joined newly in each department.

Regard
Radhu

[Updated on: Wed, 03 October 2012 04:58]

Report message to a moderator

Re: Error in creating Materialized view [message #567564 is a reply to message #567556] Wed, 03 October 2012 06:10 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How many hours/days/weeks/months "recently" means?
Or do you want the LAST employee that joined a department.

Regards
Michel
Re: Error in creating Materialized view [message #567565 is a reply to message #567556] Wed, 03 October 2012 06:10 Go to previous messageGo to next message
John Watson
Messages: 4489
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 #567569 is a reply to message #567564] Wed, 03 October 2012 06:21 Go to previous messageGo to next message
radhuu
Messages: 40
Registered: August 2012
Location: India,Chennai
Member
Hi Michel,

You are right,i want the details of employee who are joined lastly and getting salary than others who are joined before in the same department and in same dessignation

Regards
Radhu
Re: Error in creating Materialized view [message #567570 is a reply to message #567569] Wed, 03 October 2012 06:36 Go to previous messageGo to next message
John Watson
Messages: 4489
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 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
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 Go to previous messageGo to next message
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 #567577 is a reply to message #567575] Wed, 03 October 2012 07:02 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See my previous post.

Regards
Michel
Re: Error in creating Materialized view [message #567581 is a reply to message #567575] Wed, 03 October 2012 07:27 Go to previous messageGo to next message
radhuu
Messages: 40
Registered: August 2012
Location: India,Chennai
Member
Michel

Thanks, But in that result there are more than one employee from the same dapartment and designation its because of my mistake.

I am sorry for,
Quote:
Hi Michel,

You are right,i want the details of employee who are joined lastly and getting salary than others who are joined before in the same department and in same dessignation


I want the employee details who are joined in current year and they should get more salary comparing others within the same department and belonging the same designation.

I want to get result as

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


Sorry once again.

Regards
Radhu
Re: Error in creating Materialized view [message #567583 is a reply to message #567581] Wed, 03 October 2012 07:30 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
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 #567590 is a reply to message #567583] Wed, 03 October 2012 07:50 Go to previous messageGo to next message
radhuu
Messages: 40
Registered: August 2012
Location: India,Chennai
Member
Michel,

That was the result i needed.

EMPLOYEE_ID EMPLOYEE_NAME                             DATE_OF_J    DEPT_ID DESIGNATION              SALARY
----------- ----------------------------------------- --------- ---------- -------------------- ----------
        234 VIRAT K                                   12-JAN-12         11 ASSOCIATE                 27000
        511 MANI V                                    24-APR-07         11 ASSOCIATE                 25000
        973 VENKAT R                                  14-FEB-12         11 ASSOCIATE                 27110


In above employee venkat who joined in 2012 and got more salary camparing others who are belong the same desigantion(ASSOCIATE) within in his department.
like this i post the result and this is what i want.
I didnt tell that you made mistake. The mistake is mine because I didnt tell my reqiurement exactly.
Help me to get the result like that.

Regards
Radhu
Re: Error in creating Materialized view [message #567592 is a reply to message #567590] Wed, 03 October 2012 07:53 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand what you want, at each post you change the result.
I show how to do it, I think you should be able to modify the query to get what you want.

Regards
Michel
Re: Error in creating Materialized view [message #567594 is a reply to message #567590] Wed, 03 October 2012 07:54 Go to previous messageGo to next message
cookiemonster
Messages: 10908
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 #567614 is a reply to message #567594] Wed, 03 October 2012 08:38 Go to previous messageGo to next message
radhuu
Messages: 40
Registered: August 2012
Location: India,Chennai
Member
Michel

Thanks for your patience and response.
I will try to get the result.

Regards
Radhu
Re: Error in creating Materialized view [message #567692 is a reply to message #567614] Thu, 04 October 2012 06:47 Go to previous messageGo to next message
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 #567697 is a reply to message #567692] Thu, 04 October 2012 06:55 Go to previous messageGo to next message
cookiemonster
Messages: 10908
Registered: September 2008
Location: Rainy Manchester
Senior Member
This isn't a good idea:
HAVING SUBSTR(MAX(em.date_of_joining),8)='12'

If you change your default date format that'll break.
I suggest you use:
HAVING MAX(em.date_of_joining) > trunc(sysdate, 'YYYY')
Re: Error in creating Materialized view [message #567700 is a reply to message #567697] Thu, 04 October 2012 07:10 Go to previous messageGo to next message
radhuu
Messages: 40
Registered: August 2012
Location: India,Chennai
Member
Thanks Cookiemonster. I will change it.


Regards
Radhu
Re: Error in creating Materialized view [message #567707 is a reply to message #567700] Thu, 04 October 2012 07:56 Go to previous message
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

Previous Topic: SQL query help required
Next Topic: Query Help
Goto Forum:
  


Current Time: Fri Aug 22 19:17:16 CDT 2014

Total time taken to generate the page: 0.13643 seconds