INTRO TO PS QUERY WORKSHOP NERUG Alliance 2018

INTRO TO PS QUERY WORKSHOP NERUG Alliance 2018 Monday, September 24 th - Session 8132

PRESENTER • Gregg Jenczyk Applications Programmer / Analyst Williams College gj 3@williams. e

Williams College Established in 1793 with funds bequeathed by Colonel Ephraim Williams, the college is private, residential, and liberal arts, with graduate programs in the history of art and in development economics. The undergraduate enrollment is approximately 2, 000 students. The student-faculty ratio is 7: 1. Williams admits U. S. students without regard to their ability to pay. The college meets 100 percent of every admitted student’s demonstrated financial need for four years. More than half of all

WHAT WE’VE GOT Campus Solutions 9. 2 P UM 009 People. Tools 8. 55 Upgraded in February of 2017 Also use People. Soft Human Resources & Financials

WHAT’S YOUR LEVEL OF PS QUERY EXPERIENCE? Run PS Queries? Write PS Queries? Join tables? Use Expressions or Prompts? Schedule Queries?

OVERVIEW Relational Database & SQL Querying Metadata: Records and Fields Building a PS Query Records & Fields Criteria Expressions Prompts Having View SQL Run Output Formats Basics

OVERVIEW (CONTINUED) Joins & Unions In Depth Expressions Aggregates Query Administration Wrapping it Up Questions

RELATIONAL DATABASE & SQL BASICS

RELATIONAL DATABASE A Database structured to recognize relations among stored items of information. Contains a series of tables organized by key fields and unique IDs. Tables are made up of columns and rows. Relational databases are created and managed using Structured Query Language (SQL) Through SQL logic, related tables can be make a virtual table Tables (Physical) vs Lingo: Primary Key, Foreign Key, Super Key, Composite Key, Compound Key Views (Virtual) joined to

A TYPICAL TABLE: “ CLASS_TABLE Unique Key Rows ” Columns

A TYPICAL TABLE WITH SQL SELECT Class_ID, Subject, Catalog_Number, Classroom FROM Class_Table WHERE Term = ‘ 2158’ AND Subject = ‘ENG’ ORDER BY Catalog_Number

OUTER JOIN VS INNER JOIN You can outer join as many tables as you want Once a table is an outer join, it cannot be joined to any more tables

RECORD (TABLE) JOINS Inner Join Left Outer Join (Exclude B with Where) Full Outer Join Left Outer Join Full Outer Join (Exclude B with Where)

INNER JOIN – STANDARD JOIN IN PS INNER JOIN Table A Last_Name Jackson Johnson Thompson Class. ID 125 214 479 • Select * From A Inner Join B On A. Class. ID = B. Class. ID; A. Last_Name Jackson A. Class. ID 125 B. Class. ID 125 Table B Class. ID 125 474 559 Class_Room RB 102 QA 405 TE 309 • Select * From A, B Where A. Class. ID = B. Class. ID; B. Class_Room RB 102

LEFT OUTER JOIN Table A LEFT OUTER JOIN Last_Name Jackson Johnson Thompson Table B Class. ID 125 214 479 Class. ID 125 474 559 Select * From A Left Outer Join B ON A. Class. ID = B. Class. ID; A. Last_Name Jackson Johnson Thompson A. Class. ID 125 214 479 B. Class. ID 125 B. Class_Room RB 102 QA 405 TE 309

SELF JOIN – JOIN A TABLE WITH ITSELF • Consider an Employee Table in which there are employees and their managers • Use Self Join to select an employee name and their manager’s name SELECT e 1. name As “Employee Name” , e 2. name As "Manager Name" FROM Employee e 1 JOIN Employee e 2 ON e 1. mgr = e 2. empno;

SELF JOIN – JOIN A TABLE WITH ITSELF Table A Class_ID Term Subject Class_Room 7854 2158 ENG MB 102 6543 2158 BIL MM 207 3982 2158 MTH MM 115 2098 2158 CHM SCB 453 7854 2159 ENG MB 102 SELECT X. * FROM A As X INNER JOIN (SELECT Y. Class_ID, Y. MAX(Term) AS Term FROM A as Y GROUP BY Y. Class_ID) Z ON Z. Class_ID = X. Class_ID AND Z. Term = X. Term; SELECT A. * FROM A Where A. Term = (SELECT MAX(B. Term) FROM A as B Where B. Class_ID = A. Class_ID); Class ID Term Subject Class Room 7854 2159 ENG MB 102 6543 2158 BIL MM 207 3982 2158 MTH MM 115 2098 2158 CHM SCB 453

