Build SQL Prototype panel

The Build SQL Prototype panel allows you to interactively build SELECT, INSERT, UPDATE, and DELETE statements.

Db2 Administration Tool has four versions of this panel:

Build SQL SELECT Prototype (ADB21TSE) panel
Build SQL INSERT Prototype (ADB21TIN) panel
Build SQL UPDATE Prototype (ADB21TUP) panel
Build SQL DELETE Prototype (ADB21TDE) panel

Primary commands

Depending on which version of the panel is displayed, one or more of the following primary commands are available:

* (asterisk): Adds an asterisk to the query so that all columns are returned in the result.
COUNT(*): Adds the COUNT(*) function to the query. This function returns the number of selected rows as an integer.
COUNT_BIG(*): Adds the COUNT_BIG(*) function to the query. This function returns the number of selected rows as a decimal.
DEL: Opens the prototype panel to create a DELETE statement. DEL is not applicable to creating a view.
DRAW: Opens an ISPF editor with a query that includes all columns in the table. The data type for each column is included in comments. You can edit the statement from this editor.
EDIT: Opens an ISPF editor for you to edit the statement. Any changes that you make in the ISPF editor are not reflected in the Build SQL Prototype panel.
For SELECT statements, the difference between EDIT and DRAW is that EDIT does not automatically add all columns to the query.
FETCH: Adds FETCH FIRST ? ROWS ONLY to the DELETE statement. Replace the question mark (?) with an integer. This clause limits the number of rows that are deleted by the statement to the number that you specify.
INS: Opens the prototype panel to create a INSERT statement. INS is not applicable to creating a view.
QUOTE: Places quotation marks around column names.
RESET: Resets the query to how it looked when the Build SQL Prototype panel first opened. You can choose which clauses to reset (or to reset all of them) on a subsequent confirmation panel.
RUN: Executes the query.
SEL: Opens the prototype panel to create a SELECT statement.
UPD: Opens the prototype panel to create a UPDATE statement. UPD is not applicable to creating a view.

Line commands

Depending on which version of the panel is displayed, one or more of the following line commands are available:

S

Adds the column to the SELECT list so that is included (shown) in the result table.

SA

Adds the column to the SELECT list so that is included in the result table and indicates that you want the result set sorted in ascending order according to the values in this column. This column is added to the ORDER BY clause with the ASC keyword. SA is not applicable to creating a view.

SD

Adds the column to the SELECT list so that is included in the result table and indicates that you want the result set sorted in descending order according to the values in this column. This column is added to the ORDER BY clause with the DESC keyword. SD is not applicable to creating a view.

<value>

Inserts the specified value or expression into the column or updates the column with the specified value.

For example, if you are building an INSERT statement, on the Build SQL INSERT Prototype (ADB21TIN) panel, in the Column values column, enter the column values for the row that you want to insert:

Column values                      Column Name       
                                   *                 
---------------------------------> ------------------
100                                COL1           
b                                  COL2          
c                                  COL3          
4000                               COL4

If you are building an UPDATE statement, on the Build SQL UPDATE Prototype (ADB21TUP) panel, in the Expression or Predicate column, enter the new value for the column:

                                    Column Name       
 Expression or Predicate            *                 
 ---------------------------------> ------------------
 200                                COL1           
                                    COL2          
                                    COL3          
                                    COL4

The following line commands add the specified function to the query for the specified column:

AVG: Returns the average value for a numeric column.
COUNT: Returns the number of selected rows as an integer.
COUNT_BIG: Returns the number of selected rows as a decimal.
MAX: Returns the maximum value for a numeric column.
MIN: Returns the minimum value for a numeric column.
STDDEV: Returns the standard deviation for a numeric column.
SUM: Returns the sum of the selected columns.
VARIANCE: Returns the variance of a set of numbers from the selected columns.

The following line commands add predicates to the WHERE clause:

<oper><expr>

Adds a condition for the column.

<oper>: An operator. This value can be =, ¬=, >, >=, <, <=, <>, LIKE or IS.
<expr>: An expression. This value must be an alphanumeric.

<operator><value>

Adds a condition for the column.

<operator>: An operator. This value can be =, ¬=, >, >=, <, <=, <>, LIKE or IS.
<value>: Predicate, data value, or remainder of expression

Examples:

>=4
IS NOT NULL

OR <pred>

Adds the OR operator and a condition for the column.

<pred>: A predicate. This value can be an alphanumeric value or another valid operator, such as IN or BETWEEN.

Examples:

OR=1O
OR=x
OR IN(1,2,3,4,5)
OR BETWEEN s,t

IN list

Adds the IN operator and possible values for the column.

list: A list of values.

Examples:

IN x,y
IN('x','y')
IN 1,2,3,4,5,6

BETWEEN <expr>, <expr>

Adds the BETWEEN operator and a range of values for the column. You can also specify BTW instead of BETWEEN.

<expr>: An expression

Examples:

BTW x,y 
BETWEEN x AND y 
BTW nnn,ppp