power expressions: taking query to the next level session #20368 (m6) march 13, 2006 ~ monday 3:30 -...

Power Expressions:Power Expressions: Taking Query to Taking Query to the Next Levelthe Next Level Session #20368 (M6)

March 13, 2006~ Monday 3:30 - 4:30 ~

HEUG 2006 Alliance Conference

Nashville, Tennessee

Tim McGuireTim McGuire

Enterprise Information Systems Enterprise Information Systems Dept.Dept.

Information TechnologyInformation Technology

Functional Applications SpecialistFunctional Applications Specialist

Central Washington UniversityCentral Washington University

PeopleSoft Systems SupportPeopleSoft Systems Support – Since 2002– Since 2002

PresenterPresenter

ObjectiveObjective

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

BenefitsBenefits

• Increase functional reporting Increase functional reporting options.options.• Reduce reliance on technical report Reduce reliance on technical report

creation.creation.• Produce a wider variety of query Produce a wider variety of query information and formats.information and formats.• Utilize query development validation Utilize query development validation tools.tools.

PurposePurpose

• Expose the Concept of Using Expressions.Expose the Concept of Using Expressions.

• Illustrate the Potential with Examples.Illustrate the Potential with Examples.

• Provide Resources.Provide Resources.

Major TopicsMajor Topics

• Intro to ExpressionsIntro to Expressions• Sample Function Statements Sample Function Statements • Conditional Logic Conditional Logic • Grouping Logic Grouping Logic • The Power ComboThe Power Combo

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

CWU FactsCWU Facts

8

PeopleSoft HRSA at PeopleSoft HRSA at CWUCWUVersion: PeopleSoft 8.0 SP1Version: PeopleSoft 8.0 SP1

PeopleTools: 8.20.06PeopleTools: 8.20.06

Database: Oracle 9Database: Oracle 9ii

Live Date: September 2004Live Date: September 2004

Self-Service Name: SafariSelf-Service Name: Safari

9

VocabularyVocabulary•CWUCWU= Central = Central Washington University= Central = Central Washington University

•PeopleSoftPeopleSoft = the enterprise = the enterprise applicationapplication software software

formerly know as PeopleSoftformerly know as PeopleSoft

•OracleOracle = the = the databasedatabase developed by the company of developed by the company of

the same name that now owns the enterprise the same name that now owns the enterprise

application software formerly know as PeopleSoftapplication software formerly know as PeopleSoft

•SafariSafari = the name of the = the name of the HRSA system at CWUHRSA system at CWU using using

the enterprise application software formerly know the enterprise application software formerly know

as PeopleSoft and running on the database as PeopleSoft and running on the database

developed by the company of the same name that developed by the company of the same name that

now owns the enterprise application software now owns the enterprise application software

formerly know as PeopleSoftformerly know as PeopleSoft

BackgroundBackground

• Experience from FMS and HRExperience from FMS and HR–Staffing, Focus, Reports

• Focus on Reporting Focus on Reporting –Inventory, Cleanup, Creation

• Conscious Decision to Maximize Conscious Decision to Maximize Use of Use of the Query Tool the Query Tool

–Ease, Flexibility, Technical Resources

The Query Tool Is:The Query Tool Is:

• Data Retrieval ToolData Retrieval Tool–Pulls a list of data that match specific conditions or criteria.

The Query Tool Is Not:The Query Tool Is Not:

•Reporting Tool (Crystal Reports)Reporting Tool (Crystal Reports)–Grouping, Headers, Totals & Sub-totals

•Formatting Tool (Word)Formatting Tool (Word)–Font, Layout, Pictures, Pretty

•Spreadsheet (Excel)Spreadsheet (Excel)–Data Manipulation (Auto Filter, Pivot-Tables)

•Green-bar Paper ReportGreen-bar Paper Report

EXPRESSIONSEXPRESSIONS

Introduction to Expressions Introduction to Expressions

• Expressions are Expressions are manuallymanually created created statements used to evaluate, statements used to evaluate, manipulate, or calculate values to manipulate, or calculate values to produce supplementary values or produce supplementary values or formats.formats.

• Provide Additional Flexibility.Provide Additional Flexibility.

