Using PS Query for Effective Dated Tables in PeopleSoft

1. PeopleSoft Query 201 4/4/2019 3:30 -4:30 Room H Tara O’Neil

2. Tara O’Neil Associate Director Marquette University tara.oneil@marquette.edu

3. Agenda • Using PS Query to help your office including how to use effective dated tables in more complex ways with effective sequences and action dates. Show how to accurately query such tables. It will also enhance your Query results with Drilling URLs, Pagelets and BI Publisher reports. • Create Query results that are user-friendly, easy-to-read, and useful. Make your Query results actionable. This presentation is for everyone, functional and technical alike.

4. Effective Dated Tables • Using PS Query to help your office including how to use effective dated tables in more complex ways with effective sequences and action dates.

5. Effective Dated Tables • Like pages in a diary • Most times you do want the most recent entry • Sometime you want to know if something ever happened • For example ISIR transactions • Did they ever have a IRS DRT = 02 *AND* • Most recent transaction says WILL FILE or WON’T FILE

6. Effective Dated Tables • Like pages in a diary • Most times you do want the most recent entry • Sometime you want to know if something ever happened • For example ISIR transactions • Did they ever have a IRS DRT = 02 *AND* • Most recent transaction says WILL FILE or WON’T FILE SELECT B.EMPLID FROM SYSADM.PS_ISIR_CONTROL B WHERE B.AID_YEAR = '2020' AND B.SFA_STU_IRS_REQUST = '02‘ **no effective dated join**

7. Effective Dated Tables SELECT C.EMPLID FROM SYSADM.PS_ISIR_CONTROL C WHERE C.AID_YEAR = A.AID_YEAR AND C.SFA_STU_IRS_REQUST = '02‘ **no effective dated join**

8. Effective Dated Tables SELECT A.EMPLID, A.ETI_DEST_CD, A.ISIR_TXN_NBR, TO_CHAR(A.TRANS_RECEIPT_DT,'YYYY-MM-DD'), A.SFA_STU_IRS_REQUST, A.SFA_PAR_IRS_REQUST, A.ADJ_EFC   FROM PS_FAN_ISIR_CTL_VW A, PS_FAN_FA_TERM_VW B   WHERE ( A.AID_YEAR = '2020'      AND A.EMPLID = B.EMPLID      AND A.INSTITUTION = B.INSTITUTION      AND A.AID_YEAR = B.AID_YEAR      AND B.STRM = '1610'      AND B.ACAD_CAREER = 'UGRD'      AND B.EFF_STATUS = 'A') SELECT C.EMPLID FROM SYSADM.PS_ISIR_CONTROL C WHERE C.AID_YEAR = A.AID_YEAR AND C.SFA_STU_IRS_REQUST = '02‘ **no effective dated join**

9. Effective Dated Tables SELECT A.EMPLID, A.ETI_DEST_CD, A.ISIR_TXN_NBR, TO_CHAR(A.TRANS_RECEIPT_DT,'YYYY-MM-DD'), A.SFA_STU_IRS_REQUST, A.SFA_PAR_IRS_REQUST, A.ADJ_EFC   FROM PS_FAN_ISIR_CTL_VW A, PS_FAN_FA_TERM_VW B   WHERE ( A.AID_YEAR = '2020'      AND A.EMPLID = B.EMPLID      AND A.INSTITUTION = B.INSTITUTION      AND A.AID_YEAR = B.AID_YEAR      AND B.STRM = '1610'      AND B.ACAD_CAREER = 'UGRD'      AND B.EFF_STATUS = 'A'      AND A.EMPLID IN (SELECT C.EMPLID   FROM PS_ISIR_CONTROL C   WHERE C.SFA_STU_IRS_REQUST = '02'      AND A.AID_YEAR = C.AID_YEAR)) SELECT C.EMPLID FROM SYSADM.PS_ISIR_CONTROL C WHERE C.AID_YEAR = A.AID_YEAR AND C.SFA_STU_IRS_REQUST = '02‘ **no effective dated join**

10. Effective Dated TablesNOW UNION WITH PARENT

