NVL For Correct Counts

If you're matching on fields that have any possibility of being null, make sure you code them with the NVL function. If you don't, your count will not be correct.

Select Count(*)
  From Client  B
  Join Account A
    On a.Code           =     b.Retail_Num
 Where Nvl(a.Name ,' ') = Nvl(b.Client_Name ,' ')
   And Nvl(a.State,' ') = Nvl(b.Client_State,' ')
   And Nvl(a.Zip  ,' ') = Nvl(b.Client_Zip  ,' ');

Row_Number() Over Partition By

This function can come in very handy. As shown in this example, Row_Number numbers each row within a department in order of the hiredate.

Select     Row_Number()
                Over
                (Partition By Deptno
                     Order By Hiredate)
           As
       Ord,
       -------------------------------
       EmpNo,
       Ename,
       Job,
       HireDate,
       Deptno
  From Emp;

This then allows you to generate a report of the first person to be hired by each department.

Select *
  From
      (
      Select     Row_Number() 
                      Over 
                   (Partition By Deptno
                        Order By Hiredate) 
                 As
             Ord,
             -------------------------------
             EmpNo, 
             Ename,
             Job,
             HireDate,
             Deptno
        From Emp
      )
  Where Ord = 1
/

See Oracle documentation for further detail:
Partition By clause

Connect By

Show the hierarchy of data using 'Connect By' statements. 'Order Siblings' is new in 9i.

Example 1:

Set Pagesize 60
Column Empnm Format A20  Heading EmpNm
Column Mgr   Format 9999 Heading Mgr
Column Empno Format 9999 Heading Emp#

Ttitle Left 'Example 1:' skip 2

Select Empno,
            Rpad(
                   Lpad(' ',3*(Level-1)) /* indention based on level*/
                 ||Level||'. '           /* location on food chain */
                 ||Ename                 /* employee's name */
             ,20
             ,'. ')         /* Right pad with periods and spaces*/
            As
      Empnm,
      Mgr
 From Emp
Start With Ename = 'KING'
Connect By Mgr = Prior Empno
Order Siblings By Ename;

---

Example 2:

Column Enames  Format A14
Column Sortord Format A23

Ttitle Left 'Example 2:' skip 2

Select            Lpad (' ', 3*(Level-1))||Ename
                As
      Enames,     Sys_Connect_By_Path(Ename, '/' )
                As
      Sortord
 From Emp
Start With Ename = 'KING'
Connect By Mgr = Prior Empno
Order By Sortord;

Making a Multiple Table Query Faster

I don't totally understand this, but my experience has been that the second query shown will significantly improve the processing speed.

To improve speed:

• use In-line views (virtual tables)
• sort data before combining with another in-line view
• don't move any data forward that isn't necessary (old keys)
• collapse all data as soon as possible
  

Slower:

Select A.Name, A.City, B.Description
  From Master    A
  Join Reference B
 Using (Name)
 Where Zip = '22042';

Faster:

Select A.Name, A.City, B.Description
  From
       (
        Select Name, City
          From Master
         Where Zip = '22042'
         Order By Name
       ) A
  Join       
      (
       Select Name, Descripition
         From Reference
        Where Zip = '22042'
        Order By Name
      ) B      
Using (Name);

Tiny link to this post: http://tinyurl.com/fast-query

Creating Column Headings From Data

In this request, they want the last 3 years that anyone was hired to be the column headings.
We need to read the data to get the variables to be used as column headers.

Column Yr1 New_Value Yr1
Column Yr2 New_Value Yr2
Column Yr3 New_Value Yr3

--+- 1 ----------------------------------------------------------------
--| Max will put these all on one line.
--+--------------------------------------------------------------------
Select Max(Case When Rownum = 1 Then Year End) Yr1,
     Max(Case When Rownum = 2 Then Year End) Yr2,
     Max(Case When Rownum = 3 Then Year End) Yr3
