You are here: Reference > Standard rules > Alias Function rules (SQL)

  Atlas — Standard SQL function rules

About Function Alias rules

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.

General syntax

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.

UpAbout Function Alias rules

UpAtlas home