Calculations | Guerrilla Oracle: The Succinct Windows Perspective

The calculation verbs available are basically the same ones you use in programming: AVG , COMPUTE , COUNT , MAX , MIN , NUM , SUM , STD , and VAR .

To produce totals, use the BREAK ON command followed by a COMPUTE statement. In the next example you'll see simple totals. BREAK ON is used to tell Oracle when to skip a line or lines, or start a new page. BREAK ON can take four conditions:

BREAK ON <column name > skip 1 . When the value in the column changes, your report will skip a line.
BREAK ON ROW skip 1 . Every time the value of the row changes, your re- port will skip a line.
BREAK ON PAGE . Every time a page is filled, your report will jump to a new page.
BREAK ON REPORT <commands> . When the report ends, it will produce totals.

Using the BREAK ON command gets you into advanced reporting. With it you can produce reports , for example, that will list all the exams someone has taken; show the class dates, grades, hours, and then total hours; skip two lines; and go on to the next person. At the end, it can skip to a new page and then print total hours for everyone. As with any other programming tool, you will have to use BREAK ON , make mistakes, learn the nuances , and keep practicing until you're comfortable.

Finally, you can create pseudocolumns , make calculations using columns , and get pretty fancy with your reports. (A pseudocolumn is a column that re-ally isn't in the table, such as SYSDATE .) I refer you to further documentation on using the power of SQL*Plus reporting.

Let's look at our earlier report, now that it has some totals and uses two tables. We've added the student last name. We also wanted a count for the number of students who have taken the various tests available. Since we ran the first report a couple of minutes ago, several more students have taken various exams. Here's the new report:

Mon Jan 21			page 1
Test Id _______	Student Id __________	All Tests Taken Location ______________	Student Name ___________________
1	111111111	Science Lab	Cohen
	222222222	Main Lab	Williams
	333333333	CVS Office	Champagne
	444444444	High School	Klien
	555555555	Training Rm 300	Sanpletic
______	__________
5	5 ^[1]
2	111111111	Science Lab	Cohen
	222222222	CVS Office	Williams
	333333333	Off site	Champagne
	444444444	Lab 554	Klien
	555555555	Training Rm 50	Sanpletic
_____	__________
5	5
and so on . . .

^[1] Totals from the BREAK ON

And here's the SQL*Plus script for this report:

 rem rem            Tests Taken report rem    written by Jerome Engles rem rem  purpose: this is an ad hoc report to list all tests taken, rem  by type of test rem set headsep  ttitle 'All Tests Taken' btitle 'Guerrilla Exams' Column TEST_ID         heading 'TestId ' Column TEST_ID         format 999999999 Column STUDENT_ID      heading 'StudentId ' Column STUDENT_ID      format 999999999 Column LOCATION        heading 'Location' format a20 trunc Column L_Name          heading 'StudentName' format a30 trunc Break on TEST_ID skip 2  compute count of test_id on test_id  compute count of student_id on test_id Set LINESIZE    80 Set PAGESIZE    55 Set NEWPAGE     0 SPOOL C:\TEST.LST SELECT  TEST_ID, TEST_HISTORY.STUDENT_ID, LOCATION, L_NAME        FROM          TEST_HISTORY, STUDENTS          WHERE TEST_HISTORY.STUDENT_ID = STUDENTS.STUDENT_ID  ORDER BY TEST_ID; SPOOL OFF

(1) Notice the use of the head separator.

(2) Skip two lines after a break.

(3) Totals.

(4) Get the student last name from the student table by matching the student ID.

And here's what the report looks like if the user selects "1" for the test ID (only one student has taken that test so far):

All GUERRILLA EXAM Tests Taken As of APRIL, 15, 2002 SYSTEM;
Test Id _________	Student Id ____________	Location ______________	Student Name ______________
1 ________	111111111 ____________	Main Lab	Cohen
1	1

Here's one other important note on Oracle Reports: You can use variables . Suppose you wanted to do something like let your users enter which test they want to report on. Here's how you would do it: In your script, add the PROMPT and SELECT statements, and you will get just the test that your user specifies:

 ACCEPT  XTEST  PROMPT 'Enter Test ID: '  SELECT TEST_ID, TEST_HISTORY.STUDENT_ID, LOCATION, L_NAME  FROM          TEST_HISTORY, STUDENTS          WHERE             TEST_ID =  '&XTEST'   AND           TEST_HISTORY.STUDENT_ID = STUDENTS.STUDENT_ID        ORDER BY TEST_ID;

(1) Prompt the user.

(2) Use what was entered to qualify the SELECT statement.

(3) Note this rule: The variable must be in single quotes and start with an ampersand when it is used in a SELECT statement.

Now for the sake of completeness, and because you're probably scratching your head, here's the script modified to show the current date and user in the title. Notice that to do this, we had to:

Create two special columns with the tag NEW_VALUE
In our SELECT statement, format and print SYSDATE
Get the user ID and print it

 rem rem         Tests Taken report rem written by Jerome Engles rem rem purpose: this is an ad hoc report to list all tests taken, rem by type of test rem set headsep ttitle center       'All GUERRILLA EXAM Tests Taken As of ' skip 1 -        center       XTODAY ' ' XUSER ; SKIP 3 btitle 'Guerrilla Exams' column XTODAY NEW_VALUE XTODAY noprint format a1 trunc  column XUSER NEW_VALUE XUSER noprint format a1 trunc Column TEST_ID           heading 'TestId ' Column TEST_ID           format 999999999 Column STUDENT_ID        heading 'StudentId ' Column STUDENT_ID        format 999999999 Column LOCATION          heading 'Location' format a20 trunc Column L_Name            heading 'StudentName' format a30 trunc Break on TEST_ID skip 2 compute count of test_id on test_id compute count of student_id on test_id Set LINESIZE 80 Set PAGESIZE 55 ACCEPT XTEST PROMPT 'Enter Test ID: ' SPOOL C:\TEST.LST SELECT TEST_ID, TEST_HISTORY.STUDENT_ID, LOCATION, L_NAME,      TO_CHAR(SYSDATE, 'fmMONTH, DD, YYYY') XTODAY,  USER XUSER    FROM      TEST_HISTORY, STUDENTS        WHERE             TEST_ID = '&XTEST'             AND          TEST_HISTORY.STUDENT_ID = STUDENTS.STUDENT_ID          ORDER BY TEST_ID; SPOOL OFF;

(1) Notice the NEW_VALUE tags.

(2) Notice the formatting and use of SYSDATE and user.

What's going on? NEW_VALUE is an Oracle construct that lets you put a vari- able, such as SYSDATE , into ttitle or btitle . SYSDATE is the current date automatically maintained by Oracle. The format is DD-MMM-YYfor example, 04-JUN-03. (Remember the pseudocolumn I mentioned earlier, when I gave SYSDATE as an example?) NEW_VALUE moves what you take in the SELECT ... TO_CHAR ... statement into the NEW_VALUE column. In effect, it is a column that you make on the fly; it exists only as long as you run the script, and it really isn't part of any table. Notice that the name of the column, XTODAY , appears in several placesthat is, in the column definition and in the SELECT statements. Here's the output from the script:

All GUERRILLA EXAM Tests Taken As of ^[1] JANUARY, 25, 2002 WAIVER_DEV2; ^[2]
Test Id _______	Student Id ______________	Location ________________	Student Name ______________
1 ^[3]	111111111	Science Lab	Cohen
	222222222	Main Lab	Williams
	333333333	CVS Office	Champagne
	444444444	High School	Klien
_____	555555555	Training Rm 300	Sanpletic
_______	______________
5	5
		Guerrilla Exams

^[1] Notice the date.

^[2] The user is WAIVER_DEV2

^[3] Only Test 1 was selected for this run.

There's another SQL*Plus reporting tool that is very useful for quick and dirty scans of the database. For any mainframe or DOS programmers out there, this tool is similar to a line editor or the old Edlin tool. It's called the command-line editor , and you run it from the SQL> prompt. Remember, if you don't save your work, it disappears when you exit SQL!

Type in a standard SELECT command, such as

 1* SELECT STUDENT_ID, L_NAME, SSNUM from STUDENTS

Student Id __________	Student Name ________________________	SSNUM ______
123456789	Randolph	123456789
222222222	Williams	222222222
111111111	Cohen	111111111
333333333	Champagne	333333333
444444444	Klien	444444444
555555555	Sanpletic	555555555
666666666	Levin	666666666
777777777	Corsu	777777777

Now we want to add the first name. Just use the LIST command, followed by the CHANGE command:

 SQL> LIST  SELECT STUDENT_ID, L_NAME, SSNUM from STUDENTS SQL> CHANGE /SSNUM/SSNUM,F_NAME  1* SELECT STUDENT_ID, L_NAME, SSNUM,F_NAME from STUDENTS SQL> LIST   1* SELECT STUDENT_ID, L_NAME, SSNUM,F_NAME from STUDENTS

(1) List the commands first.

(2) Then use the CHANGE command. Use a special character to show the start and end of what you want to change. In this case it's the slash (/).

If your script is on multiple lines, just type "LIST xx", where "xx" is the line number you want to change:

 SQL> LIST   1 SELECT STUDENT_ID,   2 L_NAME,   3 SSNUM,F_NAME   4 from   5* STUDENTS

To add the state, edit just line 3:

 SQL> LIST 3  3* SSNUM,F_NAME

(3) List the line you want.

(Or just type in "3".)

 Now use "APPEND": SQL> append, state  3* SSNUM,F_NAME , state  SQL> list  1 SELECT STUDENT_ID, 2 L_NAME, 3 SSNUM,F_NAME , state 4 from 5* STUDENTS

(4) Append the new column.

(5) Check your work with another list.

Here are some other useful commands:

DEL lets you delete the current line or any other line that you specify.
INPUT lets you add a whole new line.
/ lets you run the commands after making changes.