Creating a custom SQL function in Pega

First of all a shout-out to my readers: Happy New Year!

2017 could be remembered as the year of Low-Code or even No-Code trend, but will that disruptive promise be the ultimate goal in software development? I don´t think so necessarily.

The Fall of Coding

Don´t get me wrong: Enterprises definitely benefit more if the productivity of their IT department increases. And this is more likely the case when developers do not have to write a thousands lines of code (LoC). However maintaining the auto-generated gazillion lines of code is even worse. Having an application platform that produces code from configuration could potentially write more lines than you would have done by hand.

The point however being, if you abuse the "no code" approach — or any platform for that matter — you could end up with to big of an application to support by your team. Don´t be afraid of a little coding when the problem at hand is small, isolated and can be clearly defined. The solution might be valuable as a framework or utility function.

Low Code Example

As a real-life example let me explain how the following single line solution came about. I was looking for a filter function testing if certain date column has a value that is before the current system time. Because of date type differences this could not be easily achieved and threw the following error in Designer Studio:

It made me think: Why have a complex (even hard to read) expressions like the following strophe at all?

@@pxConcatenate(.ValidFrom, "T000000.000 GMT", @@pxCurrentDateTime())

Function Alias

A simple function or alias would be more elegant, would it not? That why I came up with the CurrentDateAsText and CurrentDateTimeAsText utility functions. (We did not or could not alter the column´s data type without futher impact.)

The CurrentDateTimeAsText rule shown is just a variant of Pega´s default pxCurrentDateTime where the Return type field has been changed to Text instead of Date Time. Nonetheless, Alias Function rules like these (class Embed-UserFunction) can be selected in the Pega´s Calculation builder for Report Definitions.

Tip: SQL is a very rich language with many potential statements that you could leverage in Pega by adding such a simple rule definition. Just stick to ANSI variant/dialect to make sure your functions are cross-database.

In practice, configuration (business rules in PRPC) is easily duplicated with a dumb Save As. So are the numerous expressions like they were in tradition imperative programming in source code.

In conclusion, good software development comes down to people recognizing and answering the question: When do I use what? A question that is valid on pretty much any level or task. In the (above) situation of a complex filter expression just write you own and share! #SharingIsCaring

[Edgar] (1/5/2018 2:30:30 PM)