DB2 Server for VSE & VM: Interactive SQL Guide and Reference

Using SELECT Statements in a Routine

The use of SELECT statements in a routine (either an actual statement contained in the routine or a stored statement started by the routine) is slightly different from usage of the statements at a keyboard. SELECT statements contained in a routine do not cause query results to be displayed automatically at the terminal.

The SELECT statement results can be displayed at the terminal by placing an ISQL DISPLAY command in the routine at the desired location. This command can be placed anywhere between the SELECT statement and its associated END command. The DISPLAY command allows results to be formatted before they are displayed. It also lets you type display commands from the keyboard after the routine results are displayed.

To illustrate this process, type the following command:

   insert into routine (name,seqno,command) -
   values ('qreport',75,'display')

The QREPORT routine now appears as Figure 47.

Figure 47. Modified QREPORT Routine

NAME	SEQNO	COMMAND	REMARKS
QREPORT QREPORT QREPORT QREPORT QREPORT QREPORT QREPORT QREPORT QREPORT QREPORT	10 20 30 40 50 60 70 75 80 90	SELECT PROJNO, ACSTAFF - FROM PROJ_ACT - WHERE PROJNO = '&1' - OR ACTNO = &2 - ORDER BY PROJNO FORMAT GROUP PROJNO FORMAT SUBTOTAL ACSTAFF DISPLAY PRINT END	BEGIN:                 DONE!

Run the routine by typing:

   run qreport ('ad3100' 180)

The formatted query result is now displayed at your terminal. If required, you can alter it using additional FORMAT commands. The display resembles Figure 48.

Figure 48. Formatted Query Result from Running Routine QREPORT

+--------------------------------------------------------------------------------+
| PROJNO  ACSTAFF                                                                |
| ------  -------                                                                |
| AD3100     0.50                                                                |
|         -------                                                                |
| ******     0.50                                                                |
|                                                                                |
| AD3111     1.00                                                                |
|         -------                                                                |
| ******     1.00                                                                |
|                                                                                |
| AD3112     0.50                                                                |
|         -------                                                                |
| ******     0.50                                                                |
|                                                                                |
| AD3113     0.75                                                                |
|            1.00                                                                |
|            0.50                                                                |
|         -------                                                                |
| ******     2.25                                                                |
|                                                                                |
| MA2112     1.00                                                                |
|            1.00                                                                |
|         -------                                                                |
| ******     2.00                                                                |
+--------------------------------------------------------------------------------+

Type an END command to end the display of the query result and return to the routine. The routine issues its remaining commands, causing a report containing the modified query results to be printed.

EXERCISE 6 (Answers are in Appendix A, Answers to the Exercises, page ***.) Create a routine named EXER13 to: Select the ACTNO and ACTDESC columns from the ACTIVITY table. Provide a separation between columns consisting of three blanks. Display the results on the screen. Request three copies of the resulting report. End the query. Note: Remember to take the REMARKS column of the ROUTINE table into account if you have such a column.