EXPRESSIONS IN PS QUERY NERUG 2019 September 24

EXPRESSIONS IN PS QUERY NERUG 2019 September 24 th, 2019

PRESENTER • Gregg Jenczyk Applications Programmer / Analyst Williams College gj 3@williams. edu • ORIGINAL PRESENTATION BY • Wood Foster-Smith SIS Data and Reporting Team Lead University of California, Berkeley

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 Williams students receive financial aid from the college.

WHAT WE’VE GOT Campus Solutions 9. 2 PUM 014 People. Tools 8. 57 Upgraded in February of 2017 Also use People. Soft Human Resources & Financials

OVERVIEW Introduction Where can I find expressions? Expressions and conditions CASE Statement Syntax Creating Expressions with CASE logic CASE expressions in SQL & PS Query CASE in action Some other useful expressions

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

HOW DO I FIND EXPRESSIONS IN PS QUERY?

IT’S IN THE QUERY MANAGER!

THIS IS WHERE THE MAGIC HAPPENS

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

EXPRESSIONS & CONDITIONS

EXPRESSIONS VS CONDITIONS An expression in SQL has a Character, Number or Date value Everything that appears on the Fields tab of a PS Query, or in the SELECT list of a SQL query, is an expression (even just a plain column name). A condition is a comparison between two expressions, and only has a value of TRUE or FALSE Uses a comparison operator like =, <, >, LIKE, or IN between the expressions (except for EXISTS subqueries) Can be compounded with Boolean logic (AND, OR, NOT) and parentheses Everything that appears on the Criteria or Having tab of a PS Query, or in the WHERE or HAVING clause of a SQL query, is a condition

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)

Conditional Expressions

WHAT IS A CONDITIONAL EXPRESSION? Like any expression, evaluates to a character, number or date value Uses CASE statement syntax (among others) Evaluates one or more conditions and uses if… then logic to determine its result

CASE STATEMENT SYNTAX Keywords Rules Order of Conditions Searched vs Simple CASE vs DECODE function Nesting Show of hands: Who has worked with CASE logic before?

CASE STATEMENT SYNTAX: KEYWORDS CASE WHEN condition THEN expression [WHEN … THEN …] [ELSE expression ] END For each row, test WHEN conditions in order, and return the corresponding THEN expression for the first condition that is TRUE for the row

CASE STATEMENT SYNTAX: RULES CASE WHEN condition THEN expression [WHEN … THEN …] [ELSE expression ] END Must begin with CASE and end with END At least one WHEN…THEN pair is required; max of 255 arguments ELSE is optional; If ELSE is not present and no WHEN condition is met, statement returns NULL. Each THEN expression within a CASE statement must have the same data type.

CASE STATEMENT SYNTAX: APPLIED CASE WHEN A. EYE_COLOR = ‘BL’ THEN ‘Blue Eyes’ WHEN A. EYE_COLOR = ‘BR’ THEN ‘Brown Eyes’ ELSE ‘Other’ END For each row, system returns result for the first WHEN condition it finds that evaluates to TRUE. If none of the conditions is true, it returns the ELSE result. If no ELSE is included, it returns NULL.

CASE STATEMENT SYNTAX: PITFALLS CASE WHEN A. EYE_COLOR = ‘BL’ THEN ‘Group 1’ WHEN A. EYE_COLOR = ‘BL’ AND HAIR_COLOR = ‘BR’ THEN ‘Group 2’ ELSE ‘Group 3’ END Condition 2 will never be met, because any row that meets it will also meet Condition 1 If Condition 1 is not met, Condition 2 will also never be met No row will ever be returned with result ‘Group 2’ Always order conditions from more specific to more general

