Once a SQL Select statement is edited in the Wizard, the Select statement is permanently stored within the table layout (see notes below). To override the stored Select statement, you can use the Analyzer system variable DBSELECT in procedures or on the command line to specify an alternate Select statement prior to opening a relational table.
Note: When overriding the existing Select statement stored in the table layout, be sure to maintain the original select field list exactly. Do not modify the field list or the selected fields will not be displayed or processed correctly.
The Analyzer system variable for the select is stored as text. To assign a value to the DBSELECT system variable use the alternate ASSIGN command syntax:
ASSIGN DBSELECT:select_condition
For example:
ASSIGN DBSELECT:SELECT A.DEPTNO, A.DEPTNAME FROM DBADMIN."DEPARTMENT" A WHERE A.DEPTNAME > 'R'¿
This syntax ensures that whatever information is entered after the colon is stored exactly in the DBSELECT variable without the need to surround the entire select statement in double quotes.
Note: The DBSELECT variable requires proper usage of SQL syntax as used by the underlying SQL compliant database. For example, any string comparisons specified must use the correct string delimiter for the underlying database (usually single quotes).
Note: The DBSELECT variable is discarded upon closing a relational table. When used the DBSELECT variable must be specified for each relational table prior to opening the table or the underlying Select statement stored in the table layout will be used.
Note: Whenever you edit the system generated SQL Select command in the Data Definition Wizard, the Select statement is stored in the table layout. In this situation, the DBORDER, DBWHERE and DBCONDITION values are always ignored. For more information on manually viewing and overriding the stored SQL Select value see Table Options.
Once defined in the Wizard, the database Condition, Where and Order clause information is permanently stored in the table layout (see notes below). You can use Analyzer system variables in procedures or on the command line to specify or change the stored database Condition, Where and Order clause information prior to opening a live read of an ODBC or relational database table layout.
Note: Use of DB variables should only be used for live reads of ODBC or Relational data sources.
Note: The DB Where and Order variables should only reference native key fields. For more information on native key fields, see Native and Key Fields.
Note: For more information on manually viewing and overriding the stored Condition, Where and Order clauses see Table Options.
The value of these Analyzer system variables is stored as text so the value being assigned to these variables must be surrounded with quotes (typically double quotes). These system variables are specified as:
DBCONDITION=”join_condition”
DBWHERE=”where_ condition”
DBORDER=”column_name”
To clear the DBWHERE and DBORDER variables, enter:
DBWHERE=””
DBORDER=””
An alternate method of specifying a DB system variable, especially one containing a mix of single and double quotes, is to use the alternate ASSIGN command syntax:
ASSIGN DBWHERE:where_condition
This syntax ensures that whatever information is entered after the colon is stored exactly in the DB system variable without the need to surround the entire statement in double quotes.
Note: The DB system variables require proper usage of SQL syntax as used by the underlying SQL compliant database. For example, any string comparisons specified must use the correct string delimiter for the underlying database (usually single quotes).
Note: The DB system variables are discarded upon closing a relational table. When used these DB system variables must be specified for a relational table prior to opening the table (For example: when performing an Analyzer JOIN on two relational tables a DBORDER variable may need to be specified prior to opening each table if the ORDER clause is not already stored as required in each of the table layouts).
Tip: Analyzer also allows the DBORDER variable to specify descending order. To do this simply specify DESC (always in capital letters) following the column name (for example: DBORDER=”column_name DESC”). Do not use DESC when ordering a relational table for Analyzer’s JOIN command.