BEGIN-SELECT
Syntax
BEGIN-SELECT[DISTINCT][-Cnn][-Bnn][-XP][-NR][-SORTnn] [-LOCK{RR|CS|RO|RL|XX}][-DBdatabase] [-DBconnectionstring] [LOOPS=nn][ON-ERROR=procedure[(arg1[,argi]...)]] {column} [&synonym] {expression &synonym} {[$columnname] &synonym = (char | number | date)} [SQR commands] FROM {table,...|[table:$tablename]} [additional SQL] [$variable] END-SELECTDescription
Begins a SELECT paragraph . A SELECT paragraph is the principal means of retrieving data from the database and printing it in a report. A SELECT paragraph must be inside a PROCEDURE or BEGIN-PROGRAM section.
Note that SELECT * FROM is not a valid SQR SQL statement. BEGIN-SELECT can be placed inside a BEGIN-PROGRAM section.
Parameters
The table describes the parameters:
Note:
The arguments can span multiple lines; however, the first character position cannot be used unless the continuation character terminated the previous line. If the first character position is used with arguments spanning multiple lines, the argument will be misconstrued as a Select column.
| Parameter | Description |
|---|---|
|
DISTINCT |
Specifies that duplicate rows be eliminated from your query. |
|
-Cnn |
(Oracle) Sets the context area size (buffer size for query) to larger or smaller than the default. This option is rarely needed. |
|
-Bnn |
(Oracle, ODBC) Sets the number of rows to retrieve at one time. This is for performance purposes only. Regardless of this setting, all rows are selected. The default, without using -B, is 10 rows. An overall setting for a program can be indicated on the SQR command line with -B, which can be overridden by a separate -B flag on each BEGIN-SELECT command. |
|
-DBconnectionstring |
(ODBC) Specifies the ODBC connection string for this SELECT paragraph only. A connection string has the following syntax: DSN=data_source_name[;keyword=value[;keyword=value [;...]]] This option enables you to combine data from multiple databases in one program. For example, a connection string for an Oracle database named ora8 might look like this: where DSN, UID, and PWD are keywords common to all drivers (representing name, user ID, and password, respectively). Connection string options are always separated by a semicolon (;). Other driver-specific options can be added to the connection string by driver-defined keywords. See your ODBC driver documentation for available options. |
|
LOOPS |
Specifies the number of rows to retrieve. After the specified number has been processed, the SELECT loop exits. |
|
ON-ERROR |
Declares a procedure to run if an error occurs due to incorrect SQL syntax. Error trapping should be used with dynamic query variables. SELECT paragraphs without dynamic variables are checked for errors before the program is processed and therefore do not require a special error procedure. Optionally, you can specify arguments to be passed to the ON-ERROR procedure. Arguments can be any variable, column, or literal. |
Example
In this example, duplicate rows are not selected for the city, state, and zip columns because of the distinct keyword. The numbers within parentheses accompanying city, state, and zip define the column positions of these rows. Column names cannot have spaces in front of them.
See SQR for PeopleSoft Developers: Using Column Variables in Conditions
begin-select distinct
city (1,1,30)
state (0,+2,2)
zip (1,+3,6)
from custlist order by city
end-select
In this example, the first two columns may be present when the statement is compiled. The column cust_id is declared to be a number. A runtime error occurs if the database table, as identified by the variable $table_name, declares it to be something other than a number.
begin-select loops=100
[$col_var_char] &col1=char
[$col_var_num] &col2=number
cust_id &id=number
from [$table_name]
[$where clause]
[$order_by_clause]
end-selectIn this example, the
embedded SQR command Do Print_Row is carried out once for each row.
begin-select distinct
city (1,1,30)
state (0,+2,2)
zip (1,+3,6)
Do Print_Row
from custlist order by city
end-select
See END-SELECT , EXIT-SELECT