BEGIN-SELECT

Function

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.

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-SELECT

Arguments

Note:

Arguments can span multiple lines; however, do not use the first character position unless the continuation character terminated the previous line. Otherwise, the argument will be misconstrued as a SELECT column.

DISTINCT

Eliminates duplicate query rows.

-Cnn

(Oracle) Sets the context area size (buffer size for query) to larger or smaller than the default.

-Bnn

(ODBC, Oracle, Sybase) Sets the number of rows to retrieve at once. 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 Production Reporting command line with -B, which can be overridden by a separate -B flag on each BEGIN‑SELECT command.

-XP

(Sybase) Prevents the creation of stored procedures for the SELECT paragraph. When specified, Production Reporting generates a new SQL statement using the current value of any bind variables each time BEGIN-SELECT is executed.

Use -XP if you change variables frequently during execution and do not want Production Reporting to automatically create stored procedures. You can also use -XP if users do not have permission to create stored procedures. If you do not change variables frequently during execution, stored procedures may optimize program performance. In this case, do not use -XP.

-XP improves performance when using bind variables and dynamic query variables in the same query. Each time the dynamic query variable changes in value, a new stored procedure is created. If the dynamic query variable changes often and the query contains bind variables, you create many stored procedures if you do not use -XP.

-XP is available as a command-line flag.

-DBconnectionstring

(ODBC) The ODBC connection string for this SELECT paragraph only. A connection string has the following syntax:

DSN=data_source_name[;keyword=value[;keyword=value [;...]]]

Combines data from multiple databases in one program. For example, a connection string for an Oracle database named “ora8” might look like the following:

'DSN=ora7;UID=scott;PWD=tiger'

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 may be added to the connection string using driver-defined keywords. See your ODBC driver documentation for available options.

LOOPS

Number of rows to retrieve. After processing the specified number, the SELECT loop exits.

ON-ERROR

Procedure to execute if errors occur due to incorrect SQL syntax. Use error trapping with dynamic query variables. SELECT paragraphs without dynamic variables are checked for errors before programs are processed and do not require special error procedures.

You can optionally specify arguments to pass to the ON-ERROR procedure. Arguments can be any variable, column, or literal.

Note:

Production Reporting invokes ON-ERROR when it safely can. If Production Reporting can recover from a database error, users are given the chance to fix the error. If Production Reporting cannot recover from a database error, it will exit the program.

Description

BEGIN‑SELECT can be placed inside a BEGIN-PROGRAM section. Note that SELECT * FROM is not a valid Production Reporting SQL statement.

Note:

In Production Reporting DDO, you can name data source-specific aggregation functions in place of column names in a BEGIN-SELECT block. This shifts the processing burden from Production Reporting to the data' source host and usually improves performance. The aggregation function feature also makes it possible to use literals (such as empty column) and simple mathematical operations (such as 5+10) in place of column names.

In Production Reporting DDO-SAP, the TYPE=datatype qualifier used in a BEGIN-SELECT block is optional. When you report on data sources that provide adequate metadata (such as SAP), withholding the TYPE qualifier allows Production Reporting to generate code that is more efficient and portable than it would be otherwise.

You can use the intersect, union, and minus SQL operators in Production Reporting queries by adding them to the SQL statement that follows the FROM and WHERE clauses.

The SELECT list for the secondary SQL statement in the union, intersect, or minus query must match the data type, number of columns, and length of columns selected in the first query. If you select string expressions or literals, ensure that the lengths of the fields in both SELECT lists are the same.

Note that intersect and minus are not available with SYBASE's Transact SQL.

Enter the part of the SQL statement following the union, minus, or intersect clauses normally; that is, with commas between column names and without alias names, as shown below:

begin-select
  cust_num  (1,1) edit 099999
  co_name   (,9,30)
  name      (,+2,25)
  city      (,+2,18)
  state     (,+2,2)
  zip       (,+1) edit xxxxx-xxxx
  next-listing
  from customers where state in ('OH', 'IN', 'MI')
  union select cust_num, co_name, name, city, state, zip
  from prospects where state in ('OH', 'IN', 'MI')
  and first_contact >= '01-JAN-88'
  order by 2
end-select

Examples

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 can not have spaces in front of them. See “Column Variables” in Volume 1 of the Hyperion SQR Production Reporting Developer's Guide.

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, or may not, be present when the statement is compiled. The column cust_id is declared to be a number. A runtime error is produced 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-select