Question
US
Last activity: 2 Oct 2025 22:37 EDT
Unexposed properties cannot be selected for classes mapped to external tables.ERROR:"p"is not a table, compositetype or frgn table
Hi,
We created DB view by joining 3 tables by sql query. at first we added around 10 fields. after we added extra properties because of requirement. whatever columns we added in view at the time of alter those columns related properties we are not able to call in report definition and even if we create those properties in data model of the class also getting the error"ERROR: "providersearch" is not a table, composite type, or foreign table"
Please gave us the solution for this.
Pegasystems Inc.
GB
⚠ This is a GenAI-powered tool. All generated answers require validation against the provided references.
We recommend following the solution detailed below to resolve the issue.
Analysis
The error message "ERROR: 'providersearch' is not a table, composite type, or foreign table" indicates that the properties added to the DB view are not being recognized correctly by the Pega platform. . Other examples exist where properties created with double quotes were not considered optimized, leading to issues in report definitions.
Proposed Solution
To resolve this issue, follow these steps:
⚠ This is a GenAI-powered tool. All generated answers require validation against the provided references.
We recommend following the solution detailed below to resolve the issue.
Analysis
The error message "ERROR: 'providersearch' is not a table, composite type, or foreign table" indicates that the properties added to the DB view are not being recognized correctly by the Pega platform. . Other examples exist where properties created with double quotes were not considered optimized, leading to issues in report definitions.
Proposed Solution
To resolve this issue, follow these steps:
- Remove Double Quotes: Ensure that the properties in your DB view are created without double quotes. This can be done by altering the view creation SQL to avoid using double quotes around property names.
- Recreate the View: Drop the existing view and recreate it with the corrected property names.
- Update Data Model: Ensure that the properties in the data model of the class are correctly mapped to the columns in the DB view. Use the 'External Mapping' tab of the class rule form to configure property-column mappings.
- Optimize Properties: If necessary, use the Pega wizard to optimize the properties for reporting. This can be done by navigating to SysAdmin -> Dynamic System Setting and setting
database/AutoDBSchemaChangesto true. - Clear Cache and Restart: After making these changes, clear the cache and restart the server to ensure that the changes take effect.
Root Cause
The root cause of this issue is the improper creation of properties in the DB view, specifically the use of double quotes around property names, which causes them to be unrecognized or unoptimized in the Pega platform. By ensuring that properties are created without double quotes and properly mapped, the issue can be resolved.
PleASE LOog a support ticket ont he MSP if you still need hellp
HCA Healthcare
US
Here’s the fix. First, recreate the DB view with CREATE OR REPLACE VIEW and make sure it’s in the right schema; grant SELECT on it to the Pega DB user. In Pega, open the Database Table instance for your class, ensure the external table name is schema-qualified (schema.viewname), then click Columns/Explore Data and refresh/populate the class mapping so the new columns appear. Create or remap properties to those columns (use the “Class Mapping”/“Properties from database table” wizard), then save. Revalidate and Save the class and the Report Definition, and clear caches (or restart) if needed. In the RD, only use properties that are mapped to real columns; unexposed (BLOB) props aren’t selectable for reporting. If you still see “providersearch is not a table, composite type, or foreign table,” your view name or schema is wrong in the mapping or your DB user lacks rights—fix the schema. Finally, avoid quoting or mixed-case names in the view and keep column names simple.