Category Archives: Database

What is the difference between UNION and UNION ALL

Posted on by

UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.

There is a performance hit when using UNION vs UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).

UNION Example:

 SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar

Result

+-----+
| bar |
+-----+
| foo |
+-----+
1 row in set (0.00 sec)

UNION ALL example:

 SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar

Result

+-----+
| bar |
+-----+
| foo |
| foo |
+-----+
2 rows in set (0.00 sec)
Posted in SQL.

Oracle Release Procedure

Posted on by

During Release the Scripts are executed in the Following Order

  1. Schema – DDL Scripts – (Table Alter Scripts)
  2. Content Script – Rows addition and Deletion
  3. Packages (Stored Procedures and Function)- DML Scripts – (CRUD)

Oracle – Total of Column

Posted on by 

SELECT Sno,
       Name,
       Department,
       Salary,
       SUM(Salary) over(PARTITION BY Department ORDER BY Sno) AS Cum_Salary
  FROM Master_Employee
  ORDER BY Department;

How this Works
PARTITION BY Department ORDER BY Sno

Partition By will first Partition by Department.Which means the Cumulative Sum will get Reset once the Department Changes.

ORDER BY Sno will make sure the Cumulative value calculated will not be swapped and stays the same for the respective rows.

Now to have a total amount for the Salary the query is as below.

  SELECT Sno,
       Name,
       Department,
       Salary       
  FROM Master_Employee
  UNION
  SELECT null,
         null,
         'Total',
         NVL(SUM(Salary), 0)
   FROM Master_Employee
  ORDER BY Sno;

Total along with Cumulative Sum

 SELECT Sno,
       Name,
       Department,
       Salary,
       SUM(Salary) over(PARTITION BY Department ORDER BY Sno) AS Cum_Salary       
  FROM Master_Employee
  UNION
  SELECT null,
         null,
         'Total',
         NVL(SUM(Salary), 0),
         null
   FROM Master_Employee
  ORDER BY Sno;

Another wat of fetching records using sub query

SELECT Sno,
       Name,
       Department,
       Salary,
       CASE
         WHEN Department = 'Total' THEN
          NULL
         ELSE
          SUM(Salary) over(PARTITION BY Department ORDER BY Sno)
       END AS Cum_Salary
  FROM (SELECT Sno, Name, Department, Salary, null, 1 AS Sort_Order
          FROM Master_Employee
        UNION
        SELECT null,
               null,
               'Total',
               NVL(SUM(Salary), 0),
               null,
               2 AS Sort_Order
          FROM Master_Employee)
 ORDER BY Department, Sno, Sort_Order;

Oracle Sorting Alphanumeric Values

Posted on by

While Doing sort in oracle the Normal sort works differently for the following Data

CREATE TABLE TestTable(Col1 VARCHAR2(20));

insert into TestTable values('A11');
insert into TestTable values('A260');
insert into TestTable values('A10');
insert into TestTable values('A5');

A11
A260
A10
A5

  SELECT * 
    FROM TestTable 
   ORDER BY col1;

Output

A10
A11
A260
A5

You may expected A5 before A10 and A11. But It would have been before A10 and A11 when it is A05. The workaround for this is as below.

SELECT * 
  FROM TestTable
 ORDER BY lpad(col1,9999999);

Output

A5
A10
A11
A260

How to add Cumulative Total Oracle Query

Posted on by 
 SELECT * FROM EMP;


 SELECT EMPNO
      ,ENAME
      ,dept_no
      ,SAL
      ,SUM(sal) over(ORDER BY empno) AS dept_cum
  FROM EMP
 ORDER BY empno;

Lets group the Cumulative Sum Based on DepartmentId

SELECT EMPNO
      ,ENAME
      ,dept_no
      ,SAL
      ,SUM(sal) over(PARTITION BY dept_no ORDER BY empno) AS dept_cum
  FROM EMP
 ORDER BY empno;

Empty Check in Oracle

Posted on by 
SELECT  'Equal' FROM DUAL
 WHERE  NVL(NULL,'') = '';

Output

SELECT  'Equal' FROM DUAL
 WHERE  NVL(NULL,'~') = '~';

Output
Empty

While Passing Parameters to Procedure its should be

SELECT  'Equal' FROM DUAL
 WHERE  NVL(col_name, '~') = NVL(p_param, NVL(col_name, '~'));

Sorting with Date in Oracle Select

Posted on by

While sorting with date it should be converted using to_date first with the required format.

Other wise while sorting in Desc Order 31-02-2015 will come in front of 21-10-2015

SELECT * 
  FROM tableName
ORDER BY to_date(DATE_OF_BIRTH, 'DD-MM-YYYY');

Oracle Using Limit with ROWNUM

Posted on by

In Oracle if you want to limit the records based on ROWNUM(mostly while doing pagination) the between is not going to work.

Below Doesn’t work

 
 SELECT Column1, Column2, ......., Column N  
   FROM TableName
  WHERE ROWNUM Between 5 AND 10;

The alternate for this is as Follows

 
SELECT * FROM
 (SELECT rows_to_page.*, ROWNUM rnum FROM
   (SELECT Column1, Column2, ......., Column N  
      FROM TableName
     WHERE ROWNUM Between 5 AND 10) rows_to_page
    WHERE ROWNUM < end_offset) 
 WHERE rnum > start_offset;

Working with Date in Oracle

Posted on by 
--16-12-2015 12:33:21
SELECT SYSDATE FROM dual;

--16-12-2015
SELECT trunc(SYSDATE) FROM dual;

--16-12-2015
SELECT to_date(SYSDATE) AS ToDate  FROM dual;

--16-12-2015
SELECT to_date(SYSDATE, 'DD-MM-YY') AS ToDate  FROM dual;

--16-12-2015
SELECT to_char(SYSDATE, 'DD-MM-YYYY') AS ToDate  FROM dual;

Data Migrations Scripts

Posted on by

Modifying Column Type in Table
Method 1 :

  1. Add a Copy of the Column appending to some identifier to say its a copy i.e _CP
  2. Update the New Column to the Column whose datatype is to be changed
  3. Drop the Old Column
  4. Rename the New Column to Old Column Name
ALTER TABLE TB1 ADD COL_CP VARCHAR2(10);

UPDATE TB1 SET COL_CP = COL1;

ALTER TABLE TB1 DROP COLUMN COL1;

ALTER TABLE TB1 RENAME COLUMN COL_CP to COL1;

In case you are upgrading from Varchar2(250) to Varchar2(4000) the above is not required

Method 2 :

  1. Create a New table(TB2) with the same Columns and Type
  2. Insert the Values into TB2
  3. Truncate or Drop Table TB1
  4. Run the Changes such as New DDL Script
  5. Insert into new Table TB1 from TB2
  6. Drop Table TB2
CREATE TABLE TB1(COL1 NUMBER);

INSERT INTO TB1 VALUES(10);

CREATE TABLE TB2 AS
SELECT * FROM TB1;

DROP TABLE TB1;

.
.
New table Creation Script with New Column Types
.
.
.

INSERT INTO TB1
SELECT * FROM TB2;

DROP TABLE TB2;