From
  --+- 1.1 ----------------------------------------------------------
  --| Get a list of years & put them in order.
  --+----------------------------------------------------------------
    (Select Distinct To_Char(HireDate,'YYYY') Year
       From Emp
      Order By Year Desc
    )
  --+----------------------------------------------------------------
Where Rownum <4;

This will generate a one line report:

YR1  YR2  YR3
---- ---- ----
1987 1982 1981

This sets up the columns to be printed with the format required.

Column '&Yr1' Format $999,999
Column '&Yr2' Format $999,999
Column '&Yr3' Format $999,999

--+- 2 ----------------------------------------------------------------
--| Stack them on lines by department.
--+--------------------------------------------------------------------
Select DeptNo,
     Sum(Case When Year = '&Yr1' Then Sal End) "&Yr1",
     Sum(Case When Year = '&Yr2' Then Sal End) "&Yr2",
     Sum(Case When Year = '&Yr3' Then Sal End) "&Yr3"
From
  --+- 2.1 ----------------------------------------------------------
  --| Convert date & eliminate records not needed.
  --+----------------------------------------------------------------
     (Select To_Char(Hiredate,'YYYY') Year,
             Sal,
             Deptno
        From Emp
       Where To_Char(Hiredate,'YYYY')  In ('&Yr1','&Yr2','&Yr3')
     )
  --+----------------------------------------------------------------
Group By Deptno;

Final Report:

   DEPTNO      1987      1982      1981
--------- --------- --------- ---------
     30 .         .            $9,400
     20    $4,100 .            $5,975
     10 .            $1,300    $7,450

Sum Multiple Columns Based on Conditions

If you need to sum several columns based on conditions, try the Case statement as opposed to running multiple queries with the Where.

A Compute was used with Break to get some various calculations at the end of the report.

Clear Screen
Set Space 3

Break on Report
Compute Count Label 'Not Null Count.' -
        Sum   Label 'Total..........' -
        Avg   Label 'Average........' -
        Min   Label 'Minimum........' -
        Max   Label 'Maximum........' -
    Of Sal10 Sal20 Sal30 SalAll       -
    on Report

Column Year   Format A15    Heading 'Year'
Column Sal10  Format 99,999 Heading 'Dept 10|Salary'
Column Sal20  Format 99,999 Heading 'Dept 20|Salary'
Column Sal30  Format 99,999 Heading 'Dept 30|Salary'
Column SalAll Format 99,999 Heading ' Total |Salary'

---------------------------------------------------
Select Year,
       Sum(Case When DeptNo = 10
                Then Sal End)     As Sal10,
       Sum(Case When DeptNo = 20
                Then Sal End)     As Sal20,
       Sum(Case When DeptNo = 30
                Then Sal End)     As Sal30,
       Sum(          Sal    )     As SalAll
  From
       --------------------------------------------
       -- Break it down by year
       --------------------------------------------
       ( Select To_Char(Hiredate,'YYYY') Year, 
                Sal,
                Deptno
           From Emp
       )
       --------------------------------------------
Group by Year;

Constraints - Nulls? Table/Column?

• A Foreign Key is called a Referential Integrity Constraint
• A Foreign Key can't be in a remote database.
• A Foreign Key can refer to a unique or primary key, even in the same table.
  
• A check can't use the pseudocolumns.
• Use the table constraint form to check more than one column.
  

set echo off
set feedback off
Drop Table T1 Cascade Constraints;
Drop Table T2 Cascade Constraints;
Drop Table T3 Cascade Constraints;
clear screen
set echo on
set feedback on
Create Table T3
( C3 Char(6)
, C6 Number(7,2) Constraint T3_Uk Unique
, C7 Date
, Constraint T3_Pk Primary Key (C3)
);
Create Table T2
( C2 Varchar2(4)  Constraint T2_Pk Primary Key
, C3 Char(6)      Constraint T2_Fk References T3(C3)
, C4 Date         Constraint T2_C4_NN Not Null
, C5 Varchar2(10) Check (C5 <=9)
);  
Create Table T1  
( C1 Number(4)   Not Null          
, C2 Varchar2(4) 
, Constraint T1_Pk Primary Key (C1,C2) 
, Constraint T1_Fk Foreign Key (C2) References T2(C2)  
);  
Clear Screen 
Ttitle Left 'Constraint_Type: ' xConstraint_Type ' Owner: ' Xowner ' R_Owner: ' Xr_Owner ' Index_Owner: ' Xindex_Owner Skip 2 
Break On Constraint_Type Skip Page Dup  

