Querying Pega82 database
Hi there,
I have a question that may have been asked before however I am not able to locate any answer here yet.
We would like to write a query to combine data from these two tables:
. PegaRules.pr4_rule_property and
. PegaRULES.pr4_fieldvalue.
Basically we are implementing a list/report to display data from pr4_rule_property table and include pyFieldvalue, pyLocalizedValue from PegaRULES.pr4_fieldvalue table.
Unfortunately this is not as simple as joining the tables.
Could someone please send a sample of how to build this query?
Thank you,
Paulo Brito
***Edited by Moderator Marissa to update platform capability tags****
Pegasystems Inc.
US
If you are trying to display them in the same list there must be some relationship, right? If there were no relationship then using one list would not make much sense, since some rows would only have values for columns like Property Name and Data Type while others would only have values for Field Value and Localized Value.
Are you looking to display the localized values associated with each property? If so, you can add a Class Join to a Report Definition specifying a relationship such as pyPropertyName Equals pyFieldName.
If you wish to list properties and field values with no relationship in the same table, this would be a SQL "cross join" which is not a feature supported by Report Definitions.
Sunlife Financial
CA
Hi Trevor,
I think you are on the right track here.
Thank you for confirming a query isn’t feasible due to lacking of relationships then building a Report Definition is my next approach.
Hi Trevor,
I think you are on the right track here.
Thank you for confirming a query isn’t feasible due to lacking of relationships then building a Report Definition is my next approach.
Properties are in one table and FieldValues are in another.
Please read the attach file and let me know what best practice you suggest here.
Thank you
Pegasystems Inc.
US
It sounds like you are looking to compare the translations for fields in your sections. I don't think we can relate the properties of the fields to their labels unless all the control happen to be configured to inherit their labels from their properties.
You could build a report that lists all untranslated and translated values referenced by your sections (note that I don't have any non-English field values in my rulesets, so the translated column is just in English):
It sounds like you are looking to compare the translations for fields in your sections. I don't think we can relate the properties of the fields to their labels unless all the control happen to be configured to inherit their labels from their properties.
You could build a report that lists all untranslated and translated values referenced by your sections (note that I don't have any non-English field values in my rulesets, so the translated column is just in English):
I built this by creating a Report Definition which applies to Rule-Obj-FieldValue with the below columns and filters. The English Label and Translated Label columns do in fact come from the same property here. We have to use a control format on the English Label column to show the untranslated value. For the Translated Label column we have to either use a control that localizes the value or one that performs a rule-resolved lookup to get the correct field value for the associated field name and referenced class. Note also that I checked "Remove duplicate rows" under "Edit filters"
In order to relate the field values to sections, I added the below Class Joins under the Data Access tab with the following respective join conditions.
Sunlife Financial
CA
Hi Trevor,
Thank you very much we are certainly moving in the right direction.
One more question. When a pass two params like Section class and Section name then I want to return all properties within this section? Note I need the properties details not the properties values.
All we try to build is a Data Dictionary Report that in the end will contain all no out of the box properties and its respective details such as length, type, translations to French (this will come from the FieldValue).
Here the Classes Im trying to join: Rule-Obj-Property, Rule-Obj-FieldValue, Rule-HTML-Section
I open a second post here to see if anybody else can help as well: https://collaborate.pega.com/question/question-building-report-definition
Thank you, Paulo