Hello everyone, your Admissions Advisory Group will be posting a series of blogs over a range of topics.
We'll start our series by taking a look at PS Query Expressions!
You can use expressions as fields, or within criteria. They can help in formatting output for your users and can also help reduce hard-coding in data sources, population selections, and reports.
We will cover some of the basic and popular query expressions here. You will often find that many things that work in SQL also work in PS Query expressions (though often with some tweaking required).
The below expressions (except for the MetaSQL) are done in an Oracle database. If you are not on an Oracle Database, you may need to tweak some of these in order to get them to work for you.
- Drilling URLs are an expression used when you'd like to provide a functioning URL as a column in your query.
- Helpful for making queries more user friendly
- Used to direct users to relevant pages and reduce user 'clicks' (Component URLs)
- Reduce complexity and redundancy within your main query by linking users to other queries (Query URLs)
- Example: I want to see all the details of a commgen without needing to scroll through forty plus columns and many extra rows. Instead I will use drilling URLs to direct users to one of four separate queries based on the data they want to view:
- Seven types:
- Query URL: A link that executes another query. The following options are available:
- You can pass over prompt information from your main query to your drilling URL query. Use these to 'extend' your queries by keeping the records and columns in your base query simple.
- Pass URL key values for prompts, etc.
- External URL: A link to an external website that displays the website within your Campus Solutions header and appends your instance, environment, node, etc., in front of the URL
- Free Form URL: A link to an external website that leads directly to the website -- similar to external URL but doesn't show your header or add your node and other info -- just the URL.
- Component URL: A link to a PeopleSoft Component
- Get as granular as page level, or opt not to.
- Save your users some clicks!
- To link to a component in PeopleSoft:
- Choose component URL
- Fill out the component information that you'd like to link to, and the action (update, etc). (Note: you can use Ctrl +J to find component and page info).
- Select a page within that component, or leave it blank to direct to the Search page
- Users clicking the URL must have security for the menu action for where you are linking them
- Attachment URL: A link to an attachment within your database or in an FTP location -- a long as you have the attachment setup and security access
- Image URL: A link to retrieve data from a query showing an image type field.
- Your other query must have an image type field set up to display: this is how you select the Image Field Name field.
- REST URL: A link utilized to drill down from a query into another defined, non-bursted BI report -- but that have been set up on the Integration broker side.
- Setup shared between the types:
- URL Keys: pre-populate the search area where you would typically enter data, with the data from your query. You can have only 'selection flag' checked per drilling URL. You can also select none. Populate the Keys with the values you want to pass. You can use a default or a field. Only fields you select on the Query Tab show in the lookup
- Map URL to Query Columns: where you associate which column in your query results contains the URL
- Select Query Columns: are the fields you want to pass in your URL, and in which order
Max, Min, Sum, Count
- You can use aggregates in expressions as well -- this can be helpful for both criteria (You would use the Having tab, as you would for any aggregate using in criteria) and for displaying expressions as fields as well -- as you know, when you take a normal field and display it as an aggregate, you lose the ability to use the field in joins as in criteria (unless comparing against another aggregate in the Having tab). By using a field within an aggregate, you gain the ability to use the field itself to display the non-aggregate value, and to use it in joins and non-Having criteria.
MAX or MIN or SUM or COUNT(YOUR_FIELD)
ListAgg:You can use LisAgg for situations where you do not want multiple rows for multiple data results, and would prefer the data consolidated into one row
- ListAgg is an aggregate function (so you'll need to check the aggregate function box!) that lists values that you specify in the same column, rather than in multiple rows.
LISTAGG ((YOURFIELD), ',')
within group (order by YOURFIELD)
- Where YOURFIELD is the field you want the ListAgg on and , is your delimiter between the values
Case statements are powerful and can be used for a wide variety of scenarios ranging from simple to complex. You can also nest other expression logic within case statement.
- You can use other functions in case statements
- Can use wildcards
- In the format of:
When condition 1 operator (!=, =, like, in list, null, >, <, etc) 'constant' (or field, without quotes) then 'Value to display' (or field to return, without quotes)
ELSE (optional)'Value to display' (or field to return, without quotes)
Repeat the 'When, then' as much as needed, you only need one 'Case', 'Else', and 'End'
Some commonly used scenarios for case statements could be:
- If you only want to return certain values based on certain criteria
- Translating values
- If you want to return different values for fields using the same field name, etc. (for example, return HOME address in one column and MAIL in another, rather than as two rows)
Some Case Statement Examples
You can add expressions as fields, and each expression will show as a separate column.
- This can help in any situation where you want distinct columns for data that is stored in the same field.
- For Example: if you want to pull a person's major and minor, or personal and campus emails, as two columns, rather than have two rows for the person, etc. (In this example, we are using case statements as fields to define the email type we want to pull by column, and then setting them as 'Max')
Using Prompts an in 'IN LIST'
- For Example: you are writing a query to pull data for possible Duplicate IDs and I want my users to be able to enter up to 3 EMPLIDs to compare at once.
when EMPLID IN (:1, :2, :3) then EMPLID
'Translate' / convert values
For Example: you want to translate ADMIT_TERM to the semester name. (In the belowexample, fall semesters end in 8, summer ends in 5, and spring ends in 2)
when ADMIT_TERM like '%8' then 'Fall'
when ADMIT_TERM like '%5' then 'Summer'
when ADMIT_TERM like '%2' then 'Spring'
Find the first date of a week:
- For Example: You're asked to find how many commgens went out each week, and you're asked to show the date of the first day of each week: This is an aggregate expression, coupled with TO_Char(COMPLETED_DT,'ww')
when TO_Char(COMPLETED_DT,'day') = 'monday' then COMPLETED_DT
when TO_Char(COMPLETED_DT,'day') = 'tuesday' then (TO_date(COMPLETED_DT, 'mm/dd/yyyy') - 1)
when TO_Char(COMPLETED_DT,'day') = 'wednesday' then (TO_date(COMPLETED_DT, 'YYYY-MM-DD') -2)
when TO_Char(COMPLETED_DT,'day') = 'thursday' then (TO_date(COMPLETED_DT, 'YYYY-MM-DD') -3)
when TO_Char(COMPLETED_DT,'day') = 'friday' then (TO_date(COMPLETED_DT, 'mm/dd/yyyy') -4)
when TO_Char(COMPLETED_DT,'day') = 'saturday' then (TO_date(COMPLETED_DT, 'mm/dd/yyyy') -5)
when TO_Char(COMPLETED_DT,'day') = 'sunday' then (TO_date(COMPLETED_DT, 'mm/dd/yyyy') -6)
Formatting with expressions
You can use expressions to format your data!
- Converting Dates using To_Char
- Values recognized:
- MM -- numeric month
- DD -- numeric day
- YYYY -- numeric year
- WW -- numeric week of the year
- HH -- hour
- MI -- minutes
- SS -- seconds
- Month -- Day spelled out
- Day -- Day spelled out
- Add or subtract to date times -- You can use expressions to add or subtract hours to a datetime field.
To_char(Your_DATETIME_FIELD – (hours/24), 'MM/DD/YYYY HH:MI:SSAM')
- Add or Subtracting to dates. Set your expression type to 'Date' or 'Char', and subtract or add the number of days. You can also do with CURRENT_DATE.
to_date(FIELD,'YYY-MM-DD') + 7 TO_CHAR((CURRENT_DATE)-21,'YYYY-MM-DD')
Text: You can use expressions to format text case:
- proper case:
- Concatenate: You can combine multiple values / fields into one expression
|Field or 'value' || Field or 'Value'|
- You can use expressions to populate text or blanks, helpful for formatting for file output needing default values or blank columns, etc.
Regular expressions identify a pattern within a field or string. Different types of regular expression perform different functions / logic on that pattern
- REGEXP_SUBSTR: (Substring Regular Expression) Finds and returns values meeting pattern within a string or substring, can use wildcards:
- Returns the value / string you were searching on.
Regexp_substr(FIELD or STRING, 'pattern to search for')
Regexp_substr(FIELD or STRING, 'pattern to search for', # occurrence of this pattern to find in the string)
- REGEXP_COUNT: (Count Regular Expression) Returns a count of how many times a pattern occurs in your field or string.
- Returns the number (count) of times your pattern is met
Regexp_count(FIELD or String, 'pattern to look for')
- REGEXP_INSTR: (In-String Regular Expression) Finds and returns the character position of the matching value within a substring
- REGEXP_INSTR – useful for searching for occurrence of patterns within a string, and returning the character number that that occurrence falls on.
- Returns a number value – the occurrence # / 'position' of the value. Can also be used as an advanced 'contains' search by finding where occurrences are > 0 (could also do this with Regexp_count).
regexp_instr(FIELD or String, 'pattern', #of characters to search on, Occurrence #)
- REGEXP_REPLACE: (Regular Expression to Replace) Finds pattern(s) and replaces with values that you specify
- Returns your field with the replaced values
REGEXP_REPLACE(Field,'pattern or value to replace',' value to replace with')
- For example, let's say we have a field where HTML is enabled, like Activity Guide Agreement Text or Checklist Item Descriptions, but we want to strip the HTML so we can display the wording in a commgen.
DBMS_LOB.SUBSTR(TRIM(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(YOURFIELD,'<[^pP][^>]*>',''),' ',''),'<p>',chr(10)||chr(10))), 500, 1)
- What we are doing here is using a regular expression to replace certain HTML tag occurrences with '' – effectively removing them.
- We are initially ignoring the paragraph tag, so we can then replace the paragraph tag with line break characters, to ensure spaces in the proper places so the wording formatting still looks normal.
- You can use an expression to take a substring (pull of partial data) of the field. You can also use it with TRIM to remove any trailing or beginning spaces.
- The first number is the number of characters you are pulling, the second is the start position. So, in our example below, we pulling 12 characters from YOUR_FIELD, starting on the first character:
DBMS_LOB.SUBSTR(TRIM(YOUR_FIELD), 12, 1)
- For Example: You want to pull only the first four characters of each ACAD_ORG for a commgen:
- You can also use substrings as fields to avoid the CLOB error you may get when using distinct and a long field, such as a Text type field. This way, you can still pull all the data in that field, but by defining a substring, you will no longer get a CLOB error by following the same format and just pulling a large amount of characters:
DBMS_LOB.SUBSTR(TRIM(YOUR_FIELD), 500, 1)
Using Prompts in Expressions
- You can use prompts in expressions. This can be helpful for using prompts in OR statements, or for condition types that don't allow prompts typically (such as 'between')
Return only the most recent effective dated / sequence value, even when there are multiple variations on the value
- For Example: you want to see the name of someone who has functional security to release a hold, but even when I try and use the max EFFDT on the NAME table, per normal query functionality, if someone has multiple name variations, it is still returning all distinct variations, despite one effective date being more recent:
- With my expression, I am concatenating the effective date with the name, taking the max, and then using a substring to strip out the date:
DBMS_LOB.SUBSTR(TRIM(MIN(DATE or SEQ || FIELD)), 50, 11)
Decode can be used to translate exact values into other exact values (Unlike in Case Statements, you cannot use wildcards or other expressions nested within Decode)
'Value1', 'what to display for Value1',
'Value2', 'what to display for Value2',
'what to display for non matching values')
- For Example: here is an example of using Decode to translate terms -- notice that we have to specify each term within the Decode expression, we cannot use wildcards like we could in our case statement
'2178', 'Fall 2017',
'2188', 'Fall 2018',
'2198', 'Fall 2019',
NVL (Rreplacing NULL values)
- Sometimes, you may want to replace null values with a default value. You can use 'NVL' to tell PS Query that you want to replace NULL values with a value that you specify.
NVL (FIELD or STRING, 'value to replace null value with')
GREATEST and LEAST
- GREATEST returns the highest value in a list of expressions
- LEAST returns the lowest value in a list of expressions
- Not aggregate functions, so both can be used without the aggregate function box checked.
Meaning, you can nest multiple expressions within GREATEST or LEAST
- Need to have at least two values– use on constants rather than fields. Ex: LEAST(2192, 2198, 2188)
GREATEST(VALUE1, VALUE2, VALUE3)
- Meta-SQL can perform functions and retrieve values. Unlike other SQL functions above – it is not dependent on any specific database platform.
- Not every Meta-SQL element can be used in PS Query. For a list of which elements can be used in PS Query, visit this Oracle page.
- %OperatorID (user ID)
- See this Oracle page as a reference for a full list and how to use them
- You can use %Coalesce to replace Null values
%Coalesce(FIELD or STRING, 'value to replace null value with')
- For Example:
- %OperatorID pulls the operatorID of whomever is logged in and running the query
- %DateDiff subtracts the first date from the second date. You can then divide by 365 to get the date in years.
(%DateDiff(%DateIn(DATE1), %DateIn(DATE2))) / 365
Meta-SQL: % DateTimeDiffExtended
- % DateTimeDiffExtended compares two date times and then returns the difference in the day/hour/minutes/seconds format. You can also specific how many decimal places you want ('precision number') (the '0' in the example below – if you use 0, it will round to the next whole number)
(%DateTimeDiffExtended(Date Time1, DateTime2, DAYS, 0))
- Truncate can be helpful to remove decimal points – for example, such as if we do not want to round with %datetimediffextended (so our precision value = 1), but we do not want a decimal point returned in our expression.
%truncate(Field/constant/expression, number of places after decimal to truncate)