Column Owner            Noprint New_Value Xowner 
Column R_Owner          Noprint New_Value Xr_Owner 
Column Index_Owner      Noprint New_Value Xindex_Owner 
Column Constraint_Type  Noprint New_Value Xconstraint_Type  
Column Index_Name Format A5 
Column Table_Name Format A5 
Column Validated  Format A9  

Select *
  From User_Constraints   
 Where Table_Name Like 'T_'  
 Order By Constraint_Type;

Discrepancies in Table Data

A way to find discrepancies in data between two tables is shown below. I first ran the top three lines to create a similar table to 'EMP', but with differences in the values.

Create Table Emp2
   As
     Select *
       From Emp;
Update Emp
         Set Mgr   = 7698
       Where EmpNo = 7369;
Update Emp2
         Set Job   = 'DBA'
       Where EmpNo = 7566;

The 'WITH' allows the tables to be read only once to gather the data needed for evaluation.

Break on EmpNo Skip 1  NoDup

With Table1 as (
                Select EmpNo, Job, Mgr
                  From Emp
                 Where DeptNo = 20
               ),
     Table2 As (
                Select EmpNo, Job, Mgr
                  From Emp2
                 Where DeptNo = 20
               )
---------------------------------------------------
Select *
  From
      ( 
        ----------------------------------
        -- add user/table to differences
        ----------------------------------
        (Select 'Emp' Tbl, In1Not2.*
           From 
                -------------------------
                -- Show records in 1 not 2
                -------------------------
                (Select * From Table1
                 Minus
                 Select * From Table2
                )In1Not2
        )
    Union
        -----------------------------
        -- add user/table to differences
        -----------------------------
        (Select 'Emp2' Tbl, In2Not1.*
           From 
                -------------------------
                -- Show records in 1 not 2
                -------------------------
                (Select * From Table2
                 Minus
                 Select * From Table1
                )In2Not1
        )
      )
Order By EmpNo, Tbl;

TBL       EMPNO JOB              MGR
---- ---------- --------- ----------
Emp        7369 CLERK           7698
Emp2            CLERK           7902

Emp        7566 MANAGER         7839
Emp2            DBA             7839

Dates

A date needs to be entered either in the standard format (dd-mon-yy) as shown in the first example:

INSERT INTO EMP VALUES(1234,'MICKEY','CEO',7249,
                       '10-JUL-08',
                       1000,500,10
                      );

or you must use the TO_DATE function which tells the system the format of the date.

INSERT INTO EMP VALUES(1235,'MINNEY','DBA',7249,
                       TO_DATE('10/07/2008','MM/DD/YYYY'),
                       1000,500,10
                      );

Use To_Char to display a date in a format other than dd-mon-yy

Select To_Char(Sysdate,'mm/dd/yyyy') Today
  From Dual;


Today
----------
05/29/2008

Cartesian Merge

When trying to ensure a pair of fields match another pair of fields, do not use bars as this creates a Cartesian Merge which can significantly slow down your program. Use commas to separate the fields as shown in the 2nd example.

WRONG CODE:

SELECT field1, field2
FROM table
WHERE field1||field2 IN              /* NO */
  (SELECT field1||fieldS2            /* NO */
     FROM table
    WHERE some conditions);

CORRECT CODE:

SELECT field1, field2
FROM table
WHERE (field1,field2) IN           /* YES */
  (SELECT field1,fieldS2           /* YES */
     FROM table
    WHERE some conditions);

Resetting SETs