• SQL Syntax is RDBMS Specific.SQL Syntax is RDBMS Specific.

Expressions Window - WebExpressions Window - Web

Expressions Window - ClientExpressions Window - Client

Edit ExpressionsEdit Expressions

Web Version.Web Version.Client Version.Client Version.

Text Editors – Text Editors – GridinSoft GridinSoft NotepadNotepad

Text Editors – Note Tab LightText Editors – Note Tab Light

Function Statements Function Statements

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

Function Statements ~ Function Statements ~ Example: Example:

• JUMPJUMP

JUMP(‘Bob’,24,120)JUMP(‘Bob’,24,120)

How Long? How Long?

How High? How High?

Who?Who?

= 24 inches= 24 inches

= 120 = 120 secondsseconds= Bob= Bob

Sample FunctionsSample Functions

• Basic Aggregate Functions (AVG, Basic Aggregate Functions (AVG, COUNT, COUNT, MAX, MIN, SUM)MAX, MIN, SUM)

• ABSABS

• CONCAT or ||CONCAT or ||

• GREATEST and LEASTGREATEST and LEAST

• INITCAP, LOWER, or UPPERINITCAP, LOWER, or UPPER

• LENGTHLENGTH

Sample Functions ~ Sample Functions ~ ContinuedContinued• RANK, DENSE_RANK, or RANK, DENSE_RANK, or PERCENT_RANKPERCENT_RANK

• REPLACEREPLACE

• ROUND and TRUNCROUND and TRUNC

• SUBSTRSUBSTR

• SYSDATESYSDATE

• TO_CHAR or TO_NUMBERTO_CHAR or TO_NUMBER

• TRIM TRIM

POWER POWER EXPRESSIONSEXPRESSIONS

• Conditional Logic Conditional Logic

• Grouping Logic Grouping Logic

• The Power ComboThe Power Combo

Conditional LogicConditional Logic

Conditional Logic Conditional Logic (IF….THEN….ELSE)(IF….THEN….ELSE)

DECODEDECODE

DECODE(G.MON,'Y','M ',' ')DECODE(G.MON,'Y','M ',' ')

DECODE(A.INSTRUCTION_MODE,'IT','ITV/SATELLITE/DECODE(A.INSTRUCTION_MODE,'IT','ITV/SATELLITE/TELECAST' ,'P','IN PERSON' , 'WC','WEB TELECAST' ,'P','IN PERSON' , 'WC','WEB CENTRIC','WE','WEB ENHANCED','WP','WEB CENTRIC','WE','WEB ENHANCED','WP','WEB PRESENCE','OTHER')PRESENCE','OTHER')

– It is ORACLE specificIt is ORACLE specific– Limited to single positive conditions. Limited to single positive conditions.

Conditional Logic Conditional Logic (IF….THEN….ELSE)(IF….THEN….ELSE)

CASECASE

CASE WHEN ….. THEN ….. ELSE ….. ENDCASE WHEN ….. THEN ….. ELSE ….. END

• CASE expressions are an ANSI-standard method for CASE expressions are an ANSI-standard method for embedding conditional IF…THEN…ELSE logic into a SQL embedding conditional IF…THEN…ELSE logic into a SQL statement.statement.

• CASE was introduced in Oracle8i and enhanced in CASE was introduced in Oracle8i and enhanced in Oracle9i.Oracle9i.

• CASE is part of the SQL standard, whereas DECODE is CASE is part of the SQL standard, whereas DECODE is not. Thus, the use of CASE is preferable.not. Thus, the use of CASE is preferable.

Conditional Logic Conditional Logic (CASE (CASE simple)simple)

CASE WHEN B.FERPA = 'Y' THEN 'FERPA - DO NOT CASE WHEN B.FERPA = 'Y' THEN 'FERPA - DO NOT DISCLOSE' ELSE '' ENDDISCLOSE' ELSE '' END


CASE WHENCASE WHENB.FERPA = 'Y'B.FERPA = 'Y'

THETHENN

'FERPA - DO NOT DISCLOSE''FERPA - DO NOT DISCLOSE'