CASE STATEMENT SYNTAX: SEARCHED VS. SIMPLE Searched CASE syntax: CASE WHEN A. EYE_COLOR = ‘BL’ THEN ‘Blue Eyes’ WHEN A. EYE_COLOR = ‘BR’ THEN ‘Brown Eyes’ ELSE ‘Other’ END Equivalent Simple CASE syntax: CASE A. EYE_COLOR WHEN ‘BL’ THEN ‘Blue Eyes’ WHEN ‘BR’ THEN ‘Brown Eyes’ ELSE ‘Other’ END Both of these are also equivalent to SQL DECODE function: DECODE(A. EYE_COLOR , ‘BL’, ‘Blue Eyes’ , ‘BR’, ‘Brown Eyes’ , ‘Other’)

CASE STATEMENT SYNTAX: NESTING CASE WHEN A. EYE_COLOR = ‘BL’ THEN CASE WHEN A. HAIR_COLOR = ‘BL’ THEN ‘Group 1’ WHEN A. HAIR_COLOR = ‘BR’ THEN ‘Group 2’ ELSE ‘Group 3’ END ELSE ‘Group 4’ END A CASE statement is itself an expression So you can nest one CASE statement inside another, anywhere an expression would be used. That includes either or both sides of a condition!

CASE STATEMENTS IN SQL SELECT A. EMPLID, A. EFFDT, A. VISA_PERMIT_TYPE, Can appear in: SELECT WHERE GROUP BY HAVING A. EXPIRATN_DT, CASE WHEN NVL(A. EXPIRATN_DT, TO_DATE('31 DEC 2199‘, ‘DDMONYYYY’)) < SYSDATE THEN 'Y' ELSE 'N' END AS VISA_EXPIRED_FLG, FROM PS_VISA_PMT_DATA A WHERE A. COUNTRY = 'USA‘

CASE STATEMENTS IN PS QUERY EXPRESSIONS

So how can I use this?

ALLOW USER TO CONTROL QUERY BEHAVIOR WITH A CHECKBOX PROMPT • CREATE A CHECKBOX PROMPT (VALUE CAN ONLY BE Y OR N) • CREATE A CASE EXPRESSION THAT EVALUATES TO Y OR N (“INCLUDE ROW”) BASED ON THE VALUE OF THE CHECKBOX AND OTHER CRITERIA YOU DEFINE. • CREATE CRITERIA TO SHOW ONLY ROWS WHERE “INCLUDE ROW” EXPRESSION = Y.

OBJECTIVE: ALLOW USER TO CONTROL WHETHER ALL VISA PERMITS ARE SHOWN, OR ONLY EXPIRED VISA PERMITS

CHECKBOX PROMPT SETUP

EXPRESSION SETUP USING CHECKBOX PROMPT VALUE AND “INCLUDE ROW” CRITERIA Prompt value CASE Checkbox not checked: All rows WHEN : 1 = 'N' evaluate to ‘Y’ THEN 'Y' WHEN NVL(A. EXPIRATN_DT, TO_DATE('31 DEC 2199', 'DDMO NYYYY')) < SYSDATE Checkbox is THEN 'Y' checked, and visa permit is expired: ELSE 'N' evaluate to ‘Y’ END Checkbox is checked, and visa permit is not expired: evaluate to ‘N’

CRITERIA SETUP TO SHOW ONLY ROWS WHERE “INCLUDE ROW” EXPRESSION VALUE = ‘Y’

CHECKBOX PROMPT NOW CONTROLS QUERY BEHAVIOR

USE CHECKBOX PROMPT TO CONTROL QUERY CONTENT Create checkbox prompt: “University email only” Create CASE expression that evaluates to email address type code, based on checkbox value CASE WHEN : 1 = ‘Y’ THEN ‘CAMP’ WHEN C. PREFERRED_FLG = ‘Y’ THEN C. E_ADDR_TYPE ELSE ‘CAMP’ END Add criteria against this expression to filter the student’s email rows for the selected type

Other Expressions

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

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 TRUNC(‘Date. Value’) date_occured >= TRUNC(SYSDATE - 30)

LOGIC AND TRANSLATION DECODEtranslates 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)

Questions?

THANKS!!