To reset the 'SETs' back to what you had on sign on (or what you changed the defaults to), put the following at the end of your LOGIN.SQL.

Store Set InitSets Replace
At the end of the query, add the following line, or whenever you want to reset any settings you have changed, type the following at the SQL prompt:

@initsets

Outer Join Problem

Two tables need to be joined. They have the following columns.

Tbl1          Tbl2
----          ----
FieldA (key)  FieldA (key)
FieldB        FieldD
FieldC   

Tbl1 data:    Tbl2 data:
----------    ----------
1             1
5
6             6
              7

We want to see everything on Tbl2 whether or not there is a record on Tbl1.

Even though we specify '(+)' to indicate an outer join, this will not work correctly since there is another condition involved on that table.

Select tbl2.FieldA, tbl2.FieldD, tbl1.FieldB 
  From tbl1, tbl2 
 where tbl1.FieldA(+) = tbl2.FieldA         ** DOESN'T WORK **
   And tbl1.FieldC = '1';

Fix #1:

Select tbl2.FieldA, tbl2.FieldD, tbl1.FieldB 
  From tbl1, tbl2 
 where tbl1.FieldA(+) = tbl2.FieldA         
   And tbl1.FieldC(+) = '1';

Fix #2:

Select tbl2.FieldA, tbl2.FieldD, tbl1.FieldB 
  From (
        Select *
          From tbl1
         Where FieldC(+) = '1'  -- the condition is moved to a subquery
       ) tbl1,
       tbl2 
 where tbl1.FieldA(+) = tbl2.FieldA;

Performance: Hours to Seconds

I was sent the following query. It was taking about two hours to run. After the 'ORDER BY's were added, it went down to seconds to run. One reason I'm surprised is each separate query wasn't pulling that many records: the first about 900; the second less than 100.

 Select Count(*), V1.User_id
   From
       (  Select a.store_id, a.user_id
            From a,b
           Where a.store_id=b.store_id
             And miscellaneous conditions
        Order By a.store_id                       -- added by me
       ) V1,
       (  Select a.store_id, a.user_id
            From a,b
           Where a.store_id=b.store_id
             And miscellaneous conditions
        Order By a.store_id                       -- added by me
       ) V2
   Where V1.store_id=V2.store_id
Group by V1.user_id
Order by V1.user_id;

SQL Query Layout

When the query gets a little long and it's hard to find anything, I've used the following kind of outline/layout. You may even want to number the virtual tables created based on the paragraph number. For example, a table would be 'vt121', which means it would have been created in paragraph 1.2.1. This way it's always easy to find where the code came from.

--+- 1 -------------------------------------------------------------------------
--|
--+-----------------------------------------------------------------------------
Select
 From
     --+- 1.1 -----------------------------------------------------------------
     --|
     --+-----------------------------------------------------------------------
     (
     Select
       From
           --+- 1.1.1 ---------------------------------------------------------
           --|
           --+-----------------------------------------------------------------
           (
           Select
           From
               --+- 1.1.1.1 ---------------------------------------------------
               --|
               --+-------------------------------------------------------------
               (
               Select
                 From 
                     --+- 1.1.1.1.1 -------------------------------------------
                     --|
                     --+-------------------------------------------------------
                     (
                     Select
                       From
                     )
               --+- 1.1.1.1 End -----------------------------------------------
               --|
               --+-------------------------------------------------------------
               )
           --+- 1.1.1 End -----------------------------------------------------
           --|
           --+-----------------------------------------------------------------
           )
     --+- 1.1 End -------------------------------------------------------------
     --|
     --+-----------------------------------------------------------------------
     )
--+- 1 End ---------------------------------------------------------------------
--|
--+-----------------------------------------------------------------------------
;

Lead / Lag