11. Effective Dated TablesNOW UNION WITH PARENT SELECT A.EMPLID, A.ETI_DEST_CD, A.ISIR_TXN_NBR, TO_CHAR(A.TRANS_RECEIPT_DT,'YYYY-MM-DD'), A.SFA_STU_IRS_REQUST, A.SFA_PAR_IRS_REQUST, A.ADJ_EFC_CALC_REQ   FROM PS_FAN_ISIR_CTL_VW A, PS_FAN_FA_TERM_VW B   WHERE ( A.AID_YEAR = '2020'      AND A.EMPLID = B.EMPLID      AND A.INSTITUTION = B.INSTITUTION      AND A.AID_YEAR = B.AID_YEAR      AND B.STRM = '1610'      AND B.ACAD_CAREER = 'UGRD'      AND B.EFF_STATUS = 'A'      AND A.EMPLID IN (SELECT C.EMPLID   FROM PS_ISIR_CONTROL C   WHERE C.SFA_STU_IRS_REQUST = '02'      AND A.AID_YEAR = C.AID_YEAR)      AND A.SFA_STU_IRS_REQUST <> '02'      AND A.EFC_STATUS = 'O') UNION SELECT D.EMPLID, D.ETI_DEST_CD, D.ISIR_TXN_NBR, TO_CHAR(D.TRANS_RECEIPT_DT,'YYYY-MM-DD'), D.SFA_STU_IRS_REQUST, D.SFA_PAR_IRS_REQUST, D.ADJ_EFC_CALC_REQ   FROM PS_FAN_ISIR_CTL_VW D, PS_FAN_FA_TERM_VW E   WHERE ( D.EMPLID = E.EMPLID      AND D.INSTITUTION = E.INSTITUTION      AND D.AID_YEAR = E.AID_YEAR      AND D.AID_YEAR = '2020'      AND E.STRM = '1610'      AND E.ACAD_CAREER = 'UGRD'      AND E.EFF_STATUS = 'A'      AND D.EMPLID IN (SELECT F.EMPLID   FROM PS_ISIR_CONTROL F   WHERE F.SFA_PAR_IRS_REQUST = '02'      AND F.AID_YEAR = D.AID_YEAR)      AND D.SFA_PAR_IRS_REQUST <> '02'      AND D.EFC_STATUS = 'O')

12. Effective Dated TablesNOW UNION WITH PARENT SELECT A.EMPLID, A.ETI_DEST_CD, A.ISIR_TXN_NBR, TO_CHAR(A.TRANS_RECEIPT_DT,'YYYY-MM-DD'), A.SFA_STU_IRS_REQUST, A.SFA_PAR_IRS_REQUST, A.ADJ_EFC_CALC_REQ   FROM PS_FAN_ISIR_CTL_VW A, PS_FAN_FA_TERM_VW B   WHERE ( A.AID_YEAR = '2020'      AND A.EMPLID = B.EMPLID      AND A.INSTITUTION = B.INSTITUTION      AND A.AID_YEAR = B.AID_YEAR      AND B.STRM = '1610'      AND B.ACAD_CAREER = 'UGRD'      AND B.EFF_STATUS = 'A'      AND A.EMPLID IN (SELECT C.EMPLID   FROM PS_ISIR_CONTROL C   WHERE C.SFA_STU_IRS_REQUST = '02'      AND A.AID_YEAR = C.AID_YEAR)      AND A.SFA_STU_IRS_REQUST <> '02'      AND A.EFC_STATUS = 'O') UNION SELECT D.EMPLID, D.ETI_DEST_CD, D.ISIR_TXN_NBR, TO_CHAR(D.TRANS_RECEIPT_DT,'YYYY-MM-DD'), D.SFA_STU_IRS_REQUST, D.SFA_PAR_IRS_REQUST, D.ADJ_EFC_CALC_REQ   FROM PS_FAN_ISIR_CTL_VW D, PS_FAN_FA_TERM_VW E   WHERE ( D.EMPLID = E.EMPLID      AND D.INSTITUTION = E.INSTITUTION      AND D.AID_YEAR = E.AID_YEAR      AND D.AID_YEAR = '2020'      AND E.STRM = '1610'      AND E.ACAD_CAREER = 'UGRD'      AND E.EFF_STATUS = 'A'      AND D.EMPLID IN (SELECT F.EMPLID   FROM PS_ISIR_CONTROL F   WHERE F.SFA_PAR_IRS_REQUST = '02'      AND F.AID_YEAR = D.AID_YEAR)      AND D.SFA_PAR_IRS_REQUST <> '02'      AND D.EFC_STATUS = 'O')

