Chapter Twenty One Producing Readable Output

1
Chapter Twenty One Producing Readable Output

• Objectives
• Writing reports
• Page set up
• Page layout
• Queries with input

2
Producing Readable Output
3
Summary of FormattingOutput Commands

• COLUMN
• TTITLE
• BTITLE
• BREAK
• COMPUTE

4
COLUMN

• COLumn name expression
• CLEar
• FORmat format
• HEAding Text
• JUStify LE CE R
• NEWLine
• NEW_VALUE

continue
5
COLUMN(continued)

• NULl text
• NOPRInt
• PRInt
• WRApped
• TRUncated

6
Formatting Option

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

7
Formatting Option

• COLUMN Name FORMAT A10
• COLUMN id FORMAT 9999
• COLUMN cr FORMAT 9
• COLUMN GPA FORMAT 9.99
• SELECT Name, id, cr, GPA
• FROM student
• WHERE MajorCOSC
• ORDER BY GPA

8
Heading Option

• COLUMN Name HEADING Students Name
• COLUMN id HEADING Students ID
• COLUMN crgrade HEADING Score
• COLUMN name HEADING
• Students Name JUSTIFY LE
• COLumn Name HEAding Name FORmat A10
• COLumn Major FORmat A7 NULL NO Major
• COL address HEADING Addr FORMAT A7 TRUNC

9
Column Example

• COLUMN id HEADING Stud ID
• FORMAT 9999
• TRUNCATED

10
Clear Setting

• COLumn Major
• CLEar COLumn

11
Break on

• BREAK ON id SKIP 2
• BREAK ON id DUPLICATE SKIP 2
• BREAK ON id NODUPLICATE
• BREAK ON Major ON ID
• BREAK ON REPORT PAGE ON dept PAGE

12
Break on

• Format
• BREAK ON column
• BREAK ON row
• BREAK ON page
• BREAK ON report
• SKIP n
• SKIP page
• CLEAR BREAK

13
Compute

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

14
Check the Setting

• COLUMN
• BREAK
• SHOW
• DEFINE

15
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

16
Page Setup

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

17
Page Setup

• Title
• TTITLE text variable
• TTITLE Information
• BTITLE Confidential
• TTITLE Left Page SQL.PNO
• Right Date SYSDATE Skip 1
• Center My Report Skip 3
• SQL.LNO
• SQL.PNO
• SQL.RELEASE
• SQL.SQLCODE
• SQL.USER

18
Page Setup

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

19
Page Setup

• Summary
• COLUMN
• COLUMN colname
• TTITLE
• BTITLE
• BREAK
• COMPUTE
• DEFINE
• SHOW HEADSEP
• SHOW LINESIZE
• SHOW PAGESIZE
• SHOW NEWPAGE

20
Set Commands

• COLSEP Text
• FEEDBACK OFF ON n
• HEADING OFF ON
• LINESIZE n
• PAGESIZE n
• PAUSE OFF ON Text
• TERMOUT OFF ON

21
Report Writing Example
22
Report Writing Example

• CLEAR BREAK
• CLEAR COMPUTE
• COLUMN dept HEADING Department FORMAT A12
• COLUMN c_num HEADING CourseNo. FORMAT 999
• COLUMN title HEADING CourseName FORMAT A13
• COLUMN cr HEADING Credit FORMAT 99
• COLUMN D_start HEADING DateStarted FORMAT DATE
• COLumn m_enrol HEAding MaxEnrollment FORMAT 999

23
Report Writing Example

• COL SYSDATE NEW_VALUE xdate NOPRINT
• FORMAT A1 TRUNC
• BREAK ON dept SKIP 2
• COMPUTE SUM OF cr ON dept
• --COMPUTE SUM OF cr ON dept REPORT
• TTITLE LEFT Your NameSKIP 2 -
• RIGHT xdate SKIP 1 -
• CENTER List of Cources SKIP 1

24
Report Writing Example

• BTITLE LEFT Report1.sql -
• RIGHT Page No SQL.PNO
• SELECT dept, c_num, title, cr, d_start.m_enroll
• FROM department
• WHERE dept IN (COSC, MATH)
• ORDER BY dept

25
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

26
Substitution Variables

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

27
USING VARIABLE

• SELECT name, id, address
• FROM student
• WHERE idstudent_id
• Enter value for student_id

28
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

29
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

30
Substitution Variables

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

31
SET VERIFY ON

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

32
Character and Date Values With Substitution
Variables

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

33
Specifying Column Names, Expressions, and Text at
Run Time

• WHERE condition
• ORDER BY clause
• COLUMN expression
• TABLE name
• Entire SELECT statement

34
Example

• 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

35
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

36
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 variableCAR
• DEFINE variable
• DEFINE
• ACCEPT

37
ACCEPT Command

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

38
ACCEPT Command

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

39
DEFINE and UNDEFINE

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

40
Create a Variable

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