1 / 70

Power Expressions: Taking Query to the Next Level

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

franklin
Télécharger la présentation

Power Expressions: Taking Query to the Next Level

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  2. Presenter Tim McGuire Enterprise Information Systems Dept. Information Technology Functional Applications Specialist Central Washington University PeopleSoft Systems Support– Since 2002

  3. Objective To use key SQL function statements to expand the reporting functionality and flexibility of the Query Tool.

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

  5. Purpose • Expose the Concept of Using Expressions. • Illustrate the Potential with Examples. • Provide Resources.

  6. Major Topics • Intro to Expressions • Sample Function Statements • Conditional Logic • Grouping Logic • The Power Combo

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

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

  9. Vocabulary • CWU = Central = Central Washington University = the enterprise application software formerly know as PeopleSoft • PeopleSoft = the database developed by the company of the same name that now owns the enterprise application software formerly know as PeopleSoft • Oracle = 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 • Safari 9

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

  11. The Query Tool Is: • Data Retrieval Tool • Pulls a list of data that match specific conditions or criteria.

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

  13. EXPRESSIONS

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

  15. Expressions Window - Web

  16. Expressions Window - Client

  17. Edit Expressions Client Version. Web Version.

  18. Text Editors – GridinSoft Notepad

  19. Text Editors – Note Tab Light

  20. Function Statements • Function Statements are predefined system commands expressed with their operational parameters.

  21. Function Statements ~ Example: • JUMP = 24 inches How High? How Long? = 120 seconds Who? = Bob JUMP(‘Bob’,24,120)

  22. Sample Functions • Basic Aggregate Functions (AVG, COUNT, MAX, MIN, SUM) • ABS • CONCAT or || • GREATEST and LEAST • INITCAP, LOWER, or UPPER • LENGTH

  23. Sample Functions ~ Continued • RANK, DENSE_RANK, or PERCENT_RANK • REPLACE • ROUND and TRUNC • SUBSTR • SYSDATE • TO_CHAR or TO_NUMBER • TRIM

  24. POWER EXPRESSIONS • Conditional Logic • Grouping Logic • The Power Combo

  25. Conditional Logic

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

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

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

  29. 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'

  30. 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'

  31. 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'

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

  33. Before After 989267405 98020 98948-3722 98926-7405 98020 98948-3722 Conditional Logic (CASE mixed) CASE, LENGTH,SUBSTR,||,TRIM Zip Code Plus 4 CASE WHEN (B.COUNTRY = 'USA'  AND LENGTH(TRIM(B.POSTAL)) = 9)THENSUBSTR(B.POSTAL,1,5)|| '-' ||SUBSTR(B.POSTAL,6,4)ELSETRIM(B.POSTAL)END

  34. 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 Conditional Logic (CASE nested 1) CASE WHEN ….. THEN ….. ELSE ….. END

  35. 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 Conditional Logic (CASE nested 2)

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

  37. Grouping Logic

  38. Grouping Logic-INTRODUCTION • Grouping • Sub-Totals • Totals of Groups • Window Statement • Partition Statement • Group Aggregate

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

  40. Grouping Logic - ANALYTIC FUNCTIONS Partition Statement Syntax ….. (…..) OVER (PARTITION BY ….. ) COUNT (…..) OVER (PARTITION BY ….. ) COUNT (A.EMPLID) OVER (PARTITION BY A.STRM)

  41. Grouping Logic – Create New Expression • REMEMBER • Adjust Expression Type as needed. • Remember to enter an appropriate Length. • Do Not Select the Aggregate Function. • Use ‘Add’ Buttons as usual.

  42. Grouping Logic – Use as Output Field COUNT (A.EMPLID) OVER (PARTITION BY A.STRM) Edit Heading Text

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

  44. Grouping Logic–Different Groups COUNT (A.EMPLID) OVER (PARTITION BY A.STRM) COUNT (A.EMPLID) OVER (PARTITION BY A.ACAD_LEVEL_BOT)

  45. 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)

  46. 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)

  47. Grouping Logic–SUM a Group Total Credits by ID SUM (A.UNT_TAKEN_PRGRSS) OVER (PARTITION BY A.EMPLID)

  48. Grouping Logic–Query Tools 1 Group by a Constant COUNT (A.EMPLID) OVER (PARTITION BY 'C') COUNT (DISTINCT A.EMPLID) OVER (PARTITION BY 'C')

  49. Grouping Logic–Query Tools 2 Count Multiple ID’s COUNT (A.EMPLID) OVER (PARTITION BY A.EMPLID)

  50. Grouping Logic–ORDER BY ….. (…..) OVER (PARTITION BY A.ACAD_LEVEL_BOT ORDER BYA.CUM_GPADESC) PERCENT_RANK () OVER (PARTITION BY A.ACAD_LEVEL_BOT ORDER BYA.CUM_GPADESC) (PERCENT_RANK () OVER (PARTITION BY A.ACAD_LEVEL_BOT ORDER BY A.CUM_GPA DESC)) * 100

More Related