Atlas
— Standard SQL function rules
|
|
XXX This page is not complete wetma SQL function rules are a special form of function alias rules that can be referenced in Report Definition rules to facilitate tasks like creating trending reports.
In Report Definition rules, SQL function rules will 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 occurance of the character 'e' in the word "Pega", the expression appears as @@pxFind('Pega', 'e'). It 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.
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 | xxxxxxx. |
| pxDifferenceInHours | xxxxxxx. |
| pxDifferenceInDays | xxxxxxx. |
| pxDifferenceInMonths | xxxxxxx. |
| 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 | xxxxxxx. |
| pxConvertTextToDate | xxxxxxx. |
| pxConvertTextToTimeOfDay | xxxxxxx. |
| pxConvertTextToInt | xxxxxxx. |
| pxConvertTextToDecimal | xxxxxxx. |
| pxConvertTextToFloat | xxxxxxx. |
| pxFind | Returns the position in a Text string of the first character in the first occurence 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. |