Chapter Twenty One Producing Readable Output

1. Chapter Twenty One Producing Readable Output Objectives: Writing reports Page set up Page layout Queries with input Chapter 21: Report writing

2. Producing Readable Output

3. Summary of FormattingOutput Commands • COLUMN • TTITLE • BTITLE • BREAK • COMPUTE Chapter 21: Report writing

4. COLUMN: COLumn name | expression CLEar FORmat format HEAding Text JUStify LE | CE | R NEWLine NEW_VALUE continue Chapter 21: Report writing

5. COLUMN:(continued) NULl text NOPRInt PRInt WRApped TRUncated Chapter 21: Report writing

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 Chapter 21: Report writing

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 Major=‘COSC’ ORDER BY GPA; Chapter 21: Report writing

8. Heading Option: COLUMN Name HEADING ‘Student’’s Name’ COLUMN id HEADING ‘Student’’s ID’ COLUMN cr*grade HEADING ‘Score’ COLUMN name HEADING ‘Student’’s Name’ JUSTIFY LE COLumn Name HEAding ‘Name’ FORmat A10 COLumn Major FORmat A7 NULL ‘NO Major’ COL address HEADING ‘Addr’ FORMAT A7 TRUNC Chapter 21: Report writing

9. Column Example: COLUMN id HEADING ‘Stud ID’ FORMAT 9999 TRUNCATED Chapter 21: Report writing

10. Clear Setting COLumn Major CLEar COLumn Chapter 21: Report writing

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 Chapter 21: Report writing

12. Break on Format: BREAK ON column BREAK ON row BREAK ON page BREAK ON report SKIP n SKIP page CLEAR BREAK Chapter 21: Report writing

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 Chapter 21: Report writing

14. Check the Setting: COLUMN BREAK SHOW DEFINE Chapter 21: Report writing

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 Chapter 21: Report writing

16. Page Setup Display: SET TERMOUT ON SPOOL filename.lis SET TERMOUT OFF Heading separator: SET HEADSEP char SET HEADSEP ! Underlining: SET UNDERLINE OFF Chapter 21: Report writing

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 Chapter 21: Report writing

18. Page Setup Host: HOST myedit filename.SQL HOST list 2 Start: START filename.SQL Chapter 21: Report writing

19. Page Setup Summary: COLUMN COLUMN colname TTITLE BTITLE BREAK COMPUTE DEFINE SHOW HEADSEP SHOW LINESIZE SHOW PAGESIZE SHOW NEWPAGE Chapter 21: Report writing

20. Set Commands COLSEP [ |Text] FEEDBACK [OFF| ON| n] HEADING [OFF| ON] LINESIZE [n] PAGESIZE [n] PAUSE [OFF| ON| Text] TERMOUT [OFF| ON] Chapter 21: Report writing

21. Report Writing Example: Chapter 21: Report writing

22. Report Writing Example: CLEAR BREAK CLEAR COMPUTE COLUMN dept HEADING ‘Department’ FORMAT A12 COLUMN c_num HEADING ‘Course|No.’ FORMAT 999 COLUMN title HEADING ‘Course|Name’ FORMAT A13 COLUMN cr HEADING ‘Credit’ FORMAT 99 COLUMN D_start HEADING ‘Date|Started’ FORMAT DATE COLumn m_enrol HEAding ‘Max|Enrollment’ FORMAT 999 Chapter 21: Report writing

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 Name:’SKIP 2 - RIGHT xdate SKIP 1 - CENTER ‘List of Cources’ SKIP 1 Chapter 21: Report writing

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; Chapter 21: Report writing

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 Chapter 21: Report writing

26. Substitution Variables • Substitution variables to temporarily store values - & - & & - DEFINE and ACCEPT • Pass variable values between SQL statements • Dynamically alter headers and footers Chapter 21: Report writing

27. USING & VARIABLE: SELECT name, id, address FROM student WHERE id=&student_id; Enter value for student_id: Chapter 21: Report writing

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’ Chapter 21: Report writing

29. & Substitution Variables SELECT Name, ID, &Column_name FROM Student WHERE &condition ORDER BY ℴ Enter Value for Column_name: Enter Value for Condition: Enter Value for Order: Chapter 21: Report writing

30. && Substitution Variables Use a value of a variable more than one time SELECT Name, ID, &&Column_name FROM Student WHERE &Column_name=&NewName; Enter value for Column_name: Chapter 21: Report writing

31. SET VERIFY ON: SQL> SET VERIFY ON SQL> SELECT name, id, address 2 FROM student 3 WHERE id=&student_id; Enter value for student_id: 1111 Old 3: where id=&student_id; New 3: where id= 1111 Chapter 21: Report writing

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: Chapter 21: Report writing

33. Specifying Column Names, Expressions, and Text at Run Time • WHERE condition • ORDER BY clause • COLUMN expression • TABLE name • Entire SELECT statement Chapter 21: Report writing

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: Chapter 21: Report writing

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 Chapter 21: Report writing

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 variable=CAR • DEFINE variable • DEFINE • ACCEPT Chapter 21: Report writing

37. ACCEPT Command: • Create a customized prompt when accepting user input • Explicitly defines a NUMBER or DATE datatype variable • Hides user input for security Chapter 21: Report writing

38. ACCEPT Command: ACCEPT variable [ datatype] [FORMAT format][prompt text ] [HIDE] ACCEPT field PROMPT 'Input your Major:‘ SELECT * FROM student WHERE major=UPPER ('&field') Input your major: Chapter 21: Report writing

39. DEFINE and UNDEFINE • Use the UNDEFINE command to clear • Exit SQL*plus • To define variables for every session, modify your login.sql file Chapter 21: Report writing

40. Create a Variable DEFINE dept_name=COSC DEFINE dept_name SELECT * FROM faculty WHERE dept=UPPER ('&dept_name'); UNDEFINE dept_name Chapter 21: Report writing