13. Effective Dated TablesNOW UNION WITH PARENT SELECT A.EMPLID, A.ETI_DEST_CD, A.ISIR_TXN_NBR, TO_CHAR(A.TRANS_RECEIPT_DT,'YYYY-MM-DD'), A.SFA_STU_IRS_REQUST, A.SFA_PAR_IRS_REQUST, A.ADJ_EFC_CALC_REQ   FROM PS_FAN_ISIR_CTL_VW A, PS_FAN_FA_TERM_VW B   WHERE ( A.AID_YEAR = '2020'      AND A.EMPLID = B.EMPLID      AND A.INSTITUTION = B.INSTITUTION      AND A.AID_YEAR = B.AID_YEAR      AND B.STRM = '1610'      AND B.ACAD_CAREER = 'UGRD'      AND B.EFF_STATUS = 'A'      AND A.EMPLID IN (SELECT C.EMPLID   FROM PS_ISIR_CONTROL C   WHERE C.SFA_STU_IRS_REQUT = '02'      AND A.AID_YEAR = C.AID_YEAR)      AND A.SFA_STU_IRS_REQUST <> '02'      AND A.EFC_STATUS = 'O') UNION SELECT D.EMPLID, D.ETI_DEST_CD, D.ISIR_TXN_NBR, TO_CHAR(D.TRANS_RECEIPT_DT,'YYYY-MM-DD'), D.SFA_STU_IRS_REQUST, D.SFA_PAR_IRS_REQUST, D.ADJ_EFC_CALC_REQ   FROM PS_FAN_ISIR_CTL_VW D, PS_FAN_FA_TERM_VW E   WHERE ( D.EMPLID = E.EMPLID      AND D.INSTITUTION = E.INSTITUTION      AND D.AID_YEAR = E.AID_YEAR      AND D.AID_YEAR = '2020'      AND E.STRM = '1610'      AND E.ACAD_CAREER = 'UGRD'      AND E.EFF_STATUS = 'A'      AND D.EMPLID IN (SELECT F.EMPLID   FROM PS_ISIR_CONTROL F   WHERE F.SFA_PAR_IRS_REQUST = '02'      AND F.AID_YEAR = D.AID_YEAR)      AND D.SFA_PAR_IRS_REQUST <> '02'      AND D.EFC_STATUS = 'O')

14. Effective Dated Tables SELECT A.EMPLID, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.EFFSEQ, A.ISIR_TXN_NBR   FROM PS_ISIR_CONTROL A   WHERE ( A.EFFDT =         (SELECT MAX(A_ED.EFFDT) FROM PS_ISIR_CONTROL A_ED         WHERE A.EMPLID = A_ED.EMPLID           AND A.INSTITUTION = A_ED.INSTITUTION           AND A.AID_YEAR = A_ED.AID_YEAR           AND A_ED.EFFDT <= SYSDATE)     AND A.EFFSEQ =         (SELECT MAX(A_ES.EFFSEQ) FROM PS_ISIR_CONTROL A_ES         WHERE A.EMPLID = A_ES.EMPLID           AND A.INSTITUTION = A_ES.INSTITUTION           AND A.AID_YEAR = A_ES.AID_YEAR           AND A.EFFDT = A_ES.EFFDT)      AND A.CORRECTION_STATUS = '3')

