People Soft Query Tips and Tricks Session Number

People. Soft Query Tips and Tricks Session Number 9133 Tue, Sep 24, 2019 1

Lion. PATH is Penn State’s name for its People. Soft Campus Solution Student Information System. The Lion. PATH Development & Maintenance Organization (LDMO) is the central office that provides specialized and functional support for the student information system. We support the functional offices across the University – Admissions, Advising, Bursar, Financial Aid, Registrar, etc. Presentation by: Bob Fogarty LDMO Reporting Team Lead rtf 12@psu. edu 2

The Pennsylvania State University (Penn State) • Aug 2015: Rolling implementation of CS 9. 0 began • 24 campuses across Pennsylvania, plus online • Aug 2016: Fully live with CS 9. 0 • Almost 100 k students • Nov 2017: Upgraded to PT 8. 56 • July 2019: Upgraded to CS 9. 2 (fully applied at PUM 12, selective to PUM 13) • Land-grant University established in 1855 • ~85, 000 Undergrad • ~13, 000 Grad • <1, 000 Medical • <800 Law 3

Table of Contents DECODE() Function 4 Slide 5 Using Prompts in Expressions Slide 6 Using Optional Prompts in Criteria Slide 9 Using Today’s Date as a Prompt Default Slide 12 Prompts in an Edit List Slide 14 Creating a Custom List of Values Prompt Slide 15 Creating List of Value Criteria using Comparison Conditions Slide 22 RIGHT() and LEFT() Functions Slide 24 Working with DATES Slide 26

