Student Faculty Information System

Query – EXPRESSIONS / FUNCTIONS

_person with screwdriver

What are Expressions?
- …and how and why would you want to use them?
General Notes on Using Expressions
Expressions and Dates
Terminology
Query Functions in Expressions
- Query Function List
Other Demonstrations
Outside Sources
- Query ~ External References
- Expression Syntax – Outside Sources

Expressions are calculations that PeopleSoft Query performs as part of a query.
- Mathematical: One type of calculation that can be performed is mathematical. Standard order of operations applies.
- Functions: Expressions can also use functions. Functions are special types of command words (in the SQL Command set), usually one-word commands, which manipulate data items and return a single value which can be determined by various input parameters.
- Click this link to see a document demonstrating Query Functions in Expressions. Listed are non-Wesleyan Functions along with examples. You will see that these are available for viewing in four actual queries.
- Wesleyan Functions: Read the note below regarding Wesleyan Functions.

Reasons to use Expressions:
- When you must calculate a value that PeopleSoft Query does not provide by default
- As columns in the Query output
- As comparison values in selection criteria
- To create outer joins
- To translate coded values
- To use SQL commands
- To change the formatting of existing fields
- To join two or more fields or add text to fields
- To filter results

General Notes On Using Expressions:

Example of the TO_CHAR function used to change a number or date field to a character field.
- Expressions are created by clicking on the Expressions tab and opening the Edit Expression Properties Page.
- One tool you can use with expressions is the Function. Functions in Query Expressions are special types of command words in the SQL command set, usually one-word commands which manipulate data items and return a single value which can be determined by various input parameters.
- If you use an expression as a field, the expression can be used like any other field in a query. When you preview the query, the expression name appears as a column heading in the query. When selected for output, you can change its column heading or sort it.
- Normally, data that is the result of a calculation is produced when the query is run in real time.
- You use expressions to display a field value differently from the way you store the value. Examples would be an expression that displays the appearance of a date as July 4, 2014, or an expression that inserts a space or hyphen in a field, or an expression that displays characters all in upper case.

Expressions and Dates:
- For expressions with dates, use the date field without an alias:
  - e.g. ACTION_DATE rather than A.ACTION_DATE.
- Embedded spaces can be removed by placing the ‘fm’ prefix – as in ‘fmMonth DD, YYYY‘
  - For example, if you use the ‘fm’ prefix for a date in May, it would appear as May 29, 2016 rather than May 29, 2016 (i.e., there is only one space between “May” and “29.”
- Month display Case:
  - In order for the month to appear as September, rather than SEPTEMBER, type it as Month rather than MONTH.
- SYSDATE is the term used for today’s date or the current date.
  - On the Edit Expression Properties page you can make the Expression Type of the SYSDATE either a Character (2016-05-29) or a Date (05/29/2016).

- Functions: Functions in Query Expressions are special types of command words in the SQL command set, usually one-word commands which manipulate data items and return a single value which can be determined by various input parameters. For further examples, take a look at this extensive HEUG presentation, Expressions in Query: An In-Depth Exploration Into Function Statements.
- Wesleyan Functions: In addition to numerous standard functions that can be applied to Query Expressions, there are specialized Wesleyan Functions developed by ITS programmers for Wesleyan query users. Since the term “function” as used with PeoplesSoft Query can have a number of definitions, to differentiate this use of the term, we refer to it as a Wesleyan Function. A Wesleyan Function is a method created by the Wesleyan ITS staff that enables the PeopleSoft user to exploit the benefit that Expressions can lend to a query. It is pre-written text created specifically for inclusion in Query Expressions for Wesleyan users.
- Syntax: To create a user-defined calculation (expression) in a query, you need to know the SQL specific syntax for the expression. The following lists provide a number of illustrations of correct SQL syntax.
- You may recall that the terms of “Expression 1” and “Expression 2” are found on the Edit Criteria Properties Page. The use of the word “Expression” on the Expression Page should not to be confused with those uses of the word.

Wesleyan Query Function List
- Click the above link to see a searchable chart of Wesleyan Functions.
- Below is a sample of what you’ll see:

Query Function List
- Click the above link to see a searchable chart listing all of the functions shown in the above-described document.
- Below is a sample of what you’ll see:

Other Demonstrations:
- Click here to see a demonstration of building an Expression with the Wes Function WES_GET_CLASS_YEAR.
- Other examples of using Expressions can be found under Query Demonstrations.

Outside Sources: To see additional information on working with Expressions, follow these links:
- - Query ~ External References
    - Under this section are links and information from other sources, mostly HEUG (Higher Education Users Group), but also including some on-line instructions from other institutions. These documents do not necessarily reference Campus Solutions. However, the general concepts are the same, and you may find some helpful tips.
Go to top
- Expression Syntax – Outside Sources
  - There are a number of outside sources that have useful information that you can apply when working with PeopleSoft Query. Many include examples of syntax that can be used in Expressions in Query. Some can serve as a starting point for researching related topics. Listed are pdfs or links to these sources.

Comments are closed.