Queries that contain complex SQL
Queries that contain complex SQL
ListViews allow direct concatenation of SQL-like syntax that, in turn, is converted to SQL. ListViews do not support SQL Functions or subreports. For this reason and others, ListViews are deprecated, meaning should not be used to define new queries and/or reports.
There are number of ways to query data that are not supported by Report Definitions. An example is the Haversine formula used at the FSG enterprise layer within the provide example Booking application solution. The query is found in the Browse tab of the FSG-Data-Address HaversineFormula Connect-SQL rule.
Reference AS Reference,<br />
IsFor AS IsFor,<br />
Street AS Street,<br />
City AS City,<br />
State AS State,<br />
PostalCode AS PostalCode,<br />
Country AS Country,<br />
Latitude AS Latitude,<br />
Longitude AS Longitude,<br />
Distance AS Distance<br />
FROM (<br />
SELECT<br />
z.pyguid AS pyGUID,<br />
z.reference AS Reference,<br />
z.isfor AS IsFor,<br />
z.street AS Street,<br />
z.city AS City,<br />
z.state AS State,<br />
z.postalcode AS PostalCode,<br />
z.country AS Country,<br />
z.latitude AS Latitude,<br />
z.longitude AS Longitude,<br />
p.radius,<br />
p.distanceunit<br />
* DEGREES(ACOS(COS(RADIANS(p.latpoint))<br />
* COS(RADIANS(z.latitude))<br />
* COS(RADIANS(p.longpoint - z.longitude))<br />
+ SIN(RADIANS(p.latpoint))<br />
* SIN(RADIANS(z.latitude)))) AS Distance<br />
FROM {Class:FSG-Data-Address} AS z<br />
JOIN ( /* these are the query parameters */<br />
SELECT<br />
{AddressPage.Latitude Decimal } AS latpoint,<br />
{AddressPage.Longitude Decimal } AS longpoint<br />
{AddressPage.Distance Decimal } AS radius,<br />
{AddressPage.pyNote Decimal } AS distanceunit<br />
{AddressPage.IsFor } AS isfor ) AS p<br />
ON p.isfor = z.isfor) AS d<br />
WHERE distance <= radius<br />
ORDER BY distance<br />
LIMIT 15
Note the RDB-List step within the Code-Pega-List Connect_SQL_pr_fsg_data_address activity that is sourced by the D_AddressesWithinDistance Data Page.
It is not possible to define this type of query using a Report Definition since it has two FROM-clause SELECTs, one aliased “z”, the other aliased “d”. Unlike a Report Definition, a Connect SQL rule lacks the ability to dynamically modify its filter conditions based on a parameter value being empty. Unless the Report Definition is configured to generate “is null” when a parameter lacks a value, Pega will ignore the filter condition which, in some cases, can be risky unless a limit is placed on the number of returned rows.
Within the HaversineFormula query there is no need to generate the filter conditions. It does not make sense to execute the query unless a value is supplied for every query parameter, with the exception of the IsFor column, currently either “HOTEL” or “VENUE”.
Care must be taken when using Connect-SQL rules as the column names may not be returned as aliased. For example, despite aliasing the lower-case postalcode colum to camel-case PostalCode, the column name is returned all lower-case, the same as it exists in a Postgres database. For this reason the D_AddressesWithinDistance Data Page calls a post-processing activity named Post_Connect_SQL_pr_fsg_data_address to convert column names to camel-case to then match way property names are spelled within the FSG-Data-Address class.
The java step in Post_Connect_SQL_pr_fsg_data_address is brute force. Ideally the External Mapping tab of the FSG-Data-Address Rule-Obj-Class rule could be leveraged. Note the pxClassSQL.pxColumnMap PageList within that class rule. Note: in the future java step in activities will be forbidden, The code in this java step should be moved to a Rule-Obj-Function.
<br />
<pxClassSQL><br />
<br />
<pxObjClass>Embed-ClassSQL</pxObjClass><br />
<br />
<pxColumnMap REPEATINGTYPE="PageList"><br />
<br />
<rowdata REPEATINGINDEX="1"><br />
<br />
<pxObjClass>Embed-ColumnMapping</pxObjClass><br />
<br />
<pxColumnName>city</pxColumnName><br />
<br />
<pyPropertyName_RH>.City</pyPropertyName_RH><br />
<br />
<pyColumnName_RH>city</pyColumnName_RH><br />
<br />
<pxPropertyName>.City</pxPropertyName><br />
<br />
</rowdata><br />
<br />
<rowdata REPEATINGINDEX="2"><br />
<br />
<pxObjClass>Embed-ColumnMapping</pxObjClass><br />
<br />
<pxColumnName>country</pxColumnName><br />
<br />
<pyPropertyName_RH>.Country</pyPropertyName_RH><br />
<br />
<pyColumnName_RH>country</pyColumnName_RH><br />
<br />
<pxPropertyName>.Country</pxPropertyName><br />
<br />
</rowdata><br />
<br />
<rowdata REPEATINGINDEX="3"><br />
<br />
<pxObjClass>Embed-ColumnMapping</pxObjClass><br />
<br />
<pxColumnName>distance</pxColumnName><br />
<br />
<pyPropertyName_RH>.Distance</pyPropertyName_RH><br />
<br />
<pyColumnName_RH>distance</pyColumnName_RH><br />
<br />
<pxPropertyName>.Distance</pxPropertyName><br />
<br />
</rowdata><br />
<br />
</pxColumnMap><br />
<br />
<!-- Etc.., etc.. --><br />
<br />
</pxClassSQL>
Want to help us improve this content?