This section provides an alphabetical reference for the SQL generation parameters listed in the Parameter page of the Universe Parameters dialog box in Designer. These are SQL parameters that are common to most data access drivers. Each parameter is valid for the universe in which it is set. Other RDBMS specific and connection parameters are listed in the data access parameter (PRM) file for the target data access driver. Refer to the Data Access guide for a reference to the parameters in the PRM file.
ANSI92 = Yes|No
| Options | Description |
|---|---|
| Values | Yes|No |
| Default | No |
| Description | Specifies whether the SQL generated complies to the ANSI92
standard. Yes: Enables the SQL generation compliant to ANSI92 standard. No: SQL generation behaves according to the PRM parameter OUTER_JOIN_GENERATION. |
AUTO_UPDATE_QUERY = Yes|No
| Options | Description |
|---|---|
| Values | Yes|No |
| Default | Yes |
| Description | Determines what happens when an object in a query is not available
to a user profile. Yes:Query is updated and the object is removed from the query. No:Object is kept in the query. |
BLOB_COMPARISON = Yes|No
| Options | Description |
|---|---|
| Values | Yes|No |
| Default | No |
| Can be edited? | No |
| Description | Species if a query can be generated with a DISTINCT statement
when a BLOB file is used in the SELECT statement. It is related
to the setting No Duplicate Row in the query properties. Yes: The DISTINCT statement can be used within the query. No: The DISTINCT statement cannot be used within the query even if the query setting No Duplicate Row is on. |
BOUNDARY_WEIGHT_TABLE = Integer 32bits [0-9]
| Options | Description |
|---|---|
| Values | Integer 32bits [0-9] |
| Default | -1 |
| Description | Allows you to optimize the FROM clause when tables have many
rows. If the table size is greater than the entered value, the
table is declared as a subquery: FROM (SELECT col1, col2,...., coln FROM Table_Name WHERE simple condition class). A simple condition is defined as not having a subquery, and not having EXCEPT or BOTH operators. |
| Limitations | Optimization is not implemented when:
|
COLUMNS_SORT = Yes|No
| Options | Description |
|---|---|
| Values | YES Columns are displayed in alphabetical order NO Columns are displayed in the order they were retrieved from the database |
| Default | No |
| Description | Determines the order that columns are displayed in tables in the Structure pane. |
COMBINE_WITHOUT_PARENTHESIS=No
| Options | Description |
|---|---|
| Values | YES Removes the parentheses. NO Leaves the parentheses. |
| Default | No |
| Description | Specifies whether or not to encapsulate a query with parentheses when it contains UNION, INTERSECT or MINUS operators. Used with RedBrick. |
COMBINED_WITH_SYNCHRO = Y|N
| Options | Description |
|---|---|
| Values | Yes|No |
| Default | No |
| Description | Specifies whether to allow a query to execute that contains
UNION, INTERSECTION, or EXCEPT operators, and whose objects in
each subquery are incompatible. Yes: Specifies that you do allow a query to execute that contains UNION, INTERSECTION and EXCEPT operators, and whose objects in each subquery are incompatible. This type of query generates synchronization (two blocks in the report). No: Specifies that you do not allow a query to execute that contains UNION, INTERSECTION and EXCEPT operators, and whose objects in each subquery are incompatible. When the query is executed the following error message is displayed: “This query is too complex. One of the subqueries contains incompatible objects. This is the default value. |
CORE_ORDER_PRIORITY = Yes|No
| Options | Description |
|---|---|
| Values | Yes|No |
| Default | Yes |
| Description | Specifies in which order you want classes and objects to be
organized once two or more universes are linked in Designer. Yes: Specifies that classes and objects follow the order defined in the kernel universe. No: Specifies that classes and objects follow the order defined in the derived universe. This is the default value. |
CORRECT_AGGREGATED_CONDITIONS_IF_DRILL = Yes|No
| Options | Description |
|---|---|
| Values | Yes|No |
| Default | No |
| Description | Specifies whether Web Intelligence can aggregate asures in
queries and conditions. Yes:Web Intelligence can aggregate measures separately in the main query and the condition, if the query is drill enabled. No:Web Intelligence cannot aggregate measures separately in the main query and the condition, if the query is drill enabled. |
CUMULATIVE_OBJECT_WHERE = Y|N
| Options | Description |
|---|---|
| Values | Yes|No |
| Default | No |
| Description | Specifies the order of WHERE clauses that have the AND connective. Yes: Specifies that WHERE clauses that have the AND connective are set at the end of the condition. No: Specifies that WHERE clauses follow standard SQL syntax. Example: If the condition is find all French clients different from John or American cities different from New York, the SQL is then: WHERE (customer.first_name <> 'John') OR (city.city <> 'New York') AND customer_country.country = 'France' AND city_country.country = 'USA' |
DECIMAL_COMMA = Yes|No
| Options | Description |
|---|---|
| Values | Yes|No |
| Default | Yes |
| Description | Specifies that Business Objects products insert a comma as
a decimal separator when necessary. Yes: Business Objects products insert a comma as a decimal separator when necessary. No: Business Objects products do not insert a comma as a decimal separator. This is the default value. |
DISTINCT_VALUES = GROUPBY|DISTINCT
| Options | Description |
|---|---|
| Values | GROUPBY|DISTINCT |
| Default | DISTINCT |
| Description | Specifies whether SQL is generated with a DISTINCT or GROUPBY clause in a list of values and Query pane when the option “Do not retrieve duplicate rows” is active. DISTINCT: The SQL is generated with a DISTINCT clause, for example; SELECT DISTINCT cust_name FROM Customers GROUPBY: The SQL is generated with a GROUP BY clause, for example; SELECT cust_name FROM Customers GROUPBY cust_name |
END_SQL = String
| Options | Description |
|---|---|
| Values | String |
| Default | <empty string> |
| Description | The statement specified in this parameter is added at the end of each SQL statement. |
| Example | For IBM DB2 databases, you can use the following: END_SQL=FOR SELECT ONLY The server will read blocks of data much faster. |
EVAL_WITHOUT_PARENTHESIS = Yes|No
| Options | Description |
|---|---|
| Values | Yes|No |
| Default | No |
| Description | By default, the function @Select(Class\object) is replaced
by the Select statement for the object <Class\object> enclosed
within brackets. For example, when combining two @Select statements, @select(objet1) *@select(objet2). If the SQL(objet1) = A-B and SQL(objet2) =C, then the operation is (A-B) * (C). You avoid the default adding of brackets by setting EVAL_WITHOUT_PARENTHESIS = Yes. The operation is then A - B * C. Yes: Brackets are removed from the Select statement for a function @Select(Class\object) No: Brackets are added around the Select statement for the function @Select(Class\object). |
FILTER_IN_FROM = Yes|No
| Options | Description |
|---|---|
| Values | Yes|No |
| Default | No |
| Description | Determines if query conditions are included in the FROM Clause.
This setting is only applicable if the other universe parameter setting ANSI92 is set to Yes. Yes: When editing an outer join, the default behavior property selected in the drop down list box of the Advanced Join properties dialog box in Designer, is set to "All objects in FROM". No:When editing an outer join, the default behavior property selected in the drop down list box of the Advanced Join properties dialog box in Designer is set to "No object in FROM". |
FIRST_LOCAL_CLASS_PRIORITY = Yes|No
| Options | Description |
|---|---|
| Values | Yes|No |
| Default | No |
| Description | Only taken into account when CORE_ORDER_PRIORITY=Yes. Yes: Classes in derived universe are placed first. No: Objects and sub classes from derived universe appear after those of the core universe. |
FORCE_SORTED_LOV = Yes|No
| Options | Description |
|---|---|
| Values | Yes|No |
| Default | No |
| Description | Retrieves a list of values that is sorted. Yes: Specifies that the list of values is sorted. No: Specifies that the list of values is not sorted. |
MAX_INLIST_VALUES = 99]
| Options | Description |
|---|---|
| Values | Integer: min 0, max 256 |
| Default | 99 |
| Description | Allows you to increase to 256 the number of values you may
enter in a condition when you use the IN LIST operator. 99: Specifies that you may enter up to 99 values when you create a condition using the IN LIST operator. This is the default value. 256: Specifies that you may enter up to 256 values when you create a condition using the IN LIST operator. 256 is the maximum authorized value you may enter. |
Parameter is not listed by default. You must add the parameter manually to the lust and set a value. See Editing SQL Generation Parameters.
PATH_FINDER_OFF= Y|N
| Options | Description |
|---|---|
| Values | Y|N |
| Default | No default. You must manually enter the parameter. |
| Description | Used for HPIW because the join generation is done by the database. Y: Joins are NOT generated in the query. N: Joins are generated in the query. This is the default behavior. |
REPLACE_COMMA_BY_SEPARATOR= Yes|No
| Options | Description |
|---|---|
| Values | Yes|No |
| Default | Yes |
| Description | In previous versions of Designer, a comma could be used to
separate multiple fields in an object Select statement. The comma
was treated as a concatenation operator. For universes that already
use the comma in this way you can set REPLACE_COMMA_BY_SEPARATOR
to No to keep this behavior. In the current version of Designer,
this parameter is set to Yes by default, so that a expressions
using a comma in this way are automatically changed to use concatenation
syntax. Yes: Comma is replaced by the concatenation expression when multi field object is found. No: Keep the comma as it is. |
SHORTCUT_BEHAVIOR = Global|Successive
| Options | Description |
|---|---|
| Values | Global|Successive |
| Default | Successive |
| Description | Specifies how shortcut joins are applied. This parameter was
formerly listed as GLOBAL_SHORTCUTS in the PRM files. The values
have been changed to Global for Yes, and Successive for No. Global: Specifies that shortcut joins are considered one by one. A shortcut join is applied only if it really bypasses one or more tables, and if it does not remove a table from the join path used by a following shortcut join. Successive: Specifies that all shortcut joins are applied. Note: If it generates a Cartesian product, no shortcut joins are applied. |
THOROUGH_PARSE = Yes|No
| Options | Description |
|---|---|
| Values | Yes|No |
| Default | No |
| Description | Specifies the methodology used for default Parsing in the Query
pane and individual object parsing. Yes: PREPARE, DESCRIBE, and EXECUTE statements are used to parse SQL for objects. Prepare+DescribeCol+Execute No: PREPARE and DESCRIBE statements are used to parse SQL for objects. |
TRUST_CARDINALITIES = Yes|No
| Options | Description |
|---|---|
| Values | Yes|No |
| Default | No |
| Description | Allows you to optimize the SQL in case of inflated results. Yes: For queries that include a measure, all conditions that inflate the measure and do not appear in the Result Objects, are transformed to sub queries to ensure that tables that may return false results for the measure are not included in the query. No: No optimization is implemented. |
UNICODE_STRINGS = Yes|No
| Options | Description |
|---|---|
| Values | Yes|No |
| Default | No |
| Description | Specifies whether the current universe can manipulate Unicode
strings or not. Only applies to Microsoft SQL Server and Oracle
9. If the database character set in the SBO file is set as Unicode,
then it is necessary to modify the SQL generation to handle specific
Unicode column types like NCHAR and NVARCHAR. Yes: Conditions based on strings are formatted in the SQL according to the value for a parameter UNICODE_PATTERN in the PRM file, for example for MS SQL Server (sqlsrv.prm): UNICODE_PATTERN=N$ The condition Customer_name='Arai ' becomes Customer_name=N'Arai'. Note: When you create a prompt with @Prompt syntax based on Unicode value, the datatype should be 'U' not 'C' No: All conditions based on strings are formatted in the standard SQL. For example the condition Customer_name='Arai ' remains Customer_name='Arai' |