If you select SQL as the Access Type option, do the following steps:
- In the SQL field, type the necessary SQL statement. JDBC BICNote: If you want to use the percent sign in the SQL statement, escape it by putting a backslash in front of each percent sign; for example,
Select * from P_ACTIVITY_PROPERTY where PROPERTY_KEY like '\%Password\%'resolves to
Select * from P_ACTIVITY_PROPERTY where PROPERTY_KEY like '%Password%'. - In the Result Set field, type the fully qualified name for the result set. This is the
location in the hash table where the results will be placed (e.g.,
<user>.<directoryName>).
To pull any piece of information from a hash table, enter the fully qualified hash table entry name.
Note: To specify a percent sign in the name itself, escape it by putting a backslash in front of it, for example: Pct\% resolves to Pct%To specify a backslash in the name itself, escape it by putting another backslash in front of it, for example, \\Results resolves to \Results.
- The Max Rows field specifies the maximum number of rows to be retrieved into the result set in the work item. You can enter a numeric value or a work item property key.
- For numeric values, if you specify a value that exceeds the number of rows, then it will return all the rows for that query.
Type "0" to return zero rows or "-1" to return all rows.
If the input is invalid, the Max Rows field adapts the default value specified in the JDBCBic.config file. Invalid values include negative numbers other than "-1", mixed decimals and non-numeric input.
In the JDBC.config file, the Max Rows default value of 100 is configurable.
- For work item property keys, type in the property name enclosed with the "%" sign. For example, if you will use the work item property name Personal_Age type in %Personal_Age% in the Max Rows field. The query will return the number of rows specified in the Personal_Age field in the viewer. If the work item property specified in the Max Rows field does not exist, the query will result to an error.