15. Effective Dated Tables SELECT A.EMPLID, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.EFFSEQ, A.ISIR_TXN_NBR   FROM PS_ISIR_CONTROL A   WHERE ( A.CORRECTION_STATUS = '3'      AND A.EFFDT = (SELECT MAX( B.EFFDT)   FROM PS_ISIR_CONTROL B   WHERE B.CORRECTION_STATUS = '3'      AND A.EMPLID = B.EMPLID      AND A.INSTITUTION = B.INSTITUTION      AND A.AID_YEAR = B.AID_YEAR)      AND A.EFFSEQ = (SELECT MAX( C.EFFSEQ)   FROM PS_ISIR_CONTROL C   WHERE C.CORRECTION_STATUS = '3'      AND A.EMPLID = C.EMPLID      AND A.INSTITUTION = C.INSTITUTION      AND A.AID_YEAR = C.AID_YEAR      AND A.EFFDT = C.EFFDT)

16. CASE STATEMENTS CASE WHEN ISIR_STUDENT.TAX_RETURN_FILED = ' ' THEN 'BLANK' WHEN ISIR_STUDENT.TAX_RETURN_FILED = '2' THEN 'WILL' WHEN ISIR_STUDENT.TAX_RETURN_FILED = '1' THEN 'COMPLETE' WHEN ISIR_STUDENT.TAX_RETURN_FILED = '3' THEN 'WONT' END AS STU_TAX_ST

17. CASE STATEMENTS CASE WHEN ISIR_STUDENT.TAX_RETURN_FILED = ' ' THEN 'BLANK' WHEN ISIR_STUDENT.TAX_RETURN_FILED = '2' THEN 'WILL' WHEN ISIR_STUDENT.TAX_RETURN_FILED = '1' THEN 'COMPLETE' WHEN ISIR_STUDENT.TAX_RETURN_FILED = '3' THEN 'WONT' END AS STU_TAX_ST

18. Expressions Manipulating data with Expressions

19. Working with numbers • ROUND – Rounds a decimal number to specified number of digits • ROUND(12.3456,1) = 12.3 • ROUND(12.3456,2) = 12.35 • SQRT – Provides the Square root of the number. Similarly, POWER, EXP etc • SQRT(144) = 12 • CEIL – Provides the largest integer greater than or equal to a decimal number • CEIL (123.45) = 124 • REGEXP_COUNT = Provides a count of characters matching the regular expression. • REGEXP_COUNT ('Anderson', 'a|e|i|o|u') = 2 • (Not 3, since it’s A and not a)

20. String Concatenation • Merge two or more string values to be displayed in 1 column of the select statement. For example: • 'People'|| 'Soft' = 'PeopleSoft' • 'People' || ' ' ' ' || 's Soft' = 'People’s Soft' • Concatenate string with record fields: • A.LAST_NAME || ‘, ‘ || A.FIRST_NAME = ‘Doe, John’ • Same result can be achieved using a key word: • CONCAT(CONCAT(A.LAST_NAME, ‘, ’), A.FIRST_NAME) = ‘Doe, John’ • Notice that the syntax of CONCAT function accepts only two strings at a time

21. Manipulating String Values Functions for String manipulation • LENGTH(String1)– Returns Length of the string. If String1 is NULL, then the LENGTH function will return NULL • INSTR( string, substring [, start_position [, th_appearance ] ] ) – Search within a string. Returns character where searched string is found in the string • SUBSTR( string, start_position [, length ] ) – Returns [length] number of characters in the string, starting at the start position • TRIM( [ [ LEADING | TRAILING | BOTH ] trim_character FROM ] string1) – Returns string1 after stripping off any characters at the start or end of the field. Can add criteria like leading, trailing or both. • RPAD( string1, padded_length [, pad_string] )– Returns string1 with pattern added onto the end specified number of times. Similarly LPAD https://www.techonthenet.com

22. Manipulating String Values • LENGTH('Tech on the Net') • Result: 15 • INSTR('Tech on the net', 'e') • Result: 2 (the first occurrence of 'e') • INSTR('Tech on the net', 'e', 1, 1) • Result: 2 (the first occurrence of 'e') • INSTR('Tech on the net', 'e', 1, 2) • Result: 11 (the second occurrence of 'e') • INSTR('Tech on the net', 'e', -3, 2) • Result: 2 https://www.techonthenet.com

23. Manipulating String Values • SUBSTR('This is a test', 6, 2) • Result: 'is' • SUBSTR('This is a test', 6) • Result: 'is a test' • SUBSTR('TechOnTheNet', 1, 4) • Result: 'Tech' • SUBSTR('TechOnTheNet', -3, 3) • Result: 'Net‘ • RPAD('tech', 8, '0') • Result: 'tech0000' • RPAD('tech on the net', 15, 'z') • Result: 'tech on the net' • RPAD('tech on the net', 16, 'z') • Result: 'tech on the netz' https://www.techonthenet.com

24. Manipulating String Values • TRIM('   tech   ') • Result: 'tech' • TRIM(' '  FROM  '   tech   ') • Result: 'tech' • TRIM(LEADING '0' FROM '000123') • Result: '123' • TRIM(TRAILING '1' FROM 'Tech1') • Result: 'Tech' • TRIM(BOTH '1' FROM '123Tech111') • Result: '23Tech' https://www.techonthenet.com

25. Manipulating String Values • NVL(A.FIRST_NAME, ‘ ’) • Returns First name if available, if not returns the string ‘ ’ • NVL2(A.AWARD_AMOUNT, ‘Student Awarded’, ‘n/a’) • Display only the last four of SSN. • ‘XXX-XX-’ + SUBSTR(A.SSN, 6) = ‘XXX-XX-1234’ https://www.techonthenet.com

26. Manipulating String Values • NVL(A.FIRST_NAME, ‘ ’) • Returns First name if available, if not returns the string ‘ ’ • NVL2(A.AWARD_AMOUNT, ‘Student Awarded’, ‘n/a’) • Display only the last four of SSN. • ‘XXX-XX-’ + SUBSTR(A.SSN, 6) = ‘XXX-XX-1234’ https://www.techonthenet.com

27. Date formatting • TO_CHAR(sysdate, 'yyyy/mm/dd') • Result: '2003/07/09' • TO_CHAR(sysdate, 'Month DD, YYYY') • Result: 'July 09, 2003’ • TO_CHAR(to_char(‘1000’, '$9,999.99’ ) • Result: $1,000.00 • TO_DATE('2003/07/09', 'yyyy/mm/dd') • TO_DATE('2015/05/15 8:30:25', 'YYYY/MM/DD HH:MI:SS') • Result: 09-JUL-03 • When comparing Date values with datetime values use TRUNC(‘DateValue’) • date_occured >= TRUNC(SYSDATE - 30)

