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

Changing the Current SQL Statement

These statements can be corrected, altered and reused, and portions of them can be deleted.

Correcting Typing Errors in the Statement

Having the facility to change the current SQL statement lets you correct one containing typing errors. For example, enter the following statement (with activity misspelled):

   select actno -
   from adtivity -
   where actno > 100

This returns an error message stating that there is no table owned by you named ADTIVITY. Correct the error by entering the following ISQL command:

   change /adtivity/activity/

The slashes in CHANGE commands separate the data to be changed (between the first two slashes) from the new data to replace it (between the last two slashes). Always include a blank before the first slash and always enter the final slash. ISQL changes the statement at the first occurrence of the data you place between the first two slashes, so ensure that the data you want changed is the first occurrence of that data in the statement to be changed.

Note:

You do not have to use a slash to separate the data; any character except a blank can be used in its place. To change data that contains a slash, choose a character that does not occur in the data to be changed or in the new data.

Now enter the START command to process the changed SQL statement. If you have made no other mistakes, the query result for this SELECT statement is displayed on your display.

Altering and Reusing the Statement

You can use the CHANGE command to alter and then reuse the statement in the command buffer.

In Altering and Reusing SQL Lines, you used the RETRIEVE function to change the table name from EMPLOYEE to DEPARTMENT. Alternatively, you can query the EMPLOYEE table, end the query, and then type the following statement:

   change /employee/department/

This command changes the statement in the buffer. To perform the new statement, which queries the DEPARTMENT table, type:

   start

Notice that the RETRIEVE command uses lines of information from the command line buffer; the CHANGE command uses the statement from the command buffer.

Deleting Portions of the Statement

The CHANGE command can also be used to delete a portion of a current statement. For example, suppose you had selected the ACTNO, ACTKWD, and ACTDESC columns from the ACTIVITY table. After you end the query, you can delete the selection of the ACTKWD column table by typing:

   change /,actkwd//

By providing no replacement string (nothing between the second and third slashes), the characters matching the string between the first two slashes are effectively deleted from the statement. You can see the result of your changed query by using the START command.