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
