DARTS : Creating Expressions

The Basics of Expressions
Step by Step for Creating Expressions
Expression Examples:
Tips

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.

Step by Step for Creating Expressions

Expression is created in the "Expression" page. Click the "Add Expression" button to create a new expression
Select Expression Type
Set the length of the new field
Type your expression in the box for "Expression Text."
After creating an expression, click "Use as Field" if you want the new field to be used in the query.

If you want to use the expression as a criteria, 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 Criteria tab.

Back to top

Expression Examples:

Aggregate Function (AVG, COUNT, MAX, MIN, &SUM)

You will need to check the "Aggregate Function" box in your expression when you use 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 blank field, use single quote, blank space, and single quote:

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

Structure of case statement:

case statement format

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

Please go to Expression Libraries page for more examples.

Back to top

Date/Time

Select Expression Type as Date, Datetime or Time

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 Add Field function in the edit expression, the application will insert the field's alias, A. You need to either delete the "A." characters or enter the field freeform to make the field operate as a date field and not a character field.

Back to top

Partition over

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

Back to top

LISTAGG

combine multiple data rows in to one row
LISTAGG is aggregate function, so you need to check the aggregate function box
Format: LISTAGG(field_name, ',' ) WITHIN GROUP (order by field_name)

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

Back to top

Regular Expressions

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

Join PS Query Group!

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

HELP IS AT HAND!

Question, comment, critique? We are here to help and we strive to make our site a comprehensive, user friendly experience. Contact us at DARTS@maine.edu.

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 numbers of parentheses

DARTS : Creating Expressions

The Basics of Expressions

Step by Step for Creating Expressions

Expression Examples:

Aggregate Function (AVG, COUNT, MAX, MIN, &SUM)

Converting Values

Numeric Function

Free text

CASE statement

Date/Time

Partition over

LISTAGG

Regular Expressions

Tips

Attachments: