SQL Tips

---

How can I transform a statement involving an OR condition to a UNION ALL?

Original Statement:

  SELECT  dname, loc

  FROM    dept

  WHERE   loc = 'CHICAGO'

     OR   loc = 'NEW YORK';

New Statement:

  SELECT  dname, loc

  FROM    dept

  WHERE   loc = 'CHICAGO'

  UNION ALL

  SELECT  dname, loc

  FROM    dept

  WHERE   loc = 'NEW YORK';

---

How do I eliminate duplicate values in a table?

Provided below are four methods for identifying or removing duplicate rows from a table:

Method 1:

  DELETE FROM emp a

    WHERE rowid > (

      SELECT min(rowid)

      FROM   emp b

      WHERE  a.emp_id = b.emp_id

    );

Method 2:

  CREATE TABLE emp2 AS

    SELECT distinct *

    FROM   emp;

  DROP TABLE emp;

  RENAME emp2 TO emp;

Method 3:

  DELETE FROM emp

    WHERE rowid NOT IN (

      SELECT    MIN(rowid)

      FROM      emp

      GROUP BY  emp_id

    );

Method 4:

  DELETE FROM emp a

    WHERE EXISTS (

      SELECT 'true'

      FROM   emp b

      WHERE  b.emp_id = a.emp_id

        AND  b.rowid <>

    );

NOTE: If you where to create an index on the joined fields in the inner loop, it may be possible to eliminate N^2 operations as there would be no need to loop through the entire table on each pass be a record.

---

How can I get a count of the different data values in a column?

  SELECT

      dname

    , sum(decode(job, 'CLERK',     1, 0)) Clerk

    , sum(decode(job, 'SALESMAN',  1, 0)) Salesman

    , sum(decode(job, 'MANAGER',   1, 0)) Manager

    , sum(decode(job, 'ANALYST',   1, 0)) Analyst

    , sum(decode(job, 'PRESIDENT', 1, 0)) President

  FROM

      emp e

    , dept d

  WHERE

      e.deptno (+) = d.deptno

  GROUP BY

      dname;

  DNAME               CLERK   SALESMAN    MANAGER    ANALYST  PRESIDENT

  -------------- ---------- ---------- ---------- ---------- ----------

  ACCOUNTING              1          0          1          0          1

  OPERATIONS              0          0          0          0          0

  RESEARCH                 2          0          1          2          0

  SALES                        1          4          1          0          0

---

How can I get count/sum RANGES of data values in a column?

A value "x" will be between values "y" and "z" if: GREATEST(x,y)=LEAST(x,z).

  SELECT

      job

    , sum(decode(greatest(sal,2999), least(sal,6000), 1, 0)) "Range 3000-6000"

    , sum(decode(greatest(sal,1000), least(sal,2999), 1, 0)) "Range 1000-3000"

    , sum(decode(greatest(sal,0),    least(sal,999), 1, 0))  "Range 0-1000"

  FROM      emp

  GROUP BY  job;

  JOB       Range 3000-6000 Range 1000-3000 Range 0-1000

  --------- --------------- --------------- ------------

  ANALYST               2               0            0

  CLERK                   0               2            2

  MANAGER             0               3            0

  PRESIDENT           1               0            0

  SALESMAN           0               4            0

---

How can I dynamically generate a list of comma-separated items?

I often find myself needing to dynamically generate a list of comma-separated items (i.e. a list of database files separated by commas). The output format, for example, would be:

...  Datafile1, Datafile2, Datafile3 ...

Instead of thinking of the list as a linear set of items, re-write the set of items each on a separate line as follows:

...

    Datafile1

  , Datafile2

  , Datafile3

...

Notice that a comma is used on each line except for the first item. We can now take advantage of a popular Analytic Function (RANK) to distinguish the first line.

Consider the following CREATE CONTROLFILE example:

SET HEADING OFF

SET TERMOUT OFF

SET FEEDBACK OFF

SET VERIFY OFF

SET PAGESIZE 9000

SET LINESIZE 135

SET TRIMSPOOL ON

SET TRIMOUT ON

SET ECHO OFF

SET SQLBLANKLINES ON

SPOOL cr_control_file_example.sql

prompt CREATE CONTROLFILE SET DATABASE testdb RESETLOGS NOARCHIVELOG

prompt   MAXLOGFILES 16

prompt   MAXLOGMEMBERS 3

prompt   MAXDATAFILES 100

prompt   MAXINSTANCES 8

prompt   MAXLOGHISTORY 3182

prompt LOGFILE

prompt   GROUP 1 (

prompt       '/u03/oradata/testdb/onlinelog/o1_mf_1_34tn7rv7_.log',

prompt       '/u03/oradata/testdb/onlinelog/o1_mf_1_34tn7sov_.log'

prompt   ) SIZE 50M,