ELSEELSE ''''

ENDEND

Conditional Logic Conditional Logic (CASE long 1)(CASE long 1)

CASE WHEN A.CHKLST_ITEM_CD = 'CAPPL' AND A.ITEM_STATUS = CASE WHEN A.CHKLST_ITEM_CD = 'CAPPL' AND A.ITEM_STATUS = 'C''C' AND B.CHKLST_ITEM_CD = 'C45CR' AND B.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 C.CHKLST_ITEM_CD = 'CRECT' AND C.ITEM_STATUS = 'C' AND D.CHKLST_ITEM_CD = 'CRECO' AND D.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 E.CHKLST_ITEM_CD = 'CPRE' AND E.ITEM_STATUS = 'C' AND F.CHKLST_ITEM_CD = 'CGPA' AND F.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 G.CHKLST_ITEM_CD = 'CFIPR' AND G.ITEM_STATUS = 'C' AND H.CHKLST_ITEM_CD = 'CCAFS' AND H.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 I.CHKLST_ITEM_CD = 'CTRAN' AND I.ITEM_STATUS = 'C' AND J.CHKLST_ITEM_CD = 'CWBM' AND J.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 K.CHKLST_ITEM_CD = 'CWBR' AND K.ITEM_STATUS = 'C' AND L.CHKLST_ITEM_CD = 'CWBW' AND L.ITEM_STATUS = 'C'AND L.CHKLST_ITEM_CD = 'CWBW' AND L.ITEM_STATUS = 'C' THEN 'ADMIT_FULL'THEN 'ADMIT_FULL'


WHEN A.CHKLST_ITEM_CD = 'CAPPL' AND A.ITEM_STATUS = 'C'WHEN A.CHKLST_ITEM_CD = 'CAPPL' AND A.ITEM_STATUS = 'C' AND B.CHKLST_ITEM_CD = 'C45CR' AND B.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 C.CHKLST_ITEM_CD = 'CRECT' AND C.ITEM_STATUS = 'C' AND D.CHKLST_ITEM_CD = 'CRECO' AND D.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 E.CHKLST_ITEM_CD = 'CPRE' AND E.ITEM_STATUS = 'C' AND F.CHKLST_ITEM_CD = 'CGPA' AND F.ITEM_STATUS = 'N'AND F.CHKLST_ITEM_CD = 'CGPA' AND F.ITEM_STATUS = 'N' AND G.CHKLST_ITEM_CD = 'CFIPR' AND G.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 H.CHKLST_ITEM_CD = 'CCAFS' AND H.ITEM_STATUS = 'C' AND I.CHKLST_ITEM_CD = 'CTRAN' AND I.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 J.CHKLST_ITEM_CD = 'CWBM' AND J.ITEM_STATUS = 'C' AND K.CHKLST_ITEM_CD = 'CWBR' AND K.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'AND L.CHKLST_ITEM_CD = 'CWBW' AND L.ITEM_STATUS = 'C' THEN 'ADMIT_PROB'THEN 'ADMIT_PROB'


WHEN A.CHKLST_ITEM_CD = 'CAPPL' AND A.ITEM_STATUS = 'C'WHEN A.CHKLST_ITEM_CD = 'CAPPL' AND A.ITEM_STATUS = 'C' AND B.CHKLST_ITEM_CD = 'C45CR' AND B.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 C.CHKLST_ITEM_CD = 'CRECT' AND C.ITEM_STATUS = 'C' AND D.CHKLST_ITEM_CD = 'CRECO' AND D.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 F.CHKLST_ITEM_CD = 'CGPA' AND F.ITEM_STATUS = 'C' AND G.CHKLST_ITEM_CD = 'CFIPR' AND G.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 H.CHKLST_ITEM_CD = 'CCAFS' AND H.ITEM_STATUS = 'C' AND I.CHKLST_ITEM_CD = 'CTRAN' AND I.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'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 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 K.CHKLST_ITEM_CD = 'CWBR' AND K.ITEM_STATUS IN ('I','R') OR L.CHKLST_ITEM_CD = 'CWBW' AND L.ITEM_STATUS IN OR L.CHKLST_ITEM_CD = 'CWBW' AND L.ITEM_STATUS IN ('I','R'))('I','R')) THEN 'PROV_ADMIT'THEN 'PROV_ADMIT'


WHEN A.CHKLST_ITEM_CD = 'CAPPL' AND A.ITEM_STATUS = 'C'WHEN A.CHKLST_ITEM_CD = 'CAPPL' AND A.ITEM_STATUS = 'C' AND B.CHKLST_ITEM_CD = 'C45CR' AND B.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 C.CHKLST_ITEM_CD = 'CRECT' AND C.ITEM_STATUS = 'C' AND D.CHKLST_ITEM_CD = 'CRECO' AND D.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 F.CHKLST_ITEM_CD = 'CGPA' AND F.ITEM_STATUS = 'N' AND G.CHKLST_ITEM_CD = 'CFIPR' AND G.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 H.CHKLST_ITEM_CD = 'CCAFS' AND H.ITEM_STATUS = 'C' AND I.CHKLST_ITEM_CD = 'CTRAN' AND I.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'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 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 K.CHKLST_ITEM_CD = 'CWBR' AND K.ITEM_STATUS IN ('I','R') OR L.CHKLST_ITEM_CD = 'CWBW' AND L.ITEM_STATUS IN OR L.CHKLST_ITEM_CD = 'CWBW' AND L.ITEM_STATUS IN ('I','R'))('I','R')) THEN 'PROV_PROB'THEN 'PROV_PROB'ELSE 'NEW'ELSE 'NEW'ENDEND

Conditional Logic Conditional Logic (CASE mixed)(CASE mixed)

CASE WHENCASE WHEN (B.COUNTRY = 'USA' AND (B.COUNTRY = 'USA' AND LENGTH(LENGTH(TRIM(TRIM(B.POSTALB.POSTAL)))) = 9) = 9) THENTHEN SUBSTR(SUBSTR(B.POSTALB.POSTAL,1,5),1,5) |||| '-' '-' |||| SUBSTR(SUBSTR(B.POSTALB.POSTAL,6,4),6,4) ELSEELSE TRIM(TRIM(B.POSTALB.POSTAL)) ENDEND

CASE, CASE, LENGTH,LENGTH, SUBSTR,SUBSTR, ||,||, TRIMTRIM

Before After9892674059802098948-3722

98926-74059802098948-3722

Zip Code Plus 4Zip Code Plus 4

Conditional Logic Conditional Logic (CASE nested (CASE nested 1)1)

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

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


Grouping LogicGrouping Logic

Grouping LogicGrouping Logic -- INTRODUCTIONINTRODUCTION

• GroupingGrouping

• Sub-TotalsSub-Totals

• Totals of GroupsTotals of Groups

• Window StatementWindow Statement

• Partition StatementPartition Statement

• Group AggregateGroup Aggregate

Grouping Logic - Grouping Logic - DEFINITIONDEFINITION

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

ANALYTIC FUNCTIONSANALYTIC FUNCTIONS

Grouping Logic - Grouping Logic - ANALYTIC ANALYTIC

FUNCTIONSFUNCTIONS

…….. (…..) OVER (PARTITION BY ….. ).. (…..) OVER (PARTITION BY ….. )

COUNT (…..) OVER (PARTITION BY ….. )COUNT (…..) OVER (PARTITION BY ….. )

COUNT (COUNT (A.EMPLIDA.EMPLID) OVER (PARTITION BY ) OVER (PARTITION BY A.STRMA.STRM))

Partition Statement SyntaxPartition Statement Syntax

Grouping Logic – Grouping Logic – Create New Create New ExpressionExpression

REMEMBERREMEMBER

• Adjust Expression Adjust Expression Type as needed.Type as needed.

• Remember to enter Remember to enter an appropriate an appropriate Length.Length.

• Do Not Select the Do Not Select the Aggregate Function.Aggregate Function.

• Use ‘Add’ Buttons as Use ‘Add’ Buttons as usual.usual.

Grouping Logic –Grouping Logic – Use as Output Use as Output FieldField

COUNT (A.EMPLID) OVER (PARTITION BY A.STRM)COUNT (A.EMPLID) OVER (PARTITION BY A.STRM)

Edit Heading TextEdit Heading Text

Grouping LogicGrouping Logic –– Run & View Run & View ResultsResults


• Function Function operation and operation and grouping happens grouping happens after all query after all query criteria have been criteria have been met. met.

• Calculation is Calculation is independent of independent of output.output.

• The value repeats The value repeats for each row with for each row with that that group/partition.group/partition.

Grouping LogicGrouping Logic –– Different GroupsDifferent Groups


COUNT (A.EMPLID) OVER (PARTITION BY COUNT (A.EMPLID) OVER (PARTITION BY A.ACAD_LEVEL_BOTA.ACAD_LEVEL_BOT))

Grouping LogicGrouping Logic –– DistinctDistinct

COUNT (A.EMPLID) OVER (PARTITION BY A.STRM)COUNT (A.EMPLID) OVER (PARTITION BY A.STRM)COUNT (A.EMPLID) OVER (PARTITION BY COUNT (A.EMPLID) OVER (PARTITION BY A.ACAD_LEVEL_BOT)A.ACAD_LEVEL_BOT)

COUNT (COUNT (DISTINCTDISTINCT A.EMPLID) OVER (PARTITION BY A.EMPLID) OVER (PARTITION BY A.ACAD_LEVEL_BOT)A.ACAD_LEVEL_BOT)

Grouping LogicGrouping Logic –– Multiple GroupsMultiple GroupsCOUNT (A.EMPLID) OVER (PARTITION BY COUNT (A.EMPLID) OVER (PARTITION BY A.ACAD_LEVEL_BOT)A.ACAD_LEVEL_BOT)COUNT (DISTINCT A.EMPLID) OVER (PARTITION BY COUNT (DISTINCT A.EMPLID) OVER (PARTITION BY

A.ACAD_LEVEL_BOT)A.ACAD_LEVEL_BOT)COUNT (DISTINCT A.EMPLID) OVER COUNT (DISTINCT A.EMPLID) OVER (PARTITION BY A.ACAD_LEVEL_BOT(PARTITION BY A.ACAD_LEVEL_BOT, , A.STRMA.STRM))

Grouping LogicGrouping Logic –– SUM a GroupSUM a Group

Total Credits by Total Credits by ID ID

SUMSUM ( (A.UNT_TAKEN_PRGRSSA.UNT_TAKEN_PRGRSS) OVER (PARTITION BY ) OVER (PARTITION BY A.EMPLIDA.EMPLID))

Grouping LogicGrouping Logic –– Query Tools 1Query Tools 1

COUNT (A.EMPLID) OVER (PARTITION BY COUNT (A.EMPLID) OVER (PARTITION BY 'C''C'))

Group by a Group by a ConstantConstant

COUNT (COUNT (DISTINCTDISTINCT A.EMPLID) OVER (PARTITION BY A.EMPLID) OVER (PARTITION BY 'C''C'))

Grouping LogicGrouping Logic –– Query Tools 2Query Tools 2

Count Multiple ID’s Count Multiple ID’s

COUNT (COUNT (A.EMPLIDA.EMPLID) OVER (PARTITION BY ) OVER (PARTITION BY A.EMPLIDA.EMPLID))

Grouping LogicGrouping Logic –– ORDER BYORDER BY…….. (…..) OVER (PARTITION BY .. (…..) OVER (PARTITION BY A.ACAD_LEVEL_BOT A.ACAD_LEVEL_BOT ORDER BYORDER BY A.CUM_GPAA.CUM_GPA DESCDESC))

((PERCENT_RANK () OVER (PARTITION BY PERCENT_RANK () OVER (PARTITION BY A.ACAD_LEVEL_BOT ORDER BY A.CUM_GPA DESC)A.ACAD_LEVEL_BOT ORDER BY A.CUM_GPA DESC)) * ) * 100 100

PERCENT_RANK ()PERCENT_RANK () OVER (PARTITION BY OVER (PARTITION BY A.ACAD_LEVEL_BOT A.ACAD_LEVEL_BOT ORDER BYORDER BY A.CUM_GPAA.CUM_GPA DESCDESC))

Grouping Logic - Grouping Logic - ANALYTIC ANALYTIC

FUNCTIONSFUNCTIONS


Partition Statement SyntaxPartition Statement Syntax

…….. (…..) OVER (PARTITION BY ….. ORDER BY ….. DESC).. (…..) OVER (PARTITION BY ….. ORDER BY ….. DESC)

The Power The Power ComboCombo

Power Combo – Power Combo – IntroductionIntroduction



CASE WHEN CASE WHEN ((….. (…..) OVER (PARTITION BY ….. (…..) OVER (PARTITION BY ….. )….. ))) > 0> 0 THEN ….. ELSE ….. END THEN ….. ELSE ….. END

…….. (.. (CASE WHEN ….. THEN ….. ELSE ….. CASE WHEN ….. THEN ….. ELSE ….. ENDEND) OVER (PARTITION BY ….. )) OVER (PARTITION BY ….. )

Power Combo – Power Combo – Example 1Example 1

SUMSUM ( (B.UNT_PRGRSSB.UNT_PRGRSS) OVER (PARTITION BY ) OVER (PARTITION BY A.EMPLIDA.EMPLID))

SUM (SUM (CASE WHEN B.ENRL_ADD_DT <= :2 THEN CASE WHEN B.ENRL_ADD_DT <= :2 THEN B.UNT_PRGRSS ENDB.UNT_PRGRSS END) OVER (PARTITION BY A.EMPLID)) OVER (PARTITION BY A.EMPLID)

Total Credits per Total Credits per PersonPerson

Total Credits per Person as of Total Credits per Person as of DateDate

Power Combo – Power Combo – Example 1 - Example 1 - continuedcontinued

WHEN (SUM (CASE WHEN B.ENRL_ADD_DT <= :2 THEN WHEN (SUM (CASE WHEN B.ENRL_ADD_DT <= :2 THEN B.UNT_PRGRSS B.UNT_PRGRSS END) OVER (PARTITION BY A.EMPLID)) END) OVER (PARTITION BY A.EMPLID)) BETWEEN 9 BETWEEN 9 AND 11 AND 11 THEN ‘3Quarter'THEN ‘3Quarter'

WHEN (SUM (CASE WHEN B.ENRL_ADD_DT <= :2 THEN WHEN (SUM (CASE WHEN B.ENRL_ADD_DT <= :2 THEN B.UNT_PRGRSS B.UNT_PRGRSS END) OVER (PARTITION BY A.EMPLID)) END) OVER (PARTITION BY A.EMPLID)) BETWEEN 6 BETWEEN 6 AND 8 AND 8

THEN 'Half'THEN 'Half'

ELSE 'Less'ELSE 'Less'

ENDEND

Enrollment Status as of Enrollment Status as of DateDate

CASE WHEN (CASE WHEN (SUM (CASE WHEN B.ENRL_ADD_DT <= :2 THEN SUM (CASE WHEN B.ENRL_ADD_DT <= :2 THEN B.UNT_PRGRSS B.UNT_PRGRSS END) END) OVER (PARTITION BY A.EMPLID)OVER (PARTITION BY A.EMPLID)) ) >= 12 THEN 'Full'>= 12 THEN 'Full'


Teacher Certification:Teacher Certification:Totals for Ethnicity & Totals for Ethnicity &

GenderGender

Power Combo – Power Combo – Example 2 Example 2 continuedcontinued

Totals for Ethnicity & GenderTotals for Ethnicity & Gender

A.STDNT_GROUP || ': ' || COUNT (DISTINCT A.EMPLID) OVER (PARTITION BY A.STDNT_GROUP)

Power Combo – Power Combo – Example 2 cont.Example 2 cont.


COUNT (DISTINCT (CASE WHEN B.SEX = 'M' THEN A.EMPLID END)) OVER (PARTITION BY A.STDNT_GROUP)



COUNT (DISTINCT (CASE WHEN D.ETHNIC_GROUP = 4 THEN A.EMPLID END)) OVER (PARTITION BY A.STDNT_GROUP)



• Remove all Remove all output fields with output fields with unique data in unique data in them - EMPLID, them - EMPLID, STRM, etc.STRM, etc.



• Select the Select the Distinct Check Distinct Check Box to suppress Box to suppress duplication for duplication for entire query.entire query.

(The Distinct Query (The Distinct Query option happens last option happens last so it does not affect so it does not affect any query selection any query selection criteria or criteria or computation.)computation.)

• Select the Query Select the Query Properties LinkProperties Link


To Produce Grand Total Reports To Produce Grand Total Reports like:like:


Totals of Students & Guests at Orientation Totals of Students & Guests at Orientation MeetingsMeetings

Power Combo – Power Combo – ConclusionConclusion



Combining these two Power Expression Combining these two Power Expression greatly increases the Query Tool’s greatly increases the Query Tool’s flexibility and power.flexibility and power.

What We Have LearnedWhat We Have Learned

•Reporting is a key component of a successful system.Reporting is a key component of a successful system.•Some people will always like Green-bar better.Some people will always like Green-bar better.•Query can be a very flexible and powerful tool.Query can be a very flexible and powerful tool.•The Query Tool is not always the best solution.The Query Tool is not always the best solution.•Help End Users to understand the benefits of Excel.Help End Users to understand the benefits of Excel.•Relying on Query too much for both functional Relying on Query too much for both functional processes and reporting needs can be problematic in the processes and reporting needs can be problematic in the long run.long run.•It is important to have a clear understanding of the It is important to have a clear understanding of the actual need.actual need.•Queries that try to do too much can have performance Queries that try to do too much can have performance issues.issues.•Creativity and perseverance can solve a lot of Creativity and perseverance can solve a lot of problems.problems.

Resources Resources • Harvard – Key Functions in Oracle SQLHarvard – Key Functions in Oracle SQL

http://vpf-web.harvard.edu/applications/ad_hoc/key_functions_in_oracle_sql.pdf

• Oracle SQL Reference Oracle SQL Reference 99i i web: web: http://www.cs.ncl.ac.uk/teaching/facilities/swdoc/oracle9i/server.920/a96540/toc.htm

99i i pdf: pdf: http://www.cs.utah.edu/classes/cs6530/oracle/doc/B10501_01/server.920/http://www.cs.utah.edu/classes/cs6530/oracle/doc/B10501_01/server.920/a96540.pdfa96540.pdf

88i i web:web: http://h50.isi.u-psud.fr/docmiage/oracle/doc/server.817/a85397/toc.htm

88i i pdf:pdf: http://csis.pace.edu/support/sql_reference.pdf

• NoteTab LightNoteTab Light http://www.notetab.com/ntl.php

• GridinSoft Notepad LiteGridinSoft Notepad Lite http://www.gridinsoft.com/downloads.php











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








http://www.notetab.com/ntl.php






http://www.gridinsoft.com/downloads.php




SummarySummary

• You have now been exposed to the concept of You have now been exposed to the concept of using expressions in your queries.using expressions in your queries.

• With the examples given you can see there is With the examples given you can see there is great potential to what you can accomplish great potential to what you can accomplish with just a few powerful expressions and your with just a few powerful expressions and your creativity.creativity.

• Some useful resources have been provided for Some useful resources have been provided for you to increase your understanding of you to increase your understanding of expressions and function statements.expressions and function statements.

ConclusionConclusion

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.

QUESTIONS?QUESTIONS?

ContactContact

Tim McGuireTim McGuireInformation Technology Applications Information Technology Applications SpecialistSpecialist

Central Washington UniversityCentral Washington University

509-963-2921509-963-2921

E-mail: E-mail: [email protected]

mailto:[email protected]





This presentation and all HEUG This presentation and all HEUG 2006 presentations are 2006 presentations are

available for download from available for download from HEUG OnlineHEUG Online

http://heug.orghttp://heug.org

Presentations from previous HEUG meetings are also availablePresentations from previous HEUG meetings are also available

power expressions: taking query to the next level session #20368 (m6) march 13, 2006 ~ monday 3:30 -...

Documents

expressions slide

peoplesoft slide

expressions expressions

tennessee slide

presenter slide

safari slide

query tool ease

query tool conscious