Utility Queries - II

For sample working of Hierarchy queries you can refer to the link below

Hierarchy Queries

Hierarchy Query SYS_CONNECT_BY_PATH

Merge Query: To maintain history of employees

Analytic Query: Department Wise highest salary

Below is an example query of a table where the dates are in ascending order of srl no in a table.

But in some cases the record with a higher srl number is having a lower date than the previous record.

We need to find out such incorrect records. Below is an example.

There is an EMP_DATE table. 

I need to find out list of employees and their records where the employee record with a lower serial number is having a higher date.

CREATE TABLE EMP_DATE
(
	EMPLOYEE_ID   NUMBER,
	SRL_NO        NUMBER,
	EMP_DATE      DATE
);

INSERT INTO EMP_DATE VALUES(10001, 1, '01-JAN-23');
INSERT INTO EMP_DATE VALUES(10001, 2, '01-FEB-23');
INSERT INTO EMP_DATE VALUES(10001, 3, '01-MAR-23');
INSERT INTO EMP_DATE VALUES(10001, 4, '28-FEB-23');

INSERT INTO EMP_DATE VALUES(10002, 1, '01-MAR-25');
INSERT INTO EMP_DATE VALUES(10002, 2, '01-APR-25');
INSERT INTO EMP_DATE VALUES(10002, 3, '01-MAY-25');
INSERT INTO EMP_DATE VALUES(10002, 4, '01-JUN-25');
INSERT INTO EMP_DATE VALUES(10002, 5, '01-JUL-25');

INSERT INTO EMP_DATE VALUES(10003, 1, '01-SEP-26');
INSERT INTO EMP_DATE VALUES(10003, 2, '02-OCT-26');
INSERT INTO EMP_DATE VALUES(10003, 3, '01-OCT-26');

SELECT EMPLOYEE_ID, SRL_NO,EMP_DATE FROM (
SELECT EMPLOYEE_ID, SRL_NO, PRIOR SRL_NO prior_srl_no, EMP_DATE, PRIOR emp_date prior_date
FROM EMP_DATE
CONNECT BY PRIOR SRL_NO + 1 = SRL_NO
AND PRIOR EMPLOYEE_ID = EMPLOYEE_ID
START WITH SRL_NO = 1)
WHERE prior_date > EMP_DATE;

EMPLOYEE_ID SRL_NO EMP_DATE

10001 4 28-FEB-23

10003 3 01-OCT-26

Merge Query

The merge query will query on a set of data for a given key field.

If data for the given key criteria exists, then merge does an update.

If data for the given key criteria does not exist, then merge does an insert.

For example

You want to archive employee data into employee archive table on a daily basis.

For new employees an insert will happen, and for existing queries you can perform an update.

Below is the query for the same.

MERGE INTO employees_hist eh
    USING (SELECT * FROM employees WHERE HIRE_DATE > ADD_MONTHS(SYSDATE, -500))em -- WHERE HIRE_DATE > ADD_MONTHS(SYSDATE, -500)
    ON (eh.EMPLOYEE_ID = em.EMPLOYEE_ID)
  WHEN MATCHED THEN
    UPDATE SET eh.EMAIL = em.EMAIL
  WHEN NOT MATCHED THEN
    INSERT (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID, OLD_EMAIL, EMP_CITY)
    VALUES (em.EMPLOYEE_ID, em.FIRST_NAME, em.LAST_NAME, em.EMAIL, em.PHONE_NUMBER, em.HIRE_DATE, em.JOB_ID, em.SALARY, em.COMMISSION_PCT, em.MANAGER_ID, em.DEPARTMENT_ID, em.OLD_EMAIL, em.EMP_CITY);

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	COMMISSION_PCT	MANAGER_ID	DEPARTMENT_ID	OLD_EMAIL	EMP_CITY
101	Neena	Kochhar	NKOCHHAR	515.123.4568	21-SEP-05 00:00:00	AD_VP	17000		100	90
103	Alexander	Hunold	AHUNOLD	590.423.4567	03-JAN-06 00:00:00	IT_PROG	9000		102	60
104	Bruce	Ernst	BERNST	590.423.4568	21-MAY-07 00:00:00	IT_PROG	6000		103	60
105	David	Austin	DAUSTIN	590.423.4569	25-JUN-05 00:00:00	IT_PROG	4800		103	60
106	Valli	Pataballa	VPATABAL	590.423.4560	05-FEB-06 00:00:00	IT_PROG	4800		103	60
107	Diana	Lorentz	DLORENTZ	590.423.5567	07-FEB-07 00:00:00	IT_PROG	4200		103	60
110	John	Chen	JCHEN	515.124.4269	28-SEP-05 00:00:00	FI_ACCOUNT	8200		108	100
111	Ismael	Sciarra	ISCIARRA	515.124.4369	30-SEP-05 00:00:00	FI_ACCOUNT	7700		108	100
112	Jose Manuel	Urman	JMURMAN	515.124.4469	07-MAR-06 00:00:00	FI_ACCOUNT	7800		108	100
113	Luis	Popp	LPOPP	515.124.4567	07-DEC-07 00:00:00	FI_ACCOUNT	6900		108	100

