Remove Line Breaks from Data

If you have a column fornatted to hold the longest value, but data is still falling to a second line, it probably has a line break character in it. This is coded as 'CHR(10).' It can be replaced with a space to keep the value on one line as shown in the screen to the right.

Column Comments Format A80

Select Comments
 From Dict
Where Table_Name = 'ALL_REFRESH_DEPENDENCIES';

Select         Replace(Comments,Chr(10),' ')
              As
      Comments
 From Dict
Where Table_Name = 'ALL_REFRESH_DEPENDENCIES';

Sub and Grand Totals

Clear Screen
Column Comm   Format 99,999
Column Sal    Format 99,999
Column Deptno Format 99 New_Value xDeptNo Noprint 
Column Dname  Format A13
Column Job    Format A10
Set Newpage 2 Pagesize 60
Clear Computes
Clear Breaks

---------------------------------------------------------------------------
-- Use a compute for each field that you break on for which you want totals
-- Include more than one field in the 'of' without using commas.
----------------------------------------------------------------------------
Compute Min    Label 'Dpt Min $'      -
        Max    Label 'Dpt Max $'        Of Comm Sal On Dname

Compute Number Label 'Count'         - 
        Sum    Label 'Total $'          Of Comm Sal On Job

Compute Count  Label 'Tot Not Null #' -
        Number Label 'Tot Row Count'  -
        Avg    Label 'Tot Average $'  -
        Sum    Label 'Tot Sum $'       Of Comm Sal On Report

---------------------------------------------------------------------------
-- Mention those fields in the break statement
----------------------------------------------------------------------------
Break On Deptno Skip Page          -
      On Dname               Nodup -
      On Job    Skip 1       Dup   - 
      On Report Skip Page

---------------------------------------------------------------------------
-- Make sure they're sorted correctly
----------------------------------------------------------------------------
Select Dname, Deptno, Job, Ename, Comm, Sal
  From Emp
  Join Dept
 Using (Deptno)
 Where DeptNo in (20,30)
   And Job ^= 'MANAGER'
 Order By Deptno, Job;

Outer Join

Column Key_L  Format 999999 Heading Key-L
Column Key_R  Format 999999 Heading Key-R
Column Join_type Format A20 Heading " "
Break On Join_type

Drop Table Table_L;
Drop Table Table_R;

Create Table Table_L (Key_L Number(1));
Create Table Table_R (Key_R Number(1));

Insert Into Table_L Values (1);
Insert Into Table_L Values (2);

Insert Into Table_R Values (1);
Insert Into Table_R Values (3);

Select * from Table_L;
Select * from Table_R;

Prompt =========================================================
Prompt = Non-Ansi Joins
Prompt =========================================================

Define Join_type = "'Key_L = Key_R'"

Select &Join_Type as Join_type, KEY_L, KEY_R
  From Table_L L 
     , Table_R R
 Where L.Key_L = R.Key_R;

Define Join_type = "'Key_L(+) = Key_R'"
Select &Join_Type as Join_type, KEY_L,   KEY_R
  From Table_L L
     , Table_R R
 Where L.Key_L(+) = R.Key_R;

Prompt
Prompt =========================================================
Prompt = Ansi-Compliant Joins
Prompt =========================================================

Define Join_type = "'Join'"
Select &Join_Type as Join_type, KEY_L,   KEY_R
  From Table_L 
  Join Table_R 
   On ( Key_L = Key_R);

Define Join_type = "'Right Outer Join'"
Select &Join_Type as Join_type, KEY_L,   KEY_R
  From Table_L Right Outer 
  Join Table_R 
   On ( Key_L = Key_R);

Define Join_type = "'Full Outer Join'"
Select &Join_Type as Join_type, KEY_L,   KEY_R
  From Table_L Full Outer 
  Join Table_R 
   On ( Key_L = Key_R);

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;