28. Logic and Translation DECODE translates a set of values to another set of values. Provides the ability to assign a default value. DECODE(A.Day, ‘M’, ‘Monday’, ‘Tu’, ‘Tuesday’, ‘W’, ‘Wednesday’, ‘Th’, ‘Thursday’, ‘F’, ‘Friday’, ‘Holiday’)

29. Logic and Translation Select DECODE(A.supplier_id, 10000, 'IBM', 10001, 'Microsoft', 10002, 'Hewlett Packard', 'Gateway') result From Supplier_tbl A; IF supplier_id = 10000 THEN result = 'IBM'; ELSIF supplier_id = 10001 THEN result = 'Microsoft'; ELSIF supplier_id = 10002 THEN result = 'Hewlett Packard'; ELSE result = 'Gateway'; END IF; https://www.techonthenet.com

30. Logic and Translation IF supplier_id = 10000 THEN result = 'IBM'; ELSIF supplier_id = 10001 THEN result = 'Microsoft'; ELSIF supplier_id = 10002 THEN result = 'Hewlett Packard'; ELSE result = 'Gateway'; END IF; Select DECODE(A.supplier_id, 10000, 'IBM', 10001, 'Microsoft', 10002, 'Hewlett Packard', 'Gateway') result From Supplier_tbl A; https://www.techonthenet.com

31. Logic and Translation • CASE – Helps you make more complex decisions using multiple fields CASE WHEN A.Grade = ‘A’ THEN ‘Pass’ WHEN A.Grade = ‘B’ THEN ‘Pass’ WHEN A.Grade = ‘C’ THEN ‘Pass’ WHEN A.Grade = ‘I’ AND A.Extension = ‘Y’ THEN ‘Incomplete’ WHEN A.Grade = ‘I’ AND A.Extension = ‘N’ THEN ‘Fail with Incomplete’ ELSE ‘Fail’ END

32. Meta-SQL • Meta-SQL can also be used in expressions. Meta-SQL can be used to retrieve current date, current date time, operatorID, to get the difference between dates, etc. • Examples: • %OperatorID • %CurrentDateTimeIn • %CurrentDateIn • %DateDiff • See Meta-SQLfor more helpful information

