Pages

Wednesday, March 23, 2011

Report Formating

Changing a Column Heading

COLUMN LAST_NAME        HEADING 'LAST NAME'
COLUMN SALARY           HEADING 'MONTHLY SALARY'
COLUMN COMMISSION_PCT   HEADING COMMISSION
SELECT LAST_NAME, SALARY, COMMISSION_PCT  FROM EMP_DETAILS_VIEW
WHERE JOB_ID='SA_MAN';
LAST NAME                 MONTHLY SALARY COMMISSION
------------------------- -------------- ----------
Russell                            14000         .4
Partners                           13500         .3

Splitting a Column Heading
COLUMN SALARY HEADING 'MONTHLY|SALARY'
COLUMN LAST_NAME HEADING 'LAST|NAME'
LAST                         MONTHLY
NAME                          SALARY COMMISSION
------------------------- ---------- ----------
Russell                        14000         .4
Partners                       13500         .3
Setting the Underline Character
SET UNDERLINE =
/
LAST                         MONTHLY
NAME                          SALARY COMMISSION
========================= ========== ==========
Russell                        14000         .4
Partners                       13500         .3

Formatting a NUMBER Column
COLUMN SALARY FORMAT $99,990
LAST                       MONTHLY
NAME                        SALARY COMMISSION
------------------------- -------- ----------
Russell                    $14,000         .4
Partners                   $13,500         .3

Formatting a Character Column
LAST  MONTHLY
NAME   SALARY COMMISSION
---- -------- ----------
Russ  $14,000         .4
ell

Resetting Column Display Attributes to their Defaults
CLEAR COLUMNS

Suppressing Duplicate Values in a Break Column
BREAK ON DEPARTMENT_ID;
BREAK ON DEPARTMENT_ID SKIP 1

SELECT DEPARTMENT_ID, LAST_NAME, SALARY  
FROM EMP_DETAILS_VIEW
WHERE SALARY > 12000 
ORDER BY DEPARTMENT_ID;

DEPARTMENT_ID LAST_NAME                     SALARY
------------- ------------------------- ----------
           20 Hartstein                      13000

           80 Russell                        14000
              Partners                       13500

           90 King                           24000
              Kochhar                        17000

Combining Spacing Techniques
BREAK ON DEPARTMENT_ID SKIP PAGE ON JOB_ID SKIP 1
TTITLE COL 35 FORMAT 9 'Page:' SQL.PNO



Page: 1
DEPARTMENT_ID JOB_ID     LAST_NAME                     SALARY
------------- ---------- ------------------------- ----------
           20 MK_MAN     Hartstein                      13000

Page: 2
DEPARTMENT_ID JOB_ID     LAST_NAME                     SALARY
------------- ---------- ------------------------- ----------
           80 SA_MAN     Russell                        14000
                         Partners                       13500
Computing and Printing Subtotals
break on DEPARTMENT_ID page  nodup on JOB_ID skip 1 nodup
COMPUTE SUM OF SALARY ON DEPARTMENT_ID

DEPARTMENT_ID JOB_ID     LAST_NAME                     SALARY
------------- ---------- ------------------------- ----------
           20 MK_MAN     Hartstein                      13000
************* **********                           ----------
sum                                                     13000

DEPARTMENT_ID JOB_ID     LAST_NAME                     SALARY
------------- ---------- ------------------------- ----------
           80 SA_MAN     Russell                        14000
                         Partners                       13500

************* **********                           ----------
sum                                                     27500
Example
COLUMN DUMMY NOPRINT;
COMPUTE SUM OF SALARY ON DUMMY;
BREAK ON DUMMY SKIP 1;
SELECT DEPARTMENT_ID DUMMY,DEPARTMENT_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW WHERE SALARY>12000 ORDER BY DEPARTMENT_ID;
DEPARTMENT_ID LAST_NAME                     SALARY
------------- ------------------------- ----------
           20 Hartstein                      13000
                                        ----------
                                             13000

           80 Russell                        14000
           80 Partners                       13500
                                        ----------
                                             27500

           90 King                           24000
           90 Kochhar                        17000
           90 De Haan                        17000
                                        ----------
                                             58000
