Best Practices DECODE and Pivot Data with CASE DB2 for z/OS

DB2 for z/OS
Best Practices
DECODE and Pivot Data with
CASE
Sheryl M. Larsen
IBM WW DB2 for z/OS Evangelist
[email protected]
© 2014 IBM Corporation
DB2 for z/OS Best Practices
IBM®
Disclaimer/Trademarks
© Copyright IBM Corporation 2013. All rights reserved.
U.S. Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM
Corp.
THE INFORMATION CONTAINED IN THIS DOCUMENT HAS NOT BEEN SUBMITTED TO ANY FORMAL IBM TEST AND IS DISTRIBUTED AS IS.
THE USE OF THIS INFORMATION OR THE IMPLEMENTATION OF ANY OF THESE TECHNIQUES IS A CUSTOMER RESPONSIBILITY AND
DEPENDS ON THE CUSTOMER’S ABILITY TO EVALUATE AND INTEGRATE THEM INTO THE CUSTOMER’S OPERATIONAL ENVIRONMENT.
WHILE IBM MAY HAVE REVIEWED EACH ITEM FOR ACCURACY IN A SPECIFIC SITUATION, THERE IS NO GUARANTEE THAT THE SAME OR
SIMILAR RESULTS WILL BE OBTAINED ELSEWHERE. ANYONE ATTEMPTING TO ADAPT THESE TECHNIQUES TO THEIR OWN
ENVIRONMENTS DO SO AT THEIR OWN RISK.
ANY PERFORMANCE DATA CONTAINED IN THIS DOCUMENT WERE DETERMINED IN VARIOUS CONTROLLED LABORATORY
ENVIRONMENTS AND ARE FOR REFERENCE PURPOSES ONLY. CUSTOMERS SHOULD NOT ADAPT THESE PERFORMANCE NUMBERS TO
THEIR OWN ENVIRONMENTS AS SYSTEM PERFORMANCE STANDARDS. THE RESULTS THAT MAY BE OBTAINED IN OTHER OPERATING
ENVIRONMENTS MAY VARY SIGNIFICANTLY. USERS OF THIS DOCUMENT SHOULD VERIFY THE APPLICABLE DATA FOR THEIR SPECIFIC
ENVIRONMENT.
Trademarks
IBM, the IBM logo, ibm.com, and DB2 are trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. Other
product and service names might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the Web at “Copyright and
trademark information” at www.ibm.com/legal/copytrade.shtml.
© 2014 IBM Corporation
2
DB2 for z/OS Best Practices
IBM®
The powerful Case Expression
1)
DECODE
2)
Simple
3)
Searched
4)
Table Pivots
© 2014 IBM Corporation
3
IBM®
DB2 for z/OS Best Practices
DECODE –
the ultimate simple CASE
.---------------------------------.
V
|
>>-DECODE-(-expression1-,-expression2-,-result-expression-+-+-------------------+-)-><
'-,-else-expression-'
Examples:
DECODE
Equivalent CASE Expression
DECODE(c1, 7, 'a', 6, 'b', 'c')
CASE c1
WHEN 7 THEN 'a'
WHEN 6 THEN 'b'
ELSE 'c'
END
DECODE(c1, var1, 'a', var2, 'b')
CASE
WHEN c1 = var1 OR (c1 IS NULL AND var1 ISNULL) THEN 'a'
WHEN c1 = var2 OR (c1 IS NULL AND var2 ISNULL) THEN 'b'
ELSE NULL
END
© 2014 IBM Corporation
IBM®
DB2 for z/OS Best Practices
CASE syntax
CASE
ELSE NULL
END
searched-when-clause
simple-when-clause
ELSE
result-expression
searched-when-clause
WHEN
search-condition
THEN
result-expression
NULL
simple-when-clause
expression
WHEN
expression
THEN
result-expression
NULL
© 2014 IBM Corporation
5
DB2 for z/OS Best Practices
IBM®
Simple & Searched WHEN clauses
SELECT
Simple WHEN
CASE C1 – C3
clause can only
WHEN 0 THEN ‘OFF’
test for =
WHEN 1 THEN ‘ON’
ELSE ‘INVALID’
END
,CASE
WHEN C1 – C3 < 0 THEN ‘NEGATIVE’
WHEN C1 – C3 > 0 THEN ‘POSITIVE’
Searched
ELSE ‘ZERO’
WHEN
clause
END
© 2014 IBM Corporation
6
DB2 for z/OS Best Practices
IBM®
Case within a Case
SELECT
CASE C1 – C3
WHEN 0 THEN
CASE
WHEN C2+C7 > 5 THEN ‘OFF’
ELSE ‘Maybe’
END
WHEN 1 THEN ‘ON’
ELSE ‘INVALID’
END
© 2014 IBM Corporation
7
IBM®
DB2 for z/OS Best Practices
Simple Case Example
SELECT
CLNT_ID AS “Client ID"
,(CASE CC.CLNT_CC_TYP_CD
WHEN ‘A' THEN ‘American Express'
WHEN ‘C' THEN ‘Carte Blanche‘
WHEN ‘D' THEN ‘Diners Club'
WHEN ‘I' THEN ‘Discover'
WHEN ‘M' THEN ‘Mastercard'
WHEN ‘V' THEN ‘VISA'
ELSE 'Unknown'
END) AS “Credit Card"
FROM
CLNT_CC CC;
8© 2014 IBM Corporation
Implied =
Client ID Credit Card
------------ ---------------000000000101 Diners Club
000000000201 VISA
000000000301 Mastercard
000000000401 Discover
000000000501 American Express
000000000601 VISA
000000000701 Diners Club
000000000801 Mastercard
000000000901 VISA
000000001001 American Express
000000001101 Mastercard
000000001201 VISA
000000001301 American Express
000000001401 Diners Club
000000001501 Mastercard
000000001601 VISA
000000001701 Mastercard
000000001801 American Express
000000001901 Carte Blanche
000000002001 VISA
000000002002 Diners Club
000000000001 American Express
IBM®
DB2 for z/OS Best Practices
Searched Case Example
SELECT
CLNT_ID AS “Client ID"
,(CASE
WHEN CLNT_ADDR_START_DT < '06/30/1998'
THEN ‘Long Term'
WHEN CLNT_ADDR_START_DT > '12/31/1999'
THEN 'Newbie'
ELSE 'Intermediate'
END) AS “Resident"
FROM
CLNT_ADDR_REL R;
9© 2014 IBM Corporation
Client ID
Resident
------------ -----------000000001301 Long Term
000000001501 Intermediate
000000001601 Long Term
000000001601 Newbie
000000001901 Long Term
000000000101 Newbie
000000001201 Newbie
000000001701 Newbie
000000000201 Long Term
000000002002 Newbie
10 record(s) selected.
IBM®
DB2 for z/OS Best Practices
Case in WHERE clause
SELECT
CC.CLNT_ID AS "Client ID"
,CC.CC_TYP_CD AS “Card Type"
,CC.CC_EXP_YR AS “Expiration Yr"
FROM
CLNT_CC CC
WHERE
CC_EXP_YR > CASE
WHEN CC.CC_TYP_CD = 'M' THEN ‘08'
WHEN CC.CC_TYP_CD = ‘V' THEN ‘07'
ELSE ‘09'
END
;
10© 2014 IBM Corporation
Client ID
-----------000000000101
000000000201
000000000601
000000000701
000000000801
000000000901
000000001801
000000001901
000000002001
000000000001
Card Type
--------D
V
V
D
M
V
A
C
V
A
Expiartion Yr
---------11
09
12
11
10
09
12
11
10
10
10 record(s) selected.
IBM®
DB2 for z/OS Best Practices
CASE for Identifying
((CASE LENGTH(NAME_PREFX)
WHEN 0 THEN 0 ELSE 1 END) +
MIN(CASE WHEN EMLC.DC_ADR_EML = 'H'
(CASE LENGTH(NAME_SFX)
AND EMLC.DC_STA_PD32 = 'A' THEN '1'
WHEN 0 THEN 0 ELSE 1 END) +
(CASE LENGTH(NAME_MID) +
WHEN EMLC.DC_ADR_EML = 'W'
AND EMLC.DC_STA_PD32 = 'A' THEN '2'
…… END)) AS TOT_RANK
WHEN EMLC.DC_ADR_EML = 'A'
AND EMLC.DC_STA_PD32 = 'A' THEN '3'
END ) AS LABEL
© 2014 IBM Corporation
(CASE WHEN colx > 99 THEN '1'
WHEN colx = 14 THEN '2'
WHEN colx = 78 THEN '3'
WHEN colx = 22 THEN ‘4'
WHEN colx = 66 THEN ‘5'
END ) AS CHOSEN_ONE
11
DB2 for z/OS Best Practices
IBM®
Table Pivoting - Quarter by LINEOBIZ
SELECT
QUARTER(CLNT_PLCY_START_DT) AS Quarter
,SUM(CASE LINOBIZ WHEN 'A' THEN 1 ELSE 0 END) AS "Accident"
,SUM(CASE LINOBIZ WHEN 'F' THEN 1 ELSE 0 END) AS "Fire"
,SUM(CASE LINOBIZ WHEN 'H' THEN 1 ELSE 0 END) AS "Home Owners"
,SUM(CASE LINOBIZ WHEN 'L' THEN 1 ELSE 0 END) AS "Liability"
,SUM(CASE LINOBIZ WHEN 'S' THEN 1 ELSE 0 END) AS "Special"
,SUM(CASE LINOBIZ WHEN 'X' THEN 1 ELSE 0 END) AS "Other"
FROM
POLICY
GROUP BY
QUARTER(CLNT_PLCY_START_DT)
ORDER BY
QUARTER(CLNT_PLCY_START_DT)
;
QUARTER
Accident
Fire
Home Owners Liability
Special
Other
----------- ----------- ----------- ----------- ----------- ----------- ----------1
1
0
2
1
0
0
2
5
1
0
1
2
1
3
0
2
1
0
0
0
4
0
1
0
0
2
0
4 record(s) selected.
© 2014 IBM Corporation
12
IBM®
DB2 for z/OS Best Practices
DECODE and Pivot Data with CASE
w Use the power of CASE to gain insights!
DECODE, Simple CASE
Searched CASE
zEnterprise
Table pivot using CASE
© 2014 IBM Corporation
13