33. Regular expression to replace • You can use regular expressions in PS Query. Regular expressions are used to identify a pattern within a field, and REGEXP_REPLACE can be used to replace the data pulled from a field with other data. • REGEXP_REPLACE(Field,‘pattern or value to replace’,‘ value to replace with’) • You can make these regular expressions much more complex (fun!), too, so these can be extremely flexible. • EXAMPLE: We have rich text enabled for activity guide agreement values, so when we try to pull the agreement text, HTML tags show up.

34. Using REGEXP_Replace to strip HTML tags What we are doing here is using a regular expression to replace certain HTML tag occurrences with ‘’ – effectively removing them.

35. Aggregates Aggregating data

36. Aggregating Field Values From the Fields tab, you can edit a field to choose one of the following aggregate values: • SUM – Find the sum of a numeric value for a field grouping. • COUNT – Count the rows returned in a grouping. All Rows Vs Distinct Rows • COUNT DISTINCT – counts distinct rows • MIN – Find the lowest value for a field grouping. • MAX – Find the highest value for a field grouping. • AVERAGE – Find the average, only for numbers.

37. Aggregating Field Values

38. Aggregating Field Values

39. Aggregate Expressions You can also aggregate expressions using these same values • SUM(FIELD_NAME) – Find the sum of a numeric value for a field grouping. • COUNT(FIELD_NAME) – Count the rows returned in a grouping. All Rows Vs Distinct Rows • DISTINCT COUNT(FIELD_NAME) – counts distinct rows • MIN(FIELD_NAME) – Find the lowest value for a field grouping. • MAX(FIELD_NAME) – Find the highest value for a field grouping. • AVERAGE(FIELD_NAME) – Find the average, only for numbers.

40. Aggregate Expressions The ‘Aggregate Function’ box must be checked!

41. Aggregate Values - ListAgg ListAgg is an expression that combines the data from a given field for in one row, partitioned by however you decide to group it – rather than returning data in multiple rows. Can be combined with regex_replace to remove duplicates, used within case statements, etc. https://docs.oracle.com/cloud/latest/db112/SQLRF/functions089.htm#SQLRF30030

42. Example of pulling career and app number for a given person without listagg Aggregate Values - ListAgg Same Data, but using list agg and concatenating our two fields

43. Aggregate Values - Analytics SELECT DISTINCT INSTITUTION, ACAD_CAREER, COUNT(*) AS COUNT   FROM PS_STDNT_ENRL   WHERE (INSTITUTION IN ('MY_INST_NAME')) GROUP BY CUBE(INSTITUTION, ACAD_CAREER); DISTINCT INSTITUTION, ACAD_CAREER, COUNT(*) AS COUNT   FROM PS_STDNT_ENRL   WHERE (INSTITUTION IN ('MY_INST_NAME')     AND 1 = 1) GROUP BY CUBE(INSTITUTION, ACAD_CAREER )

44. Aggregate Values - Listagg SELECT EMPLID, LISTAGG(ACAD_CAREER, ', ') WITHIN GROUP (ORDER BY ACAD_CAREER) FROM PS_STDNT_CAR_TERM WHERE INSTITUTION IN (‘MY_INST') Group by EMPLID; SELECT EMPLID, LISTAGG(ACAD_CAREER, ', ') WITHIN GROUP (ORDER BY ACAD_CAREER) As ACAD_CAREER FROM (SELECT DISTINCT EMPLID, ACAD_CAREER, INSTITUTION FROM PS_STDNT_CAR_TERM) WHERE INSTITUTION IN (MY_INST') Group by EMPLID;

45. SUM Vs SUM with GROUP BY

46. Rollup

47. Cube

48. Sub Queries How and when to use Sub Queries in PS Query

49. Why/when would we use subqueries • What is a subquery? • A query within a query, also called a “sub select” • For our purposes, we will use it to filter • A subquery can also be part of the select clause as a field – delivered outer join • Why use a subquery? • Pick one row out of multiple rows • In List Queries • Not Exist Queries

50. Why/when would we use subqueries • What is a subquery? • A query within a query, also called a “sub select” • For our purposes, we will use it to filter • A subquery can also be part of the select clause as a field – delivered outer join • Why use a subquery? • Pick one row out of multiple rows • In List Queries • Not Exist Queries