DECODE function The Oracle DECODE() function allows you to add the procedural if-then-else logic to the query. 5 DECODE(A. SRVC_IND_CD, 'RCA', 'Y', ’N’) DECODE(B. SRVC_IND_CD, 'BFR', 'N', 'Y’) Term Consent Elec Bus Completed Financial Resp Completed 2198 Y Y 2198 N N 2198 Y Y 2198 N Y 2198 Y N

Using Prompts in Expressions The prompt, Include Ethnicity? , is a Yes/No Table prompt with a Default Value of N with a Length of 11. 6

The DECODE expression returns null for the default value of ‘N ‘ or the column K. ETHNIC_GRP_CD for a selected value of ‘Y ’. Why would we do this? This expression was added because a student can have multiple ethnicities and content may be duplicated. Using the default value , the rows will be distinct. Tip of note, prompts in expressions have a length. In this case, the prompt was defaulted to UPPER, CHAR(11). This must be taken into consideration in the expression. 7

Tip of Note Optional Prompts do not return null when not selected and used in an expression. The adjacent table details values return for common prompt types. 8 Optional Prompt Type Value of Optional Prompt when not selected Character ‘ Number 0 Date ‘ ‘ with # of spaces = Prompt Length ‘ 10 spaces

Using Optional Prompts in Criteria The prompt, Career (default GRAD / UGRD) is an Optional Translate Table prompt. In combination with criteria, when a Translate Table value is not selected, the query will return values for GRAD and UGRD. If selected, the query will return values for the selected value. 9

The Criteria needed to achieve the desired results. Tip of note, optional prompts in criteria result in null when not selected. 10

Default output, prompt not chosen, by default Career is GRAD or UGRD Output where Career is selected as NCRD Term: 2188 Term Career Count Distinct ID 2188 GRAD 18251 2188 UGRD 91619 Career Count Distinct ID NCRD 195 Career (default GRAD or UGRD) : NCRD, Term: 2188 Term 2188 11 2 1

Using Today’s Date as a Default Value in a Date Prompt The prompt, Date is a Date prompt with Default Value of the current date. 12

The prompt defaults to the current date with a calendar selector to change the prompt value. Tip of note, common Meta data values include: %Date. Time %Operator. Id 13

Tip Alert – Prompts can be used in an Edit List, I recommend making : 2, : 3, : 4, : 5, : 6 Optional. This results in, A. EMPLID IN (: 1, : 2, : 3, : 4, : 5, : 6) 14

Creating and Using a Custom List of Values Prompt The prompt, List of Campus ID’s (w/’, ’s) is a CHAR(200) prompt. Nothing magical about 200, it allows roughly 30 values and fit the browser without wrapping! The Upper format is important because Campus ID’s are upper case. 15

Note – The input string allows spaces and upper and lower case. Values though must be separated by commas. Create the following expression to be used in criteria. 16

INSTR(', ' || REPLACE(: 1, ' ') || ', ' , ', ' || A. CAMPUS_ID || ', ’) Creates this string (UPPER from Prompt format): , LLA 2, JXC 181, JMC 5558, JCC 5081, RHE 2, DMF 29, RTF 124, REPLACE function replaces a sequence of characters in a string with another set of characters. REPLACE(string 1, string_to_replace, [, replacement_string]) In this expression, string 1 = : 1 (prompt), string_to replace = all ‘ ‘s, replacement_string omitted removes all occurrences of the string_to_replace (removes all spaces) commas are concatenated on both ends. This is necessary to unify format of the 1 st and last values. INSTR function returns the location, as an integer, of a substring in a string. INSTR(string, substring) In this expression, string = , LLA 2, JXC 181, JMC 5558, JCC 5081, RHE 2, DMF 29, RTF 124, substring = , CAMPUS_ID, (CAMPUS_ID is a field in record SCC_PERS_SA_WV , alias A) Note, commas are concatenated before and after the field. 17

Set the expression not equal to 0 to return the following results: List of Campus ID's (w/ ', ' s) : LLA 2, Campus ID DMF 29 JCC 5081 JMC 5558 JXC 181 LLA 2 RHE 2 RTF 124 18 jxc 181 , JMC 5558 , JCC 5081, r. He 2 , DMF 29, rtf 12, RTF 124

Creating and Using a custom List of Values Prompt Optional Same prompt (Slide ##) renamed, Campus ID’s (w/’, ’s) optional is a CHAR(200) Upper prompt. Note, the Optional check box is irrelevant with No Table Edit. 19

Note – The input string is now optional (omitted, it returns all values). It allows spaces and mixed case, . Values must be separated by commas. Create the following expression to be used in criteria. 20

DECODE(REPLACE(: 1, ' '), '', 1, instr(', ' || REPLACE(: 1, ' ') || ', ' , ', ' || A. CAMPUS_ID || ', ’)) REPLACE(: 1, ‘ ‘) this removes all spaces so that I do not have to compare to ‘ 200 spaces’ Tip – DO NOT worry about the expression syntax … just copy it, trust it, and replace the field, as needed. DECODE(REPLACE(: 1, ' '), '', 1, instr(', ' || REPLACE(: 1, ' ') || ', ' , ', ' || B. ACAD_PROG || ', ')) DECODE(REPLACE(: 1, ' '), '', 1, instr(', ' || REPLACE(: 1, ' ') || ', ' , ', ' || C. EMPL_ID || ', ’)) In this query, additional criteria A. CAMPUS_ID not equal to ‘ ‘ was needed to exclude unwanted data. 21

Create a List of Values Criteria using Comparison Conditions. in list: equal to ANY() Note, because A. ITEM_TYPE is CHAR(12) the values in the expression must be in quotes and separated by commas. A little Excel trickery will convert a column of values to a quoted list, separated by commas. 22

Create a List of Values Criteria using Comparison Conditions. not in list: to ALL() not equal Note, because A. CLASS_NBR is Num 5. 0 the values in the expression have no quotes and are separated by commas. A little Excel trickery will convert a column of values to a list, separated by commas. 23

Create RIGHT Function (Using SUBSTR) SUBSTR(A. DESCR, -4) LEFT Function (Using SUBSTR) SUBSTR(A. DESCR, 1, 6) LEFT Function (Using SUBSTR & INSTR) ' ') - 1) 24 SUBSTR(A. DESCR, 1, INSTR(A. DESCR,

Output from SUBSTR expressions for RIGHT(), LEFT() Acad Year Term A. DESCR RIGHT(4) LEFT(6) INSTR(A. DESCR, ' ‘) 1 SUBSTR(A. DESCR, 1, INSTR(A. DESCR. ' ') - 1) 2017 2175 Summer 2017 Summer 6 Summer 2017 2178 Fall 2017 Fall 2 4 Fall 2017 2181 Spring 2018 Spring 6 Spring 25

Tips when Working with DATES the following field list and criteria: Field List Col Record. Fieldname Criteria Format Heading Text Logical 1 B. LAST_UPD_DT_STMP - Last Update Date Stamp 2 B. LAST_UPD_TM_STMP - Last Update Time Stamp Date Time Last Upd Date Last Upd Time AND 3 A. TERM_BEGIN_DT - Term Begin Date 4 A. TERM_END_DT - Term Ending Date Begin Date End Date AND 5 6 7 8 9 Num 5. 0 Num 6. 3 Char 18 Num 5. 0 Num 6. 3 using substr(sysdate, 1, 10) sysdate current_date AND AND cast(A. TERM_END_DT as date) - sysdate cast(A. TERM_END_DT as date) - cast(substr(sysdate, 1, 10) as date) to_char(sysdate, 'DD-MON-YY HH: MI: SS') to_char(current_date, 'DD-MON-YY HH: MI: SS') Expression 1 Condition Type Expression 2 A. STRM - Term A. ACAD_CAREER - Academic Career equal to A. ACAD_CAREER - Academic Career A. STRM - Term B. LAST_UPD_DT_STMP - Last Update Date Stamp B. STDNT_POSITIN - Student Position B. CLASS_NBR - Class Nbr sysdate equal to 2198 UGRD B. ACAD_CAREER - Academic Career B. STRM - Term equal to greater than 2019 -07 -31 2070 21812 substr(sysdate, 1, 10) result in the following output: 26 Last Upd Date Last Upd Time 7/31/2019 12: 37: 52 AM Begin Date End Date 8/26/2019 12/20/2019 Num 5. 0 Num 6. 3 using substr(sysdate, 1, 10) sysdate current_date 91 91. 362 92. 000 19 -SEP-19 03: 18: 23

the following field list and criteria: Field List Col Record. Fieldname Criteria Format Heading Text Logical 1 B. LAST_UPD_DT_STMP - Last Update Date Stamp 2 B. LAST_UPD_TM_STMP - Last Update Time Stamp Date Time Last Upd Date Last Upd Time AND 3 A. TERM_BEGIN_DT - Term Begin Date 4 A. TERM_END_DT - Term Ending Date Begin Date End Date AND 5 6 7 8 Num 5. 0 Num 6. 3 Char 18 Num 5. 0 Num 6. 3 using substr(sysdate, 1, 10) sysdate AND AND cast(A. TERM_END_DT as date) - sysdate cast(A. TERM_END_DT as date) - cast(substr(sysdate, 1, 10) as date) to_char(sysdate, 'DD-MON-YY HH: MI: SS') 9 to_char(current_date, ’DD-MON-YY HH: MI: SS’) Char 18 current_date AND Expression 1 Condition Type Expression 2 A. STRM - Term A. ACAD_CAREER - Academic Career equal to A. ACAD_CAREER - Academic Career A. STRM - Term B. LAST_UPD_DT_STMP - Last Update Date Stamp B. STDNT_POSITIN - Student Position B. CLASS_NBR - Class Nbr sysdate B. LAST_UPD_DT_STMP - Last Update Date Stamp equal to 2198 UGRD B. ACAD_CAREER - Academic Career B. STRM - Term equal to greater than 2019 -07 -31 2070 21812 substr(sysdate, 1, 10) less than substr(sysdate, 1, 10) Result in the following Query SQL: SELECT TO_CHAR(B. LAST_UPD_DT_STMP, 'YYYY-MM-DD'), TO_CHAR(CAST((B. LAST_UPD_TM_STMP) AS TIMESTAMP), 'HH 24. MI. SS. FF'), TO_CHAR(A. TERM_BEGIN_DT, 'YYYY-MM-DD'), TO_CHAR(A. TERM_END_DT, 'YYYY-MM-DD'), cast( TO_CHAR(A. TERM_END_DT, 'YYYY-MM-DD') as date) - sysdate, cast( TO_CHAR(A. TERM_END_DT, 'YYYY-MM-DD') as date) - cast(substr(sysdate, 1, 10) as date), to_char(sysdate, 'DD-MON-YY HH: MI: SS'), to_char(current_date, 'DD-MON-YY HH: MI: SS') FROM PS_TERM_TBL A, PS_STDNT_ENRL B WHERE ( A. STRM = '2198' AND A. ACAD_CAREER = 'UGRD' AND A. ACAD_CAREER = B. ACAD_CAREER AND A. STRM = B. STRM AND B. LAST_UPD_DT_STMP = TO_DATE('2019 -07 -31', 'YYYY-MM-DD') AND B. STDNT_POSITIN = 2070 AND B. CLASS_NBR = 21812 AND sysdate > substr(sysdate, 1, 10) AND B. LAST_UPD_DT_STMP < substr(sysdate, 1, 10)) 27

Key Points from previous two slides • Date and Time format fields are evaluated as characters - TO_CHAR(B. LAST_UPD_DT_STMP, 'YYYYMM-DD’) • Change the date field to a date to perform date difference - cast(A. TERM_END_DT as date) - sysdate • Use SUBSTR(date field, 1, 10) to prevent unwanted results cast(A. TERM_END_DT as date) - cast(substr(sysdate, 1, 10) as date) • sysdate and current_date may be interchangeable but you better check that to_char(sysdate, 'DD-MON-YY HH: MI: SS’) = to_char(current_date, 'DD-MON-YY HH: MI: SS’) • Date fields and date constants in criteria as evaluated appropriately B. LAST_UPD_DT_STMP = TO_DATE('2019 -07 -31', 'YYYY-MM-DD’) B. LAST_UPD_DT_STMP < substr(sysdate, 1, 10) 28

Questions? 29