prompt   GROUP 2 (

prompt       '/u03/oradata/testdb/onlinelog/o1_mf_2_34tn7v0s_.log',

prompt       '/u03/oradata/testdb/onlinelog/o1_mf_2_34tn7v7j_.log'

prompt   ) SIZE 50M,

prompt   GROUP 3 (

prompt       '/u03/oradata/testdb/onlinelog/o1_mf_3_34tn7wj5_.log',

prompt       '/u03/oradata/testdb/onlinelog/o1_mf_3_34tn7wqp_.log'

prompt   ) SIZE 50M

prompt DATAFILE

SELECT

    CASE WHEN ROW_NUMBER() OVER(ORDER BY name) = 1

      THEN

        '    ''' || name || ''''

      ELSE

        '  , ''' || name || ''''

      END AS file_name

FROM v$datafile;

prompt CHARACTER SET WE8ISO8859P1

SPOOL OFF

------------------------------------------------------------

CREATE CONTROLFILE SET DATABASE testdb RESETLOGS NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 3182

LOGFILE

GROUP 1 (

'/u03/oradata/testdb/onlinelog/o1_mf_1_34tn7rv7_.log'

, '/u03/oradata/testdb/onlinelog/o1_mf_1_34tn7sov_.log'

) SIZE 50M,

GROUP 2 (

'/u03/oradata/testdb/onlinelog/o1_mf_2_34tn7v0s_.log'

, '/u03/oradata/testdb/onlinelog/o1_mf_2_34tn7v7j_.log'

) SIZE 50M,

GROUP 3 (

'/u03/oradata/testdb/onlinelog/o1_mf_3_34tn7wj5_.log'

, '/u03/oradata/testdb/onlinelog/o1_mf_3_34tn7wqp_.log'

) SIZE 50M

DATAFILE

    '+ORCL_DATA1/orcl/datafile/example.263.623550121'

  , '+ORCL_DATA1/orcl/datafile/soe.269.623559923'

  , '+ORCL_DATA1/orcl/datafile/soeindex.270.623560217'

  , '+ORCL_DATA1/orcl/datafile/sysaux.261.623550085'

  , '+ORCL_DATA1/orcl/datafile/system.259.623549989'

  , '+ORCL_DATA1/orcl/datafile/undotbs1.260.623550053'

  , '+ORCL_DATA1/orcl/datafile/undotbs2.264.623550147'

  , '+ORCL_DATA1/orcl/datafile/users.265.623550181'

CHARACTER SET WE8ISO8859P1

---

Can I retrieve only the Nth row from a table?

Method 1:

  SELECT

      ename

    , job

    , hiredate

  FROM

      emp

  WHERE

      rowid = (

        SELECT rowid FROM emp

        WHERE rownum <= 3

        MINUS

        SELECT rowid FROM   emp

        WHERE rownum <>

      );

Method 2:

  SELECT

      ename

    , job

    , hiredate

  FROM

      emp

  WHERE

        rownum = 1

    AND rowid NOT IN (

      SELECT rowid FROM emp

      WHERE  rownum <>

    );

NOTE: Always remember that there is no explicit order in a relational database.

---

Can I retrieve only rows X to Y from a table?

  SELECT

      ename

    , job

    , hiredate

  FROM

      emp

  WHERE

      rowid in (

        SELECT rowid FROM emp

        WHERE rownum <= 7

        MINUS

        SELECT rowid FROM   emp

        WHERE rownum <>

      );

---

Can I retrieve EVERY Nth row from a table?

Method 1:

  SELECT

      ename

    , job

    , hiredate

  FROM

      emp

  WHERE

      (rowid,0) in (

        SELECT rowid, mod(rownum,4)

        FROM emp

      );

Method 2: Using Dynamic Views: (available in Oracle7.2 and higher)

  SELECT

      ename

    , job

    , hiredate

  FROM (  SELECT rownum rn, empno, ename, job, hiredate

          FROM   emp

       ) d_table

  WHERE mod(d_table.rn,4) = 0;

---

Retrieve the TOP N Rows from a table?

Method 1: Starting with Oracle8i, you can have an inner-query with an ORDER BY clause

  SELECT

      ename

    , job

    , hiredate

    , sal

  FROM (  SELECT empno, ename, job, hiredate, sal

          FROM   emp

          ORDER BY sal DESC

       )

  WHERE rownum <>

Method 2: The following workaround will work with prior releases

  SELECT

      ename

    , job

    , hiredate

    , sal

  FROM

      emp a

  WHERE 5 >= ( SELECT count(distinct b.sal)

               FROM   emp b

               WHERE  b.sal >= a.sal

             )

  ORDER BY a.sal DESC