SQL function rules are a special form of function alias rules that you can reference in report definition rules to facilitate tasks like creating trend reports.
In report definitions, SQL function rules appear in expressions following the symbols @@. For instance, to find the location of the first instance in a string of a character or substring you provide, you can use pxFind. To search for the first occurrence of the character 'e' in the word "Pega", the expression appears as @@pxFind('Pega', 'e'). This expression returns the location of the letter 'e' as 1, since the expression starts counting with 0.
SQL function rules are not case sensitive when used, so pxFind, PXFIND, and pxfind are all valid.
Your system includes these standard SQL function rules.
SQL function |
Purpose |
| pxDay | Returns the date portion of a DateTime value. |
| pxMonth | Returns the month portion of a Date or Datetime value as a Date value which is the first day of that month (useful for sorting and grouping across multiple years). |
| pxYear | Returns the year of a Date or DateTime value as a four-digit integer. |
| pxHour | Returns the hour portion of a Date or DateTime value as an integer between 0 and 23. |
| pxMonthNumber | Returns the month portion of a Date or DateTime value as an integer between 1 and 12. |
| pxDifferenceInMinutes | Returns the difference between two DateTime values (the second minus the first) in minutes. |
| pxDifferenceInHours | Returns the difference between two DateTime values (the second minus the first) in hours. |
| pxDifferenceInDays | Returns the difference between two DateTime values (the second minus the first) in days. |
| pxDifferenceInMonths | Returns the difference between two DateTime values (the second minus the first) in months. |
| pxQuarter | Returns the Date value of the first day of the quarter that a Date or DateTime value falls within. |
| pxWeek | Returns the week that a Date or DateTime value falls within as an integer value between 0 and 53. |
| pxYear | Returns the year portion of a Date or DateTime value as a four-digit integer. |
| pxConvertNumericToText | Returns a numeric value converted to a Text value. |
| pxFloor | Returns the largest integer that is less than or equal to a Decimal value. |
| pxCeiling | Returns the smallest integer that is greater than or equal to a Decimal value. |
| pxSubstring | Returns a sub-string of characters from a Text value, starting from a given position. @@pxSubstring('Pega', 2) returns 'ga'. |
| pxLowerCase | Returns a Text value with all upper-case characters converted to their lower-case equivalents. |
| pxUpperCase | Returns a Text value with all lower-case characters converted to their upper-case equivalents. |
| pxConcatenate | Appends a specified Text string to another specified Text string. |
| pxReplace | Returns a Text string with all occurrences of a specified substring replaced by a specified string. @@pxReplace('Peach', 'ach', 'ga') returns 'Pega'. |
| pxConvertTextToDateTime | Returns a Text value (which is in a valid date format) converted to a DateTime value. |
| pxConvertTextToDate | Returns a Text value (which is in a valid date format) converted to a Date value. |
| pxConvertTextToTimeOfDay | Returns a Text value (which is in a valid time format) converted to a Time value. |
| pxConvertTextToInt | Returns a Text value (which is in a valid numeric format) converted to an Integer value. |
| pxConvertTextToDecimal | Returns a Text value (which is in a valid numeric format) converted to a Decimal value. |
| pxConvertTextToFloat | Returns a Text value (which is in a valid numeric format) converted to a Floating Point value. |
| pxFind | Returns the position in a Text string of the first character in the first occurrence of a submitted string. @@pxFind("Pegasystems", "sys") returns 4. |
| pxLeft | Returns the leftmost nn characters in a Text string. @@pxLeft('Pegasystems', 5) returns "Pegas". |
| pxLength | Returns the number of characters in a Text string. |
| pxRight | Returns the rightmost nn characters in a Text string. @@pxRight('Pegasystems', 5) returns "stems". |
| pxCurrentDateTime | Returns the current system date and time as a DateTime value. |