Analytic query

Below is an example to get department wise employee with the highest salary using analytic functions.

SELECT EMP_NAME "Employee Name",  DEPARTMENT_NAME "Department Name",SALARY "Highest Salary"
FROM
(
SELECT FIRST_NAME || ' ' || LAST_NAME EMP_NAME, d.DEPARTMENT_NAME ,
SALARY,
ROW_NUMBER() OVER ( PARTITION BY e.DEPARTMENT_ID ORDER BY SALARY DESC) AS RN
FROM EMPLOYEES e, DEPARTMENTS d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID)
WHERE RN = 1;

Employee Name	Department Name	Highest Salary
Jennifer Whalen	Administration	4400
Michael Hartstein	Marketing	13000
Den Raphaely	Purchasing	11000
Susan Mavris	Human Resources	6500
Matthew Weiss	Shipping	8200
Alexander Hunold	IT	9000
Hermann Baer	Public Relations	10000
John Russell	Sales	14000
Steven King	Executive	24000
Nancy Greenberg	Finance	12008
Shelley Higgins	Accounting	12008

Query to extract characters from the fifth to the twelfth position 

We can use oracle SUBSTR function for this. 

Definition 

The SUBSTR functions return a portion of string , beginning at character position , substring_length characters long. Query is as below. From the 5th to the 12th postion there are 7 characters. So as there parameter we give the length of the string we want. ie : 7.

select SUBSTR('1234567890ABCDE',5, 7 ) FROM DUAL;

SUBSTR_5_7
567890A

Query to extract a subset of data based on a pattern. 

Example : 

I have a telepone number 123-456-7890 I want to extract the middle position of this string. 

This can be done using a combination of SUBSTR and INSTR. 

Using SUBSTR I can do SUBSTR(string, 5, 3). ie - Substr with first character after first '-' and with last character before second '-'. 

Definition 

The INSTR functions search string for pattern . The function returns an integer indicating the position of the pattern in string that is the first character of this occurrence. To get the position for second occurrence of the pattern, you can pass this as fourth parameter to INSTR. The third parameter is the position in the string from where to start the search for the pattern. This defaults to 1. 

So we have

INSTR(string, pattern_to_search, position_in string_to_start, occurance_of_the_patern). 3rd and fourth parameters are optional. 

In above example, 

string - '123-456-7890' 

pattern_to_search - '-' 

INSTR('123-456-789','-',1,1) - will give the position of the first occurrence of the pattern. 

INSTR('123-456-789','-',1, 2) - will give the position of the second occurrence of the pattern. 

With this information we can now build the query as follows.

SELECT SUBSTR('123-456-789',INSTR('123-456-789','-',1,1)+1,INSTR('123-456-789','-',1, 2)-INSTR('123-456-789','-',1,1)-1) FROM DUAL;

string_extract
456

Thought for the day

5 Rules of happiness: Don’t hate, don’t worry, give more, expect less, live simply