This example is a little silly, but I'm trying to use the tables that come with the Oracle database so anyone can paste them in and run them. This is showing how many days between hirings within each department. The lead statement Lead(Hiredate) Over (Partition By Deptno Order By Hiredate) basically creates a field in the current record based on a field in the next record. It is broken down (or partitioned by Deptno), and is in the Hiredate order. Lag would work in the opposite direction. Obviously, the last one in the department wouldn't have a value since there is no record behind it in that 'partition.' This can work well for calculating time if there are separate records for when something is started and stopped.

SQL> Set Pagesize 60
Break On Deptno Dup Skip 1
Select EmpNo, 
       EName, 
       Job, 
       DeptNo, 
       HireDate,
       ----------------------------------------------------------------
                            Lead(HireDate) Over (Partition By DeptNo
                                                    Order By HireDate)
                            - Hiredate As 
       Days_Between_Hirings
       ----------------------------------------------------------------
  From Emp
 Where DeptNo <> 30                             -- just to limit output
 Order By DeptNo, 
          HireDate;

To Generate a CSV file

To create a CSV file, commas need to be concatenated between the fields.

Sets:

• Pagesize set to zero turns off page breaks and headers
• Feedback set to off removes the record counts from the bottom of the file
• Trimspool set to off removes extra spaces from the end of the record

Alpha fields must be concatenated with double quotes in case it contains a comma.

Set Echo Off
Set Term Off
Set Pagesize 0
Set Feedback off
Set Trimspool On

Spool emp1.csv

Select -----------------------------------------------------
     -- Trim the leading spaces off a number
     -----------------------------------------------------
     LTrim(Sal,' ')  
     ||   
     -----------------------------------------------------
     -- Insert a comma for a column separator
     -----------------------------------------------------
     ','             
     ||   
     -----------------------------------------------------
     -- An Alpha Field with possible embedded commas need
     -- double quotes
     -----------------------------------------------------
    '"'|| EName ||'"'      
From Emp;

Spool off

Set Term on

Prompt EMP1.CSV has been generated.

Define a Variable List

Define Dept_List = "20,30"

Select *
 From Dept
Where Deptno in (&Dept_list);

old   3:  Where Deptno in (&Dept_list)
new   3:  Where Deptno in (20,30)

   DEPTNO DNAME          LOC
---------- -------------- -------------
       20 RESEARCH       DALLAS
       30 SALES          CHICAGO

WITH (Subquery_Factoring_Clause) 9i

The WITH clause lets you use the results of a complex query more than once. Using the WITH clause, Oracle retrieves the results of a query and stores them in the user's temporary tablespace. It does not support recursive use of the WITH clause.

With DeptTempTbl As (Select DeptNo,
                            Count(*) As DeptRecCnt
                       From Emp
                   Group By Deptno)
/*-------------------------------------------------------------*/
/* Use the results from the above query in the following query */
/* and subquery                                                */
/*-------------------------------------------------------------*/
Select DeptNo,
       DeptRecCnt
  From DeptTempTbl
 Where DeptRecCnt >= (Select Avg(DeptRecCnt)
                        From DeptTempTbl);

    DEPTNO DEPTRECCNT
---------- ----------
        30          6
        20          5

Query Documentation

This is what I typically write at the top of my queries. It's easier to type it in now than try to remember why you wrote it next year. :)

rem +--------------------------------------------------------------------------+
rem | Script ID:     x.sql
rem |
rem | Purpose:      
rem |               
rem |               
rem | Developer:    
rem | Script Date:   12/12/2003
rem | Oracle Ver:    10g
rem |
rem | Input File(s): none
rem |
rem | Table(s) Used:
rem |               
rem | Called by:    
rem | Calls:        
rem |
rem | Variables:    
rem |
rem | Output:        n/a
rem |
rem | Revisions:     Dvl Date     Ver Comment/Change
rem |                --- -------- --- -----------------------------------------+
rem |               
rem +--------------------------------------------------------------------------+

Select Based on a Date

Since date fields contain minutes and seconds, to match on dates use the function TRUNC . This will strip if down to the month, day and year.

Select Empno, Ename, Job, Hiredate
  From Emp
 Where Trunc(Hiredate) = Trunc(Sysdate);

     EMPNO ENAME      JOB       HIREDATE  
