Chapter Twelve Report Writing

1
Chapter TwelveReport Writing

• Objectives
• -Writing reports
• -Page set up
• -Page layout

2
Report Writing
3
COLUMN

• COLumn name expression
• FORmat format
• HEAding Text
• JUStify L C R
• NEWLine
• WRApped TRUncated

4
Column

• COLUMN Name HEADING Students Name
• COLUMN id HEADING Students ID
• COLUMN crgrade HEADING Score
• COLUMN name HEADING
• Students Name JUSTIFY L

5
Column

• COLUMN Name FORMAT A20
• COLUMN id FORMAT 9999
• COLUMN cr FORMAT 9
• COLUMN Gpa FORMAT 9.99
• FORMAT
• 9999
• 0999
• 990
• 9,999.99
• DATE
• A10

6
Column

• COLUMN Name TRUNCATED
• COLUMN id HEADING Stud ID
• FORMAT 9999

7
Break on

• BREAK ON id SKIP 2
• BREAK ON id DUPLICATE SKIP 2
• BREAK ON id DUPLICATE
• SKIP 2 ON REPORT

8
Compute

• COMPUTE SUM OF gpa ON id
• COMPUTE SUM LABEL Total OF gpa ON id
• COMPUTE AVG OF a, b, c, d ON e, f
• AVG
• COUNT
• MAX
• MIN
• STD
• VAR

9
Page Setup

• Line size
• SET LINESIZE 75
• Page size
• SET PAGESIZE 60
• Blank lines at the top
• SET NEWPAGE 3
• Writing into a file
• SPOOL filename.lis
• SPOOL Gstudent.lis
• SPOOL OFF

10
Page Setup

• Display
• SET TERMOUT ON
• SPOOL filename.lis
• SET TERMOUT OFF
• Heading separator
• SET HEADSEP char
• SET HEADSEP !

11
Page Setup

• Title
• TTITLE
• BTITLE
• TTITLE Left Page SQL.PNO
• Right Date SYSDATE Skip 1
• Center My Report Skip 3

12
Page Setup
SQL environment COLUMN COLUMN
colname TTITLE BTITLE BREAK COMPUTE DEFINE SH
OW HEADSEP SHOW LINESIZE SHOW PAGESIZE SHOW
NEWPAGE

• Host
• HOST myedit filename.SQL
• Start
• START filename.SQL

13
Login File

• Login.sql
• prompt login.sql loaded
• set feedback off
• set sqlprompt
• set sqlnumber off
• set numwidth 5
• set pagesize 24
• set linesize 79

14
Substitution Variables

• Substitution variables to temporarily store
  values
• -
• -
• - DEFINE and ACCEPT
• Pass variable values between SQL statements
• Dynamically alter header and footers

15
Substitution Variables

• User provides the input to the query
• SELECT Name, GPA, ID
• FROM Student
• WHERE UPPER(Major) Major_Input
• Enter Value for Major_Input COSC

16
Substitution Variables

• SELECT Name, ID, Column_name
• FROM Student
• WHERE condition
• ORDER BY order
• Enter Value for Column_name
• Enter Value for Condition
• Enter Value for Order

17
Substitution Variables

• Use a value of a variable more than one time
• SELECT Name, ID, Column_name
• FROM Student
• WHERE Column_name
• Enter value for Column_name

18
SET VERIFY ON

• SET VERIFY ON
• SELECT name, id, address
• FROM student
• WHERE idstudent_id
• Enter value for student_id
• Old where idstudent_id
• New when id

19
Character and Date Values With Substitution
Variables

• SELECT name, id, b_date
• FROM student
• WHERE b_date 'new_birthdate'
• Enter value for new_birthdate

20

• SELECT name, id, rank, column_name
• FROM faculty
• WHERE condition
• ORDER BY order_column
• Enter value for column_name
• Enter value for condition
• Enter value for order_column

21
Defining User Variables

• You can pre-define variables using
• DEFINE
• Create a user variable of CHAR data type
• ACCEPT
• Read user input and store it in a variable

22
Defining User Variables

• If you need to predefine a variable that includes
  spaces, you need to enclose the value within
  single quotation marks, when using the DEFINE
  command.
• DEFINE variablevalue
• DEFINE variable
• DEFINE
• ACCEPT

23
ACCEPT Command

• Create a customized prompt when accepting user
  input
• Explicitly defines a NUMBER or DATE datatype
  variable
• Hides user input for security

24
ACCEPT Command

• ACCEPT variable datatype
• FORMAT format
• prompt text HIDE
• ACCEPT field PROMPT 'Input your Major
• SELECT
• FROM student
• WHERE majorUPPER ('field')
• Input your major

25
DEFINE and UNDEFINE

• Use the UNDEFINE command to clear
• Exit SQLplus
• To define variables for every session, modify
  your login.sql file

26
DEFINE

• DEFINE dept_nameCOSC
• DEFINE dept_name
• SELECT
• FROM faculty
• WHERE deptUPPER ('dept_name')
• UNDEFINE dept_name

27
Example

• SQLgt SET PAGESIZE 40
• SQLgt SET LINESIZE 60
• SQLgt SET FEEDBACK OFF
• SQLgt Define MyName 'Dr. Chitsaz'
• SQLgt TTITLE LEFT 'MyName ' MyName -
• RIGHT SYSDATE Skip1
• CENTER List Of Courses'
• SQLgt COLUMN dept HEADING 'Deaprtment' format A10
• SQLgt COLUMN C_Num HEADING 'CourseNumber' format
  9999
• SQLgt COLUMN title HEADING 'CourseName' FORMAT A5
• SQLgt COLUMN Cr HEADING 'Credit' FORMAT 9

28
Example

• SQLgt COLUMN S_DATE HEADING 'Date Started' SQLgt
  COLUMN Max HEADING 'Max Enrollment' format
  999SQLgt BREAK ON dept
• SQLgt COMPUTE SUM LABLE 'Total' OF Cr ON DeptSQLgt
  BREAK ON REPORT
• SQLgt COMPUTE sum LABLE 'Grand Total'
• SQLgt select DEPT, C_Num, TITLE, CR, S_DATE, Max
  2 FROM Course 3 ORDER BY DEPt