Transcript HEUG PowerPoint Template

Power Expressions:
Taking Query to
the Next Level
Session #20368 (M6)
March 13, 2006
~ Monday 3:30 - 4:30 ~
HEUG 2006 Alliance Conference
Nashville, Tennessee
Presenter
Tim McGuire
Enterprise Information Systems Dept.
Information Technology
Functional Applications Specialist
Central Washington University
PeopleSoft Systems Support – Since 2002
Objective
To use key SQL function
statements to expand the
reporting functionality and
flexibility of the Query Tool.
Page 3
Benefits
• Increase functional reporting options.
• Reduce reliance on technical report
creation.
• Produce a wider variety of query
information and formats.
• Utilize query development validation tools.
Page 4
Purpose
• Expose the Concept of Using Expressions.
• Illustrate the Potential with Examples.
• Provide Resources.
Page 5
Major Topics
•
•
•
•
•
Intro to Expressions
Sample Function Statements
Conditional Logic
Grouping Logic
The Power Combo
Page 6
CWU Facts
Main Campus
 Located in Ellensburg, WA
 8,359 students
Off-site Centers
 Six off-site centers
- 4 centers in Western, WA
- 2 centers in Eastern, WA
 1,525 students
7
PeopleSoft HRSA at CWU
Version: PeopleSoft 8.0 SP1
PeopleTools: 8.20.06
Database: Oracle 9i
Live Date: September 2004
Self-Service Name: Safari
8
Vocabulary
•CWU = Central = Central Washington University
•PeopleSoft
•Oracle
= the enterprise application software formerly
know as PeopleSoft
= the database developed by the company of the
same name that now owns the enterprise application
software formerly know as PeopleSoft
•Safari
= the name of the HRSA system at CWU using the
enterprise application software formerly know as
PeopleSoft and running on the database developed by the
company of the same name that now owns the enterprise
application software formerly know as PeopleSoft
9
Background
• Experience from FMS and HR
–Staffing, Focus, Reports
• Focus on Reporting
–Inventory, Cleanup, Creation
• Conscious Decision to Maximize Use of
the Query Tool
–Ease, Flexibility, Technical Resources
Page 10
The Query Tool Is:
• Data Retrieval Tool
–Pulls a list of data that match specific
conditions or criteria.
Page 11
The Query Tool Is Not:
•Reporting Tool (Crystal Reports)
–Grouping, Headers, Totals & Sub-totals
•Formatting Tool (Word)
–Font, Layout, Pictures, Pretty
•Spreadsheet (Excel)
–Data Manipulation (Auto Filter, Pivot-Tables)
•Green-bar Paper Report
Page 12
EXPRESSIONS
Introduction to Expressions
• Expressions are manually created
statements used to evaluate, manipulate,
or calculate values to produce
supplementary values or formats.
• Provide Additional Flexibility.
• SQL Syntax is RDBMS Specific.
Page 14
Expressions Window - Web
Page 15
Expressions Window - Client
Page 16
Edit Expressions
Client Version.
Web Version.
Page 17
Text Editors – GridinSoft Notepad
Page 18
Text Editors – Note Tab Light
Page 19
Function Statements
• Function Statements are predefined
system commands expressed with their
operational parameters.
Page 20
Function Statements ~ Example:
• JUMP
How High?
= 24 inches
How Long?
= 120 seconds
Who?
= Bob
JUMP(‘Bob’,24,120)
Page 21
Sample Functions
• Basic Aggregate Functions (AVG, COUNT,
MAX, MIN, SUM)
• ABS
• CONCAT or ||
• GREATEST and LEAST
• INITCAP, LOWER, or UPPER
• LENGTH
Page 22
Sample Functions ~ Continued
• RANK, DENSE_RANK, or PERCENT_RANK
• REPLACE
• ROUND and TRUNC
• SUBSTR
• SYSDATE
• TO_CHAR or TO_NUMBER
• TRIM
Page 23
POWER
EXPRESSIONS
• Conditional Logic
• Grouping Logic
• The Power Combo
Conditional Logic
Conditional Logic (IF….THEN….ELSE)
DECODE
DECODE(G.MON,'Y','M ',' ')
DECODE(A.INSTRUCTION_MODE,'IT','ITV/SATELLITE/TELECAST'
,'P','IN PERSON' , 'WC','WEB CENTRIC','WE','WEB
ENHANCED','WP','WEB PRESENCE','OTHER')
–
–
It is ORACLE specific
Limited to single positive conditions.
Page 26
Conditional Logic (IF….THEN….ELSE)
CASE
CASE WHEN ….. THEN ….. ELSE ….. END
• CASE expressions are an ANSI-standard method for embedding
conditional IF…THEN…ELSE logic into a SQL statement.
• CASE was introduced in Oracle8i and enhanced in Oracle9i.
• CASE is part of the SQL standard, whereas DECODE is not.
Thus, the use of CASE is preferable.
Page 27
Conditional Logic (CASE simple)
CASE WHEN ….. THEN ….. ELSE ….. END
CASE WHEN B.FERPA = 'Y'
THEN 'FERPA - DO NOT DISCLOSE'
ELSE ''
END
CASE WHEN B.FERPA = 'Y' THEN 'FERPA - DO NOT
DISCLOSE' ELSE '' END
Page 28
Conditional Logic (CASE long 1)
CASE WHEN A.CHKLST_ITEM_CD = 'CAPPL' AND A.ITEM_STATUS = 'C'
AND B.CHKLST_ITEM_CD = 'C45CR' AND B.ITEM_STATUS = 'C'
AND C.CHKLST_ITEM_CD = 'CRECT' AND C.ITEM_STATUS = 'C'
AND D.CHKLST_ITEM_CD = 'CRECO' AND D.ITEM_STATUS = 'C'
AND E.CHKLST_ITEM_CD = 'CPRE' AND E.ITEM_STATUS = 'C'
AND F.CHKLST_ITEM_CD = 'CGPA' AND F.ITEM_STATUS = 'C'
AND G.CHKLST_ITEM_CD = 'CFIPR' AND G.ITEM_STATUS = 'C'
AND H.CHKLST_ITEM_CD = 'CCAFS' AND H.ITEM_STATUS = 'C'
AND I.CHKLST_ITEM_CD = 'CTRAN' AND I.ITEM_STATUS = 'C'
AND J.CHKLST_ITEM_CD = 'CWBM' AND J.ITEM_STATUS = 'C'
AND K.CHKLST_ITEM_CD = 'CWBR' AND K.ITEM_STATUS = 'C'
AND L.CHKLST_ITEM_CD = 'CWBW' AND L.ITEM_STATUS = 'C'
THEN 'ADMIT_FULL'
Page 29
Conditional Logic (CASE long 2)
WHEN A.CHKLST_ITEM_CD = 'CAPPL' AND A.ITEM_STATUS = 'C'
AND B.CHKLST_ITEM_CD = 'C45CR' AND B.ITEM_STATUS = 'C'
AND C.CHKLST_ITEM_CD = 'CRECT' AND C.ITEM_STATUS = 'C'
AND D.CHKLST_ITEM_CD = 'CRECO' AND D.ITEM_STATUS = 'C'
AND E.CHKLST_ITEM_CD = 'CPRE' AND E.ITEM_STATUS = 'C'
AND F.CHKLST_ITEM_CD = 'CGPA' AND F.ITEM_STATUS = 'N'
AND G.CHKLST_ITEM_CD = 'CFIPR' AND G.ITEM_STATUS = 'C'
AND H.CHKLST_ITEM_CD = 'CCAFS' AND H.ITEM_STATUS = 'C'
AND I.CHKLST_ITEM_CD = 'CTRAN' AND I.ITEM_STATUS = 'C'
AND J.CHKLST_ITEM_CD = 'CWBM' AND J.ITEM_STATUS = 'C'
AND K.CHKLST_ITEM_CD = 'CWBR' AND K.ITEM_STATUS = 'C'
AND L.CHKLST_ITEM_CD = 'CWBW' AND L.ITEM_STATUS = 'C'
THEN 'ADMIT_PROB'
Page 30
Conditional Logic (CASE long 3)
WHEN A.CHKLST_ITEM_CD = 'CAPPL' AND A.ITEM_STATUS = 'C'
AND B.CHKLST_ITEM_CD = 'C45CR' AND B.ITEM_STATUS = 'C'
AND C.CHKLST_ITEM_CD = 'CRECT' AND C.ITEM_STATUS = 'C'
AND D.CHKLST_ITEM_CD = 'CRECO' AND D.ITEM_STATUS = 'C'
AND F.CHKLST_ITEM_CD = 'CGPA' AND F.ITEM_STATUS = 'C'
AND G.CHKLST_ITEM_CD = 'CFIPR' AND G.ITEM_STATUS = 'C'
AND H.CHKLST_ITEM_CD = 'CCAFS' AND H.ITEM_STATUS = 'C'
AND I.CHKLST_ITEM_CD = 'CTRAN' AND I.ITEM_STATUS = 'C'
AND (E.CHKLST_ITEM_CD = 'CPRE' AND E.ITEM_STATUS = 'I'
OR J.CHKLST_ITEM_CD = 'CWBM' AND J.ITEM_STATUS IN ('I','R')
OR K.CHKLST_ITEM_CD = 'CWBR' AND K.ITEM_STATUS IN ('I','R')
OR L.CHKLST_ITEM_CD = 'CWBW' AND L.ITEM_STATUS IN ('I','R'))
THEN 'PROV_ADMIT'
Page 31
Conditional Logic (CASE long 4)
WHEN A.CHKLST_ITEM_CD = 'CAPPL' AND A.ITEM_STATUS = 'C'
AND B.CHKLST_ITEM_CD = 'C45CR' AND B.ITEM_STATUS = 'C'
AND C.CHKLST_ITEM_CD = 'CRECT' AND C.ITEM_STATUS = 'C'
AND D.CHKLST_ITEM_CD = 'CRECO' AND D.ITEM_STATUS = 'C'
AND F.CHKLST_ITEM_CD = 'CGPA' AND F.ITEM_STATUS = 'N'
AND G.CHKLST_ITEM_CD = 'CFIPR' AND G.ITEM_STATUS = 'C'
AND H.CHKLST_ITEM_CD = 'CCAFS' AND H.ITEM_STATUS = 'C'
AND I.CHKLST_ITEM_CD = 'CTRAN' AND I.ITEM_STATUS = 'C'
AND (E.CHKLST_ITEM_CD = 'CPRE' AND E.ITEM_STATUS = 'I'
OR J.CHKLST_ITEM_CD = 'CWBM' AND J.ITEM_STATUS IN ('I','R')
OR K.CHKLST_ITEM_CD = 'CWBR' AND K.ITEM_STATUS IN ('I','R')
OR L.CHKLST_ITEM_CD = 'CWBW' AND L.ITEM_STATUS IN ('I','R'))
THEN 'PROV_PROB'
ELSE 'NEW'
END
Page 32
Conditional Logic (CASE mixed)
CASE, LENGTH, SUBSTR, ||, TRIM
Zip Code Plus 4
CASE WHEN (B.COUNTRY = 'USA' AND
LENGTH(TRIM(B.POSTAL)) = 9)
THEN SUBSTR(B.POSTAL,1,5) || '-' ||
SUBSTR(B.POSTAL,6,4)
ELSE TRIM(B.POSTAL)
END
Before
After
989267405
98020
98948-3722
98926-7405
98020
98948-3722
Page 33
Conditional Logic (CASE nested 1)
CASE WHEN ….. THEN ….. ELSE ….. END
CASE WHEN (SUM(C.UNT_TRNSFR * C.GRD_PTS_PER_UNIT) /
SUM(C.UNT_TRNSFR)) IS NULL THEN A.CUM_GPA ELSE (CASE
WHEN SUM(C.UNT_TRNSFR) IS NOT NULL OR A.TOT_TAKEN_GPA
IS NOT NULL THEN (CASE WHEN SUM(C.GRD_PTS_PER_UNIT *
C.UNT_TRNSFR) IS NULL THEN A.TOT_GRADE_POINTS ELSE
SUM(C.GRD_PTS_PER_UNIT * C.UNT_TRNSFR) +
A.TOT_GRADE_POINTS END / CASE WHEN SUM(C.UNT_TRNSFR)
IS NULL THEN A.TOT_TAKEN_GPA ELSE SUM(C.UNT_TRNSFR) +
A.TOT_TAKEN_GPA END) ELSE 0 END) END
Page 34
Conditional Logic (CASE nested 2)
CASE WHEN (SUM(C.UNT_TRNSFR * C.GRD_PTS_PER_UNIT) /
SUM(C.UNT_TRNSFR)) IS NULL
THEN A.CUM_GPA
ELSE (CASE WHEN SUM(C.UNT_TRNSFR) IS NOT NULL OR
A.TOT_TAKEN_GPA IS NOT NULL
THEN (CASE WHEN SUM(C.GRD_PTS_PER_UNIT *
C.UNT_TRNSFR) IS NULL
THEN A.TOT_GRADE_POINTS
ELSE SUM(C.GRD_PTS_PER_UNIT *
C.UNT_TRNSFR) + A.TOT_GRADE_POINTS
END / CASE WHEN SUM(C.UNT_TRNSFR) IS NULL
THEN A.TOT_TAKEN_GPA
ELSE SUM(C.UNT_TRNSFR) +
A.TOT_TAKEN_GPA
END)
ELSE 0
END)
END
Page 35
Conditional Logic (CASE nested)
Note: The maximum number of arguments in a CASE
expression is 255, and each WHEN ... THEN pair counts
as two arguments. To avoid exceeding the limit of 128
choices, you can nest CASE expressions.
CASE WHEN ….. THEN ….. ELSE ….. END
Page 36
Grouping Logic
Grouping Logic - INTRODUCTION
• Grouping
• Sub-Totals
• Totals of Groups
• Window Statement
• Partition Statement
• Group Aggregate
Page 38
Grouping Logic - DEFINITION
ANALYTIC FUNCTIONS
• Analytic functions compute an aggregate value based
on a group of rows.
 Analytic functions are the last set of operations performed in a
query except for the final ORDER BY clause. All joins and all
WHERE, GROUP BY, and HAVING clauses are completed before
the analytic functions are processed.
 Analytic functions are commonly used to compute cumulative,
moving, centered, and reporting aggregates.
Page 39
Grouping Logic - ANALYTIC FUNCTIONS
Partition Statement Syntax
….. (…..) OVER (PARTITION BY ….. )
COUNT (…..) OVER (PARTITION BY ….. )
COUNT (A.EMPLID) OVER (PARTITION BY A.STRM)
Page 40
Grouping Logic – Create New Expression
REMEMBER
• Adjust Expression Type
as needed.
• Remember to enter an
appropriate Length.
• Do Not Select the
Aggregate Function.
Page 41
• Use ‘Add’ Buttons as
usual.
Grouping Logic – Use as Output Field
COUNT (A.EMPLID) OVER (PARTITION BY A.STRM)
Edit Heading Text
Page 42
COUNT (A.EMPLID) OVER (PARTITION BY A.STRM)
Grouping Logic – Run & View Results
• Function operation
and grouping happens
after all query criteria
have been met.
• Calculation is
independent of output.
• The value repeats
for each row with that
group/partition.
Page 43
Grouping Logic – Different Groups
COUNT (A.EMPLID) OVER (PARTITION BY A.STRM)
COUNT (A.EMPLID) OVER (PARTITION BY A.ACAD_LEVEL_BOT)
Page 44
Grouping Logic – Distinct
COUNT (A.EMPLID) OVER (PARTITION BY A.STRM)
COUNT (A.EMPLID) OVER (PARTITION BY A.ACAD_LEVEL_BOT)
COUNT (DISTINCT A.EMPLID) OVER (PARTITION BY A.ACAD_LEVEL_BOT)
Page 45
Grouping Logic – Multiple Groups
COUNT (A.EMPLID) OVER (PARTITION BY A.ACAD_LEVEL_BOT)
COUNT (DISTINCT A.EMPLID) OVER (PARTITION BY A.ACAD_LEVEL_BOT)
COUNT (DISTINCT A.EMPLID) OVER
(PARTITION BY A.ACAD_LEVEL_BOT, A.STRM)
Page 46
Grouping Logic – SUM a Group
Total Credits by ID
SUM (A.UNT_TAKEN_PRGRSS) OVER (PARTITION BY A.EMPLID)
Page 47
Grouping Logic – Query Tools 1
Group by a Constant
COUNT (A.EMPLID) OVER (PARTITION BY 'C')
COUNT (DISTINCT A.EMPLID) OVER (PARTITION BY 'C')
Page 48
Grouping Logic – Query Tools 2
Count Multiple ID’s
COUNT (A.EMPLID) OVER (PARTITION BY A.EMPLID)
Page 49
Grouping Logic – ORDER BY
PERCENT_RANK
…..
(…..) OVER (PARTITION
() OVER (PARTITION
BY A.ACAD_LEVEL_BOT
BY
ORDER BY A.CUM_GPA
A.ACAD_LEVEL_BOT
ORDER
DESC)
BY A.CUM_GPA DESC)
(PERCENT_RANK () OVER (PARTITION BY
A.ACAD_LEVEL_BOT ORDER BY A.CUM_GPA DESC)) * 100
Page 50
Grouping Logic - ANALYTIC FUNCTIONS
Partition Statement Syntax
….. (…..) OVER (PARTITION BY ….. )
….. (…..) OVER (PARTITION BY ….. ORDER BY ….. DESC)
Page 51
The Power Combo
Power Combo – Introduction
CASE WHEN ….. THEN ….. ELSE ….. END
….. (…..) OVER (PARTITION BY ….. )
CASE WHEN (….. (…..) OVER (PARTITION BY ….. ))
> 0 THEN ….. ELSE ….. END
….. (CASE WHEN ….. THEN ….. ELSE ….. END)
OVER (PARTITION BY ….. )
Page 53
Power Combo – Example 1
Total Credits per Person
SUM (B.UNT_PRGRSS) OVER (PARTITION BY A.EMPLID)
Total Credits per Person as of Date
SUM (CASE WHEN B.ENRL_ADD_DT <= :2 THEN B.UNT_PRGRSS
END) OVER (PARTITION BY A.EMPLID)
Page 54
Power Combo – Example 1 - continued
Enrollment Status as of Date
CASE WHEN (SUM (CASE WHEN B.ENRL_ADD_DT <= :2 THEN B.UNT_PRGRSS
END) OVER (PARTITION BY A.EMPLID)) >= 12 THEN 'Full'
WHEN (SUM (CASE WHEN B.ENRL_ADD_DT <= :2 THEN B.UNT_PRGRSS
END) OVER (PARTITION BY A.EMPLID)) BETWEEN 9 AND 11
THEN ‘3Quarter'
WHEN (SUM (CASE WHEN B.ENRL_ADD_DT <= :2 THEN B.UNT_PRGRSS
END) OVER (PARTITION BY A.EMPLID)) BETWEEN 6 AND 8
THEN 'Half'
ELSE 'Less'
END
Page 55
Power Combo – Example 2
Teacher Certification:
Totals for Ethnicity & Gender
Page 56
Power Combo – Example 2 continued
Totals for Ethnicity & Gender
A.STDNT_GROUP || ': ' || COUNT
(DISTINCT A.EMPLID) OVER (PARTITION
BY A.STDNT_GROUP)
Page 57
Power Combo – Example 2 cont.
Totals for Ethnicity & Gender
COUNT (DISTINCT (CASE WHEN B.SEX = 'M'
THEN A.EMPLID END)) OVER (PARTITION BY
A.STDNT_GROUP)
Page 58
Power Combo – Example 2 cont.
Totals for Ethnicity & Gender
COUNT (DISTINCT (CASE WHEN
D.ETHNIC_GROUP = 4 THEN A.EMPLID END))
OVER (PARTITION BY A.STDNT_GROUP)
Page 59
Totals for Ethnicity & Gender
Power Combo – Example 2 cont.
• Remove all output
fields with unique
data in them EMPLID, STRM, etc.
Page 60
Totals for Ethnicity & Gender
Power Combo – Example 2 cont.
• Select the Query
Properties Link
• Select the Distinct
Check Box to
suppress duplication
for entire query.
(The Distinct Query
option happens last so
it does not affect any
query selection criteria
or computation.)
Page 61
Power Combo – Example 2
To Produce Grand Total Reports like:
Totals for Ethnicity & Gender
Totals of Students & Guests at Orientation Meetings
Page 62
Power Combo – Conclusion
CASE WHEN ….. THEN ….. ELSE ….. END
….. (…..) OVER (PARTITION BY ….. )
Combining these two Power Expression greatly
increases the Query Tool’s flexibility and power.
Page 63
What We Have Learned
•Reporting is a key component of a successful system.
•Some people will always like Green-bar better.
•Query can be a very flexible and powerful tool.
•The Query Tool is not always the best solution.
•Help End Users to understand the benefits of Excel.
•Relying on Query too much for both functional processes and
reporting needs can be problematic in the long run.
•It is important to have a clear understanding of the actual need.
•Queries that try to do too much can have performance issues.
•Creativity and perseverance can solve a lot of problems.
Page 64
Resources
• Harvard – Key Functions in Oracle SQL
http://vpf-web.harvard.edu/applications/ad_hoc/key_functions_in_oracle_sql.pdf
• Oracle SQL Reference
9i web:
9i pdf:
http://www.cs.utah.edu/classes/cs6530/oracle/doc/B10501_01/server.920/a96540.pdf
8i web:
8i pdf:
http://www.cs.ncl.ac.uk/teaching/facilities/swdoc/oracle9i/server.920/a96540/toc.htm
http://h50.isi.u-psud.fr/docmiage/oracle/doc/server.817/a85397/toc.htm
http://csis.pace.edu/support/sql_reference.pdf
• NoteTab Light
http://www.notetab.com/ntl.php
• GridinSoft Notepad Lite
http://www.gridinsoft.com/downloads.php
Page 65
Summary
• You have now been exposed to the concept of using
expressions in your queries.
• With the examples given you can see there is great
potential to what you can accomplish with just a few
powerful expressions and your creativity.
• Some useful resources have been provided for you
to increase your understanding of expressions and
function statements.
Page 66
Conclusion
SQL Function Statements can really
expand the reporting functionality
and flexibility of the Query Tool.
Using Power Expressions will help
you take Query to the Next Level.
Page 67
QUESTIONS?
Contact
Tim McGuire
Information Technology Applications Specialist
Central Washington University
509-963-2921
E-mail: [email protected]
Page 69
This presentation and all HEUG
2006 presentations are available
for download from HEUG Online
http://heug.org
Presentations from previous HEUG meetings are also available