Creating Expressions in PS Query

This article explains the expressions function that can be used in PS Query.

Detailed Information

Join the PS Query Group! Have you joined UMS PS Query User Group yet? You can ask questions or share tips and more.

Table of contents

The Basics of Expressions

  • An expression is some form of operation involving numbers, character strings, or dates and times.
  • Expressions can be used in a field or criteria.
  • Many SQL functions can be used in PS Query expressions.
  • You cannot use some SQL functions, such as 'if' or 'where' in Expressions.
  • Sometimes Expressions need tweaking.

Back to top

Step by Step for Creating Expressions

  1. Expressions are created on the "Expressions" page. Click the "Add Expression" button to create a new expression

    showing the expressions tab and add expression button on the PS Query page.
  2. Select Expression Type
  3. Set the length of the new field
  4. Type your expression in the box for "Expression Text."

    Showing the window with the expression text box to enter your expression in.
     
  5. After creating an expression, click "Use as Field" if you want the new field to be used in the query.
    showing the location for the use as field link
    If you want to use the expression as a criterion, click the funnel icon. The criteria will be added to the Having tab if the field is an aggregated field.
    Please see the Having page for more information. Otherwise, the criteria will be under the Criteria tab.

    Showing the location of the add criteria icon.

Back to top

Expression Examples:

Aggregate Functions (AVG, COUNT, MAX, MIN, SUM)

You need to check the "Aggregate Function" box in your expression when you use the aggregate function.

showing the box to check for the aggregate function.

Aggregate function examples:

Function Usage Example
AVG average of values AVG(A.UNT_TAKEN_PRGRSS)
COUNT counts rows COUNT(A.EMPLID)
MAX maximum value in the field MAX(A.ADMIT_TERM)
MIN minimum value MIN(A.ADMIT_TERM)
SUM calculate sum SUM(A.UNT_TAKEN_PRGRSS)

Back to top

Converting Values

Function Usage Example
LOWER converts a string to all lowercase characters LOWER(A.STATE)
UPPER converts a string to all upper case characters UPPER(A.STATE)
INITCAP converts a string to initial capital letter INITCAP(A.FIRST_NAME)
SUBSTR extracts a portion of a string or field
SUBSTR(field, position, length)		 SUBSTR(A.ACAD_PROG, 1, 1)
|| (concatenation) combines two or more fields or values A.ACAD_CAREER || ' - ' || A.ACAD_PROG

Back to top

Numeric Function

Function Usage Example Results
ROUND

Returns a number rounded to x number of decimal points
ROUND(field, decimal place) The default decimal place is 0.

 ROUND(A.CUM_GPA,2) 3.596 → 3.60
TRUNC

Returns a number truncated to x number of decimal points
TRUNC(field, decimal place) The default decimal place is 0.

 TRUNC(A.CUM_GPA) 3.596 → 3

Back to top

Free text

You can create a new field with any text or blank field. For example, if you put 'Hello' on the expression text box, the new field will return Hello for all rows.

To create a blank field, use single quote, blank space, and single quote:

showing the free text box window and where you can type the expression text. You will need to select the expression type from the drop down.

Back to top

CASE statement

Case statements can be useful when

  • translating values
  • limiting results to specific values based on criteria
  • returning different values or splitting one field into multiple values

The standard structure of a case statement is below:

CASE WHEN condition 1 THEN result1
WHEN condition 2 THEN result2
...
WHEN condition N THEN resultN
ELSE result
END

Here is an example of a case statement that translates institution value to institution name:

Example - case statement

/*+Institution short desc*/
CASE
WHEN A.INSTITUTION = 'UMS01' THEN 'UMA'
WHEN A.INSTITUTION = 'UMS02' THEN 'UMF'
WHEN A.INSTITUTION = 'UMS03' THEN 'UMFK'
WHEN A.INSTITUTION = 'UMS04' THEN 'UMM'
WHEN A.INSTITUTION = 'UMS05' THEN 'UM'
WHEN A.INSTITUTION = 'UMS06' THEN 'USM'
WHEN A.INSTITUTION = 'UMS07' THEN 'UMPI'
ELSE 'Check'
END

Back to top

Date/Time

Select Expression Type as Date, Datetime or Time on the expressions window. Below is some examples for Date and Time expressions.

Usage Example Selected Expression Type Original data Result in new field
today's date

SYSDATE

 Date, Datetime or Time    03/24/2022
add days TO_DATE(A.GRADUATION_DT) + 365 Date, Datetime or Time  05/24/2021 05/24/2022
get difference (in days) TO_DATE(SYSDATE) - TO_DATE(A.POSITION_ENTRY_DT) Date, Datetime or Time  SYSDATE = 3/25/2022
POSITION_ENTRY_DT = 9/1/2017		 1666
calculate months between two days MONTHS_BETWEEN (SYSDATE, A.POSITION_ENTRY_DT) Character SYSDATE = 3/25/2022
POSITION_ENTRY_DT = 9/1/2017		 54.78
calculate years between two days

(TO_DATE(SYSDATE) - TO_DATE(A.POSITION_ENTRY_DT))/365.25

TRUNC((TO_DATE(SYSDATE) - TO_DATE(A.POSITION_ENTRY_DT))/365.25)

 Character SYSDATE = 3/25/2022
POSITION_ENTRY_DT = 9/1/2017		 4.5
4 (when it is truncated)
convert date to character TO_CHAR(CENSUS_DT, 'MMDDYYYY') Character 10/15/2022 10152022
get month name TO_CHAR(CENSUS_DT, 'Month') Character 10/15/2022 October
get year from date field SUBSTR(CENSUS_DT, 1,4) Character 10/15/2022 2022

IMPORTANT NOTE:
When you use the Add Field function in the edit expression, the application will insert the field's alias, A.  You need to delete the "A." characters or enter the field freeform to make the field operate as a date field, not a character field.

Back to top

Partition over

Please go to Aggregate and Analytic Functions- Partition Over/Partition By, etc. page

LISTAGG

  • LISTAGG combines multiple data rows into one row. Below is an example, and three rows turned into one low.
    showing an example of what the LISTAGG function does.
  • LISTAGG is an aggregate function, so you need to check the Aggregate Function box
     
  • Format: LISTAGG(field_name, ',' ) WITHIN GROUP (order by field_name)
    showing the expression window that appears when the LISTAGG function is used.

Please go to Flattening a File - Multiple Rows to Single Row page for more examples.

Back to top

Regular Expressions (REGEXP)

  • REGEXP_SUBSTR
    • finds and returns values meeting pattern within a string or substring
    • can use wildcards (^ , $, .)
    • returns matching values
  • REGEXP_INSTR
    • finds and return the character position of the matching value within a substring
    • returns a numeric position number
  • REGEXP_COUNT
    • tells you number of times you pattern is met
    • returns a numeric count
  • REGEXP_REPLACE
    • finds pattern and replaces with values that you specify
    • returns original pattern, but with substituted values

Back to top

Tips

  • Try to create a new expression one at a time and make sure if it works. 
  • Use header name with /*+ */
    Example: /*+description for special*/
  • Use Notepad++ 
    • Easier to read / Copy & Paste/ Replace
    • Helps to check the number of parentheses

Environment

  • PeopleSoft Query

Back to top

Print Article