Queries that contain complex SQL

There are several 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 Booking application solution. The query is found in the Browse tab of the FSG-Data-Address HaversineFormula Connect-SQL rule.

Note that the RDB-List step within the Code-Pega-List Connect_SQL_pr_fsg_data_address activity that sources the D_AddressesWithinDistance Data Page sources.

It is impossible to define this type of query using a report definition because it has two FROM-clause SELECTs, one aliased “z,” and 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 ignores 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 postal code column to camel-case PostalCode, the column name is returned all lower-case, the same as it exists in a Postgres database. 

In PostgreSQL unquoted names are case-insensitive. e.g. Select Street as STREET or Street or street, provide the column name as "street "only. if you want to get column name as "Street", then column  should be placed within the quotes  e.g. Select Street as “Street”.