Computing Multiple Summary Lines on the Same Break Column
BREAK ON DEPARTMENT_ID
COMPUTE AVG SUM OF SALARY ON DEPARTMENT_ID
SELECT DEPARTMENT_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW WHERE DEPARTMENT_ID = 30
ORDER BY DEPARTMENT_ID, SALARY;
DEPARTMENT_ID LAST_NAME                     SALARY
------------- ------------------------- ----------
           30 Colmenares                      2500
              Himuro                          2600
              Tobias                          2800
              Baida                           2900
              Khoo                            3100
              Raphaely                       11000
*************                           ----------
avg                                           4150
sum                                          24900
Listing and Removing COMPUTE Definitions
CLEAR BREAKS
CLEAR COMPUTES
Setting the Top and Bottom Titles and Headers and Footers
TTITLE ON
BTITLE ON
TTITLE CENTER - "ACME SALES DEPARTMENT PERSONNEL REPORT"
BTITLE CENTER "COMPANY CONFIDENTIAL"
REPHEADER PAGE CENTER 'PERFECT WIDGETS'
RIGHT 'SALES DEPARTMENT' SKIP 2
SET LINESIZE 60
ACME SALES DEPARTMENT PERSONNEL REPORT
                            PERFECT WIDGETS

                         COMPANY CONFIDENTIAL

                ACME SALES DEPARTMENT PERSONNEL REPORT
DEPARTMENT_ID LAST_NAME                     SALARY
------------- ------------------------- ----------
           30 Colmenares                      2500
           30 Himuro                          2600
           30 Tobias                          2800
           30 Baida                           2900
           30 Khoo                            3100
           30 Raphaely                       11000

                         COMPANY CONFIDENTIAL

Setting Page Dimensions

To set the number of lines between the beginning of each page and the top title, use the NEWPAGE variable of the SET command:
SET NEWPAGE number_of_lines
To set the number of lines on a page, use the PAGESIZE variable of the SET command:
SET PAGESIZE number_of_lines
You can change the line width using the LINESIZE variable of the SET command:
SET LINESIZE number_of_characters

SET PAGESIZE 66
SET NEWPAGE 0
SET LINESIZE 70
Creating a Flat File
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF SPOOL OFF
To store the results of a query in a file—and still display them on the screen—enter the SPOOL command in the following form:

SPOOL file_name
Sending Query Results to a Printer First, use EDIT to create the script with your operating system text editor.
EDIT EMPRPT
Next, enter the following commands into the file, using your text editor:
SPOOL TEMP
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN DEPARTMENT_ID HEADING DEPARTMENT
COLUMN LAST_NAME HEADING 'LAST NAME'
COLUMN SALARY HEADING 'MONTHLY SALARY' FORMAT $99,999

BREAK ON DEPARTMENT_ID SKIP 1 ON REPORT
COMPUTE SUM OF SALARY ON DEPARTMENT_ID
COMPUTE SUM OF SALARY ON REPORT

SET PAGESIZE 24
SET NEWPAGE 0
SET LINESIZE 70

TTITLE CENTER 'A C M E  W I D G E T' SKIP 2 -
LEFT 'EMPLOYEE REPORT' RIGHT 'PAGE:' -
FORMAT 999 SQL.PNO SKIP 2
BTITLE CENTER 'COMPANY CONFIDENTIAL'

SELECT DEPARTMENT_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000
ORDER BY DEPARTMENT_ID;

SPOOL OFF
If you do not want to see the output on your screen, you can also add SET TERMOUT OFF to the beginning of the file and SET TERMOUT ON to the end of the file. run the script EMPRPT:
@EMPRPT
SQL*Plus displays the output on your screen (unless you set TERMOUT to OFF), and spools it to the file TEMP:
A C M E  W I D G E T

EMPLOYEE REPORT                                              PAGE: 1

DEPARTMENT LAST NAME                 MONTHLY SALARY
---------- ------------------------- --------------
        20 Hartstein                        $13,000
**********                           --------------
sum                                         $13,000

        80 Russell                          $14,000
           Partners                         $13,500
**********                           --------------
sum                                         $27,500

        90 King                             $24,000
           Kochhar                          $17,000
           De Haan                          $17,000
**********                           --------------
sum                                         $58,000

                                     --------------
sum                                         $98,500
                         COMPANY CONFIDENTIAL
Ref: http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b12170/ch7.htm

No comments:

Post a Comment