Creating an SQL Statement

Using an SQL development tool, you can now create your SQL statement.

Following the SQL coding standards listed in SQL Coding Guidelines, write your SQL making sure that all fields that are required for the report are included. Check the result set for duplicate records and proper grouping. When you are done, you will be able to cut and paste this SQL statement into reports.

You need to remember to use the RV_ views when you create your SQL statement. Each RV_ view contains an OpenPages Security column, OP_FLAG_READ_ACCESS, with these possible values:
Y
If the user running the report has read access to the record.
N
If the user running the report does not have read access to the record.
For example, if you wantIBM OpenPages® security to be integrated into CQS, you would add the filter on this column:

OP_FLAG_READ_ACCESS ='Y'

If you are using Oracle SQL Developer, there are instances where SQL Developer does not operate properly because of a problem with not setting the user ID properly to a default value. You can manually set the locale variable by executing the following SQL statement in your session:

begin OP_SESSION_MGR.SET_ACTOR_ID_PRIVATE (#$account.parameters.openPagesUserId#);
  end;

Where you substitute the IBM® Cognos® macro for a valid user ID value.

SQL Statements with field level encryption

When you use the PROPERTY.AUDIT.TRAIL table in your SQL query, and field level encryption is enabled, decrypt the property value from the PROPERTY.AUDIT.TRAIL table to allow users to see the clear text values.

The following database function is used for decrypting property values: op_crypt_mgr.decrypt_property_value This is used to decrypt values in the PROPERTY.AUDIT.TRAIL table.

The following input properties are used for the op_crypt_mgr.decrypt_property_value database function:

  • Property 1: propertydefs.propertydefid (PropertyDef ID of the field.)
  • Property 2: propertyvals.value (Property Value of the field.)

For example, select op_crypt_mgr.decrypt_property_value(t.propertydefid, t.old_value) from property_audit_trail t.