SOME PS CS TABLES PS_TERM_TBL - Setup table with all terms PS_COUNTRY_TBL - Setup table with all countries PS_NAMES - Data table with student names PS_ADDRESSES - Data table with student addresses PS_ACAD_PROG - Data table with student academic programs PS_ACAD_PLAN - Data table with student academic plans

VIEWS Views are stored SQL Select statements that act like tables for query purposes. Views can greatly simplify some queries. A People. Soft Campus Solutions database contains ~23, 000 tables and ~24, 000 views.

A PS CS VIEW: PS_SCC_PRFONLYNMVW

QUERYING

WHY DO WE QUERY? Campus Solutions is a set of roughly 23 K tables – some house very basic and fundamental data (PS_PERSON, PS_ACAD_PLAN), some are only used once, some are ghost towns (like HCM tables in CS ), and some are built on the spot when we request data from them These tables are managed using People. Code, People S oft’s proprietary coding language The table information is presented by means of the Peoplesoft Pure Internet Architecture ( you won’t necessarily see all of the columns in a table on a page! The Campus Solutions application relies on thousands of relationship s between tables. P IA)–

WHY DO WE QUERY? With so much data stored in so many tables, we need ways to efficiently retrieve meaningful information This means joining tables together where necessary and then selecting filtering sorting summarizing generating output To that end, we have PS Query

PS QUERY Gives us a platform for finding the right tables & views Provides us with important information on each table – and a nice search tool to find them Makes it possible to create a basic query statement without writing your own SQL Organizes our data for us, and provides Helps us with relationships Provides a blend of output options and field names functional and technical information

THE BASIC QUERY STATEMENT Select [field(s)] From [table(s) , view(s) ] Where [criteria]

THE BASIC QUERY STATEMENT Step 1 - know what Step 2 - find out what fields you want ( Select) tables you need ( From) Step 3 – define your criteria ( Where)

METADATA RECORDS & FIELDS

METADATA: RECORDS AND FIELDS Metadata - data about data Record - table and view metadata, stored in the PS database Field - items of information, stored in record definitions

METADATA: RECORDS AND FIELDS

METADATA: RECORDS AND FIELDS

METADATA: RECORDS AND FIELDS

METADATA: RECORDS AND FIELDS

METADATA: RECORDS AND FIELDS

BUILDING A PS QUERY

RECORDS Use the Search feature to find your records (tables & views) Bring in records one at a time Know your criteria, and then join Start simple and test as you go

RECORDS

QUERY Start with the essential fields Always check to make sure your table makes sense before joining it in

FIELDS You can use the field tab to define the display information for your fields. Based on the tables you choose, these will be defined for you – however (as with everything else) they are configurable You can always change the field header Keep an eye out for translate values that can display several different sets of values based on your choice

FIELDS Fields with coded values often have associated Translate Values. You can choose to display the code, the short translate value or the long translate value in your query output. Select no translate, short or long translate value display Query output showing long translate values

FIELDS Use the Reorder/Sort button to change the order of fields in the query output and/or to set or update the sort order.

CRITERIA Define your fields Choose your Condition (and, or, >, <, >=, <=, between, in list, like, equal to, not in list, not like) Be smart with your Conditions!

CRITERIA CONT’D

EXPRESSIONS A means of further defining your SQL statement Many Oracle functions can be used in expressions The internet is a great resource to find expressions using Oracle functi ons

EXPRESSIONS Concatenation: ‘Name: ‘ || N. FIRST_NAME || ‘ ‘ || N. LAST_NAME Addition: C. TUITION_AMT + D. BOARD_AMT + F. ROOM_AMT Subtraction: A. TUITION_AMT - B. LOAN. AMT Multiplication: F. QUANTITY * K. UNIT_PRICE Division: T. TOTAL_AMT / Q. QUANTITY Date Arithmetic: ADD_MONTHS(D. ENROLL_DT, 6)

PROMPTS Prompts are also known as bind variables Prompts are completely user configurable – you can decide the length, the format, the edit type*, and specify a default value

PROMPTS

HAVING (AND AGGREGATE FUNCTIONS) Only used with aggregate field functions If you have an aggregate field, it must be in a having criteria

VIEW SQL Check your work!! Always come back to this tab to see how the criteria you add factor into the SQL statement. If you can read it here, you can write it in SQL

RUN Once you run the query, you can download the output to excel Rerun your query to enter new bind variables (prompts values)

OUTPUT FORMATS § Query allows you to run the results to the browser (HTML), Excel, or XML from the Search page § You are able to export Query results to Excel or XML from the Run tab when editing a query § An XML output file is a must when building BI Publisher reports – this is where you can get it

Joins & Unions

JOINS Query allows two types of joins: Standard (Inner) and Left outer The tool will present you with a list of common fields to join on. If there are no common fields you will get every row in table A joined with every row in table B. Be very careful before running this!!

