SELECTSTATEMENT

When you enter the SQL SELECT statement through ISQL, the query result is displayed. You can scroll through the query result at the terminal and optionally print it.

Scope

ISQL only.

ISQL Syntax

SelectStatement; [ PA[USE]; ] [BrowseOption;] [ ... ]E[ND]

Parameters

SelectStatement

is an SQL SELECT statement. Refer to the ALLBASE/SQL Reference Manual for additional information on the SELECT statement.

PAUSE

is specified in a command file or command buffer to suspend processing of commands after a SELECT statement is executed. Example:

 

   SELECT * FROM PurchDB.Parts;

   PAUSE;

The query result is displayed and can be examined using a BrowseOption. When you enter END on the display, control returns to the command file or the command buffer.

If you do not use the PAUSE option, you must specify END.

BrowseOption

is specified in a command file or in response to the prompt beneath the query result display. Valid options are listed here:

{ U[P] D[OWN] L[EFT] R[IGHT] T[OP] B[OTTOM] N[EXT] P[RINT][NumberOfCopies] }

U[P]: scrolls backward by half the number of displayed rows, along with the previous rows, and preserves the column headings.
D[OWN]: scrolls forward by half the number of displayed rows, along with the previous rows, and preserves the column headings. (Pressing Return has the same effect as pressing D.)
L[EFT]: scrolls the display left 40 screen columns.
R[IGHT]: scrolls the display right 40 screen columns.
T[OP]: displays the first group of rows and the column headings.
B[OTTOM]: displays the last group of rows and the column headings.
N[EXT]: displays the next group of rows and the column headings.
PR[INT] NumberOfCopies: copies the query result to the device specified in the environment variable lpdest. If lpdest has not been set, the output goes to the system default destination. NumberOfCopies can be from 1 (the default) through 20.

E[ND]

terminates the query result display. If the PAUSE option is in effect, control returns to the command file. If the OUTPUT option of the SET command is set to a file other than isqlout, the entire query result is written to that file. If, however, you enter a slash (/) to terminate the query result display, only the rows displayed so far are written to the output file.

Description

The number of screen columns displayed is dependent on the screen size of your terminal.
A command entry prompt appears at the bottom of the display:
U[p], d[own], l[eft], r[ight], t[op], b[ottom], n[ext], pr[int] <n> , or e[nd]>
You enter browse options from this list to manipulate the display.
The number of rows already displayed appears beneath the query result as follows:
First n rows have been selected.
The number of rows selected is incremented whenever you enter DOWN. The total number of rows in the query result is displayed when you enter BOTTOM, or when the last row in the query result has been displayed:
Number of rows selected is n.
The maximum length of a row in a query result obtained through ISQL is the PAGEWIDTH option value of the SET command, which can be as large as 32767 bytes. If a row is longer than the PAGEWIDTH option value, then the following occurs:
- Column headings are truncated.
- CHAR or VARCHAR data is truncated after the PAGEWIDTH column.
- Numeric data is not displayed or printed unless all the data fits.
The following SET command options also affect the query result:
FRACTION
determines the number of decimal digits displayed for FLOAT and DECIMAL data.
NULL
determines the character ISQL displays to signify a null value.
OUTPUT
identifies the file to which the query result is sent. The query result is stored in this file, as well as displayed at the terminal. Each line in the file is as wide as the current PAGEWIDTH option value of the SET command. If the file is isqlout, it is removed when the END option is processed. If the file is named something other than isqlout, it is created. If the output file already exists, it is overwritten with the new output. You can access and manipulate the file with an editor.
Object names enclosed in double quotes cannot be split across lines.
When you execute the SELECT statement on a LONG column, the output device is displayed, not data. The output device will contain the actual data which was selected.
When you execute the SELECT statement on a BINARY or VARBINARY column, the hexadecimal representation is displayed.

Authorization

To issue the SELECT statement, you must have the authority defined in the ALLBASE/SQL Reference Manual.

Example

 

   isql=> SELECT * FROM System.Table;



   SELECT * from System.Table;

   --------------------+---------------------+--------------------+------+-----

   NAME                |OWNER                |DBEFILESET          |TYPE  |RTYPE

   --------------------+---------------------+--------------------+------+-----

   COUNTER             |SYSTEM               |SYSTEM              |     0|

   USER                |SYSTEM               |SYSTEM              |     0|

   TRANSACTION         |SYSTEM               |SYSTEM              |     0|

   CALL                |SYSTEM               |SYSTEM              |     0|

   ACCOUNT             |SYSTEM               |SYSTEM              |     0|   

   TABLE               |SYSTEM               |SYSTEM              |     1|

   COLUMN              |SYSTEM               |SYSTEM              |     1|

   INDEX               |SYSTEM               |SYSTEM              |     1|

   SECTION             |SYSTEM               |SYSTEM              |     1|

   DBEFILESET          |SYSTEM               |SYSTEM              |     1|

   DBEFILE             |SYSTEM               |SYSTEM              |     1|

   SPECAUTH            |SYSTEM               |SYSTEM              |     1|

   TABAUTH             |SYSTEM               |SYSTEM              |     1|

   COLAUTH             |SYSTEM               |SYSTEM              |     1|

   MODAUTH             |SYSTEM               |SYSTEM              |     1|

   GROUP               |SYSTEM               |SYSTEM              |     1|

   ----------------------------------------------------------------------------

   First 16 rows have been selected.

   U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd]>

r;

   

   -+--------------------+------+------+-----------+-----------+-----------+---

    |DBEFILESET          |TYPE  |RTYPE |NUMC       |NUMI       |NUMIC      |NR

   -+--------------------+------+------+-----------+-----------+-----------+---

    |SYSTEM              |     0|     3|          3|          0|          0|

    |SYSTEM              |     0|     3|          2|          0|          0|

    |SYSTEM              |     0|     3|          4|          0|          0|

    |SYSTEM              |     0|     3|          5|          0|          0|

    |SYSTEM              |     0|     3|          6|          0|          0|

    |SYSTEM              |     1|     0|         12|          0|          2|

    |SYSTEM              |     1|     0|         10|          0|          0|

    |SYSTEM              |     1|     0|         10|          0|          0|

    |SYSTEM              |     1|     0|          7|          0|          0|

    |SYSTEM              |     1|     0|          5|          0|          0|

    |SYSTEM              |     1|     0|          7|          0|          0|

    |SYSTEM              |     1|     0|          4|          0|          0|

    |SYSTEM              |     1|     0|         10|          0|          0|

    |SYSTEM              |     1|     0|          4|          0|          0|

    |SYSTEM              |     1|     0|          3|          0|          0|

    |SYSTEM              |     1|     0|          4|          0|          0|

   ----------------------------------------------------------------------------

   First 16 rows have been selected.

   U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd]>

e;