query to select entire object
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. 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.
Accepted Solution
Updated: 7 Sep 2021 16:05 EDT
Computershare
US
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.
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.