UNION QUERIES Unions allow merging data from 2 query sets when it is impossible to get data from one. Example: Fetch a list of admitted applicants and enrolled students. 1. 2. 3. 4. Create a query for admitted applicants Click "New Union" at bottom of page Create another query for enrolled student query Have to use all the same columns Notes: • Use Subquery/Union Navigation link • Save the query before running

UNION QUERIES

In Depth Expressions

EXPRESSIONS IN QUERY MANAGER To display the expression in the output, select "Use as Field" Things to watch out for when declaring an expression: Datatype mismatches Differences in lengths of expression and returned value All possible output values There are multiple functions to achieve the same result. Explore, Discover, Experiment, Master

WORKING WITH NUMBERS CEILING – Provides the largest integer greater than or equal to a decimal number CEILING (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)

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

MANIPULATING STRING VALUES SUBSTR('This is a test', 6, 2) Result: 'is' SUBSTR('This is a test', 6) Result: 'is a test' SUBSTR('Tech. On. The. Net', 1, 4) Result: 'Tech' SUBSTR('Tech. On. The. Net', -3, 3) Result: 'Net‘ https: //www. techonthenet. com

MANIPULATING STRING VALUES RPAD('tech', 8, '0') Result: 'tech 0000' 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

MANIPULATING STRING VALUES TRIM(' tech ') Result: 'tech' TRIM(' ' FROM ' tech ') Result: 'tech' TRIM(LEADING '0' FROM '000123') Result: '123' TRIM(TRAILING '1' FROM 'Tech 1') Result: 'Tech' TRIM(BOTH '1' FROM '123 Tech 111') Result: '23 Tech' https: //www. techonthenet. com

MANIPULATING STRING VALUES NVL(A. FIRST_NAME, ‘ ’) Returns First name if available, if not returns the string ‘ ’ NVL 2(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. c

DATE FORMATTING TO_CHAR(sysdate, 'yyyy/mm/dd') Result: '2003/07/09' TO_CHAR(sysdate, 'Month DD, YYYY') Result: 'July 09, 2003‘ TO_DATE('2003/07/09', 'yyyy/mm/dd') TO_DATE('2015/05/15 8: 30: 25', 'YYYY/MM/DD HH: MI: SS') When comparing Date values with datetime values use date_occured >= TRUNC(SYSDATE - 30) TRUNC(‘Date. Value’)

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’, ‘Tuesday’, ‘Wednesday’, ‘Thursday’, ‘Friday’, ‘Weekend!’)

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

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)

Aggregates

AGGREGATE 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 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.

AGGREGATE VALUES

AGGREGATE VALUES

AGGREGATE VALUES - LISTAGG

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;

QUERY ADMINISTRATION NAVIGATION AND FEATURES HOW AND WHERE TO USE TIPS AND TRICKS

QUERY ADMINISTRATION • • Query Administration provides users with the ability to manage, monitor and mitigate queries. The feature is controlled by People. Soft security and is not part of the Report Manager menu structure. Navigation: People. Tools > Utilities > Administration > Query Administration Some of the functions provided by this functionality include: • Assign a private query to other users • Manage queries designed by blocked or terminated users. • Rename or Delete a query • Move a query to a folder • Turn query logging on/off from runs • Monitor execution statistics for queries • Set timeout for query runs • Kill queries that are hung in running state.

QUERY ADMINISTRATION

QUERY ADMINISTRATION On Admin Tab

QUERY ADMINISTRATION On Executing Tab

WRAPPING IT UP Always define some criteria first (you don’t want to try and retrieve 100 K rows of data) Limit your results with criteria/prompts when you are testing. Check and save your w ork early and often Have good joining practices (don’t automatically go with what PSQuery gives you, double check) Think about what you are asking the database to provide you with Look at your SQL statement – understanding how the criteria factor into the query is the key Use Query Administration to kill queries if necessary If you have the ability, you might want to consider building a SQL view to do the heavy lifting and plug that view into the Query tool Chances are if you’re having a problem, someone else ran into something similar and posted the solution online. Google is your friend!

APPENDIX: USEFUL CAMPUS SOLUTIONS TABLES & VIEWS PERSONAL_DATA - Reporting table, 1 row person NAMES - Student names, effective-dated ADDRESSES - Student addresses, effective-dated PSXLATITEM - Translate values, effective-dated ACAD_PROG - Student academic programs ACAD_PLAN - Student academic plans See http: //peoplesoft. wikidot. com/campus-solutions-tables for a fairly comprehensive list of CS

USEFUL CAMPUS SOLUTIONS TABLES

Questions?

CREDITS Special thanks to Sam Shunk and Rahul Nori for content from their Young Professionals Group (YPG) Query 101 and Query 102 presentations.

THANKS!!