---------- ---------- --------- --------- 
      1234 MICKEY     CEO       26-MAY-08 

Defining a Variable

There are three ways to set up a variable: Define, Accept and New_Value.

---

Use Define when you know the value and you want to hard-code it in the query.

Define variable = text

Define DNo1 = 10

Select EmpNo, EName
From Emp
Where Deptno = &DNo1;

  EMPNO ENAME
---------- ----------
   7782 CLARK
   7839 KING
   7934 MILLER

---

Use Accept when you the query operator knows the value and you want them to type it.

Accept variable [NUMBER|CHAR|DATE [FORMAT format] [DEFAULT default] [PROMPT text|NOPROMPT] [HIDE]

The query will pause and the prompt display. The 'Number' option will assure a number is entered. 'Format 99' will make sure only two digits are entered. (I typed in the '20' when the query paused.)

Accept DNo2 Number Format 99 Prompt 'Please enter Department Number: '

Please enter Department Number: 20

Select EmpNo, EName
From Emp
Where DeptNo = &DNo2;

  EMPNO ENAME
---------- ----------
   7369 SMITH
   7566 JONES
   7788 SCOTT
   7876 ADAMS
   7902 FORD

---

Use New_Value when the value can be retrieved from a table.

Column column New_Value variable

Column DeptNo New_Value DNo3

Select Ename, Deptno
From Emp
Where Ename = 'ALLEN';

ENAME          DEPTNO
---------- ----------
ALLEN              30

Select *
From Dept
Where Deptno = &DNo3;

 DEPTNO DNAME          LOC
---------- -------------- -------------
     30 SALES          CHICAGO

Calculate Table Size

Compute Sum Label 'Total' Of No_Kb On Report
Compute Sum Label 'Total' Of No_Mb On Report

Select Table_Name,
       -----------------------------------------------------
       Num_Rows,
       -----------------------------------------------------
       Avg_Row_Len,
       -----------------------------------------------------
                Num_Rows * Avg_Row_Len
                As
       No_Bytes,
       -----------------------------------------------------
                Round(Num_Rows * Avg_Row_Len/1024,2)
                As
       No_Kb,
       -----------------------------------------------------
                Round(Num_Rows * Avg_Row_Len/1024/1024,2)
                As
       No_Mb
       -----------------------------------------------------
  From User_Tables;

                    Avg
Table          Num  Row
Name          Rows  Len   NO_BYTES      NO_KB      NO_MB
------------ ----- ---- ---------- ---------- ----------
DEPT             4   20         80        .08          0
EMP             14   37        518        .51          0
BONUS            0    0          0          0          0
SALGRADE         5   10         50        .05          0
                                   ---------- ----------
Total                                     .64          0

Create a Flat File

SETS--

• SPACE: set spacing between columns to none
• PAGESIZE: set pagesize to 0 to turn off all headings
• TRIMSPOOL: set off so blanks at the end are not removed
• FEEDBACK: set off -- no record count needed at end
• TERM: set off the terminal to speed up the spooling
• LINESIZE: set the output file record length

Select fields as shown:

Filler Fields:
The placement of each field is dependent on the one before it. If you need a filler field, code it as '  ' (Like the 3rd field listed below.)

For alphabetic fields:
1) Always include SUBSTR function. With it, if the database column size is increased, it will be handled.
2) Use NVL on all columns that can potentially be NOT NULL. The number of spaces between the quotes should match the substr length.

For numeric fields:
1) Change it to character with TO_CHAR (This will left-justify it).
2) Left pad with zeros up to the necessary length with LPAD.

SET SPACE 0
SET PAGESIZE 0 
SET TRIMSPOOL OFF
SET FEEDBACK OFF
SET TERM OFF    
SET LINESIZE 40
/*      The spool file name should be lower case if writing to Unix.      */
SPOOL flatfile.txt

