Posted: 23 Sep 2020 15:24 EDT Last activity: 28 Oct 2020 18:46 EDT
I need a way to read the most recently updated version of a data- instance (the entire blob). Ideally I'd like to do this in one trip to the DB and without having to write some custom java. In my use case I don't know the specific key of the record. I know some of the key parts that I can use to query the database. Proper sorting would let me select the most recently updated record but I'm having issues finding features to do this.
Here's what I've been able to do so far.
1. Use obj-browse to select the pzinskey, and RefreshDateTime properties while sorting by RefreshDateTime in descending order. Also set the max rows to 1 to avoid pulling back too much data. Next use obj-open-by-handle to read the record using the pzinskey retrieved by the obj-browse. This is high frequency use case so making two trips to the DB to get my data is a huge issue.
2. Using obj-browse I can select the entire instance conditionally by date range on RefreshDateTime simply by not checking the "select" checkbox but the results can't be sorted. The SQL does not include the "order by" clause. This negates the ability to sort by RefreshDateTime desc and restrict the number of rows. In order for this to work I'd have to return more rows then use obj-sort to pick the most recently updated record.
How would a BIX report help? I'm on 7.2.2. Here's my use case.
I'd like to use the "refers to a data page" property option to dynamically load some data. My data is shared across multiple cases so I don't want to use an embedded page and store it redundantly in each case.
Some of the data in question gets refreshed daily based on transactions performed on the loan. As the data changes we need to keep a before/after snapshot of the data. So we might have a rows in the table like the following where the RefreshDate indicates when the data was changed.
123 01-July-20 <some data>
123 01-Aug-20 <some updated data>
123 12-Aug-20 <some more updated data>
When I open a case resolved prior to 01-Aug-20 I need to use the data from 01-Jul-20 in order to use the historically accurate data. For a case opened on 01-Aug-20 and still open on or after 12-Aug-20 we must use the data from 12-Aug-20
This would be easy to do IF the obj-browse would include the "order by" clause in the generated SQL. See my attachment for a screenshot example.
In the end I'm looking for an efficient way to select the entire row including the blob. So far the only thing that works is to make two trips to the DB.
1) Obj-Browse with the 'Select' checkbox checked for the RefreshDateTime filter, Max rec =1 and sort descending. This properly includes the 'order by' in the SQL.
2) From the Obj-Browse results use the insHandle to call obj-open-by-handle
two trips is not efficient when you consider that I'll be using this type of pattern for several classes of data that will be accessed from the case.
I raised a ticket with support and found the answer that I was hoping for. Here it is. I hope this helps others.
The issue was not related to selecting the blob via an obj-browse. The issue was about selecting the blob and sorting the results all at once via the generated SQL. Now that the sorting issue has been resolved all I needed to do was limit the results to a max of 1 sort the data descending by pxcreatedatetime to return the most recently updated row.
Selecting the blob is easily done with conditions like the following in the obj-browse. Notice that the "select" checkbox in the following screenshot is not selected on either exposed property. By not using the checkbox this causes Pega's sql generation logic to retrieve the Blob instead of individual columns. However, the SQL will not include the "Order by" clause in the SQL even though the sort option is set to descending. By checking the "select" checkbox on either filter the blob is not returned but the "Order by" clause will be included.
To work around this you must change the filters. First check the "select" checkbox on the column that needs to be sorted (pxCreateDateTime) and set the sort option. Next add any unexposed column as a filter with the "value only" option and check the "select" checkbox. The addition of the unexposed column causes Pega's sql generation logic to retrieve the Blob instead of individual columns. By making both of these changes the blob will be selected and the "Order by" clause will be included in the SQL.