SELECT LPAD(TO_CHAR(sal),8,0)              ,
       NVL(SUBSTR(ename,1,15),'          '),
       'HELLO'                                 -- write a literal
  FROM Emp;

SPOOL OFF

/*If writing to Unix, set permissions for all to access */
-- host chmod 777 flatfile.txt

Column Length

A column's length is based on three fields: Data_Precision, Data_Length, and Data_Scale.

Column Data_Len Heading Data|Length Format A6


Select Column_Name,
     Data_Type,
                 Case When (Data_Precision Is Null)
                      Then To_Char(Data_Length,'9999')
                      Else '  '||Data_Precision||'.'||Data_Scale
                 End  AS
     Data_Len
From User_Tab_Columns
Where Table_Name like 'INC_%';


Column         Data      Data
Name           Type      Length
-------------- --------- ------
EMPLOYER_NAME  VARCHAR2     20
INCOME         NUMBER      7.2
INCOME_DATE    DATE          7
SEX            CHAR          1
BIRTHDATE      DATE          7

Global Temporary Table

CREATE GLOBAL TEMPORARY TABLE global_temp_table1
 ON COMMIT PRESERVE ROWS
 AS
--------------------------------------------------
Select field,
;
TRUNCATE TABLE GLOBAL_TEMP_Table1;
  DROP TABLE GLOBAL_TEMP_Table1;

Start Up

1st DOS Window:
     SQLPLUS /NOLOG
     CONNECT SYS/SYS AS SYSDBA
     STARTUP FORCE

2nd DOS Window:
     SQLPLUSW SCOTT/TIGER

To stop:
     SHUTDOWN IMMEDIATE

To shut down Oracle services:
     Control Panel
         Admin Tools
            Services (Manual - do everytime)

Pivot (9i)

This code will take data normally shown in columns and move it into rows. The Row_Number statement gives each ENAME an order number broken (or partitioned) into departments. The top-level query then takes the maximum value of each (which would be the name) and condenses it onto one line using the Group By.

The obvious thing to watch for here is you have to know how many possible items might need to be on a row, and code a "Max(Case" line for each.

(This is how you do this in 9i. I believe I've heard there is a new function in 10g -- I'll update this when I find it.)

   Select Deptno,
          Max(Case When Ord = 1 Then Ename Else '' End) Ename1,
          Max(Case When Ord = 2 Then Ename Else '' End) Ename2,
          Max(Case When Ord = 3 Then Ename Else '' End) Ename3
     From
         (
          Select Ename, Deptno,
                 Row_Number() Over (Partition By Deptno
                                        Order By Ename ) As Ord
            From Emp
         )
Group By Deptno;

    DEPTNO ENAME1     ENAME2     ENAME3
---------- ---------- ---------- ----------
        10 CLARK      KING       MILLER
        20 ADAMS      FORD       JONES
        30 ALLEN      BLAKE      JAMES

Generate A SQL Query

This query will generate the code for a second query that will look at tables with 'EMP' in the name and compile all the source code from User_Source. In this case, the last 'UNION' would be manually removed before running.

Select
               'Select Name, Text '       ||
               'From User_Source '        ||
               'Where Upper(Text) Like  ' ||
               '''%'||Table_Name||'%'' '  ||
               'Union '
               As
      New_Query
 From User_Tables
Where Table_Name Like '%EMP%';

New_Query
------------------------------------------------------------------------
Select Name, Text From User_Source Where Text Like '%Emp%' Union
Select Name, Text From User_Source Where Text Like '%Inc_Empls%' Union

HTML Output

If you want to view the HTML code generated, use Notepad, not Word. The spool file can have the following extensions: HTM, DOC or XLS. The prompt at the end is just to remind folks where to find the report generated.

SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF
SET TERM OFF

SPOOL EMP.HTM

Select *
from EMP;

SPOOL OFF

SET MARKUP HTML OFF ENTMAP OFF SPOOL OFF PREFORMAT OFF
SET TERM ON

PROMPT ** Report Completed. Use Mozilla to view your output: EMP.HTM **