DB2 Application Development Managing SQL Complexity By Structuring Your SQL

1
DB2 Application DevelopmentManaging SQL
ComplexityBy Structuring Your SQL

• Robert Goodman
• May 11th, 330 440 pm
• Session B7

2
Managing SQL ComplexityBy Structuring Your SQL

• Major Points
• The evolution and growth of SQL
• Why structuring SQL is important
• Six steps to structuring your SQL
• Examples of unstructured vs. structured SQL
• What will the future of SQL mean to YOU!

3
Introduction to
4
Why Should We Structure SQL(Structured Query
Language)?
Hold That Question...
5
Structured SQL Objectives

• Demonstrate the need for structured SQL
• Demonstrate the steps to structure SQL
• Show why structured SQL is valuable in conquering
  SQL complexity

6
The Growth of SQL Complexity
7
Whats New In DB2 SQL?

• DB2V8 SQL Enhancements
• INSTEAD OF Triggers
• Insert through UNION ALL Views
• Trigonometric Functions
• UDF and Methods Enhancements
• Materialized Query Tables
• Multi Row INSERT/FETCH
• GROUP BY Expressions
• Multiple DISTINCT clauses
• Recursion
• INSERT with SELECT
• GET Diagnostics
• Sequences
• Dynamic Scrollable Cursors
• Session Variables
• CARDINALITY Function
• Multiple CCSIDs per SQL

• DB2V8 SQL Limits Raised
• SQL size from 32KB to 2MB
• Tables in a JOIN from 15 to 225
• Table/View/Alias names from 18 to 128
• Column Names from 18 to 30
• Predicates from 255 to 32KB
• Constants from 255 to 32KB

V8 Has Over 500 Pages of SQL Changes!
8
With New SQL Limits -Its Not Can I, But Should
I?

• SQL Limits Moved
• 2 MB SQL
• Emphasis on going beyond 32KB
• Up to 255 Tables in FROM
• Much more complex joins
• Table / View / Alias to 128 Bytes
• Languages have line length limits
• Keep short names use Alias for long names
• Column names to 30 Bytes
• Keep them meaningful
• Constants Predicates to 32KB
• Raises the limits

• Codification in Naming
• Dont expand names just because you can
• Longer names may clutter SQL
• Dont abandon name coding standards

Bigger Isn't Aways Better!
9
What is Structured SQL?

• SSQL Defined
• Applying a set of structuring rules to improve
  SQL readability, consistency and maintainability

• Structuring SQL Steps
• 1 Single predicate / line
• 2 Construct indentation
• 3 Leading connectors
• 4 Adding / nesting / aligning of parenthesis
• 5 Alignment of sub parameters
• 6 Add comments

10
Single Predicate Per Line (Step 1)
DECLARE IS000B CURSOR FOR SELECT
I_CLIE, I_TYPE_MESS, I_STATS_MESS, T_EVNT FROM
VIS97000 WHERE I_CLIEH1 AND (I_TYPE_MESSH2
OR (I_TYPE_MESSH3 AND I_EVNT_SOURH4)) ORDER
BY I_CLIE ASC, I_SYST_COMMC ASC, I_STATS_MESS
DESC, T_EVNT ASC WITH UR
DECLARE IS000B CURSOR FOR SELECT I_CLIE,
I_TYPE_MESS, I_STATS_MESS, T_EVNT FROM VIS97000
WHERE I_CLIEH1 AND (I_TYPE_MESSH2 OR
(I_TYPE_MESSH3 AND I_EVNT_SOURH4)) ORDER BY
I_CLIE ASC, I_SYST_COMMC ASC ,I_STATS_MESS DESC,
T_EVNT ASC WITH UR
VS.
11
Which is Easier to Edit?
DECLARE IS000B CURSOR FOR SELECT I_CLIE, I_TYPE_ME
SS, I_STATS_MESS, T_EVNT FROM VIS97000 WHERE
I_CLIEH1 AND (I_TYPE_MESSH2
OR (I_TYPE_MESSH3 AND I_EVNT_SOURH4)) ORDER
BY I_CLIE ASC, I_SYST_COMMC ASC, I_STATS_MESS
DESC, T_EVNT ASC WITH UR

• Make T_EVNT 3rd in the ORDER BY list

DECLARE IS000B CURSOR FOR SELECT I_CLIE,
I_TYPE_MESS, I_STATS_MESS, T_EVNT FROM VIS97000
WHERE I_CLIEH1 AND (I_TYPE_MESSH2 OR
(I_TYPE_MESSH3 AND I_EVNT_SOURH4 )) ORDER BY
I_CLIE ASC, I_SYST_COMMC ASC ,I_STATS_MESS DESC,
T_EVNT ASC WITH UR
12
Construct Indentation (Step 2)
DECLARE IS000B CURSOR FOR SELECT
I_CLIE, I_TYPE_MESS, I_STATS_MESS,
T_EVNT FROM VIS97000 WHERE
I_CLIEH1 AND (I_TYPE_MESSH2 OR
(I_TYPE_MESSH3 AND I_EVNT_SOURH4))
ORDER BY I_CLIE, I_SYST_COMMC,
I_STATS_MESS DESC, T_EVNT WITH UR
DECLARE IS000B CURSOR FOR SELECT I_CLIE, I_TYPE_ME
SS, I_STATS_MESS, T_EVNT FROM VIS97000 WHERE I_CLI
EH1 AND (I_TYPE_MESSH2 OR (I_TYPE_MESSH3
AND I_EVNT_SOURH4)) ORDER BY I_CLIE
ASC, I_SYST_COMMC ASC, I_STATS_MESS DESC, T_EVNT
ASC WITH UR
VS.
13
Which is Easier to Modify?
DECLARE IS000B CURSOR FOR SELECT
I_CLIE, I_TYPE_MESS, I_STATS_MESS,
T_EVNT FROM VIS97000 WHERE
I_CLIEH1 AND (I_TYPE_MESSH2 OR
(I_TYPE_MESSH3 AND I_EVNT_SOURH4))
ORDER BY I_CLIE, I_SYST_COMMC,
I_STATS_MESS DESC, T_EVNT WITH UR
1 Add I_ACCN_PATN after I_TYPE_MESS in SELECT
list
2 What is the second column in the ORDER BY?
3 What table is being accessed?
4 How many conditions in the WHERE clause?
4
DECLARE IS000B CURSOR FOR SELECT I_CLIE,
I_TYPE_MESS, I_STATS_MESS, T_EVNT FROM VIS97000
WHERE I_CLIEH1 AND (I_TYPE_MESSH2 OR
(I_TYPE_MESSH3 AND I_EVNT_SOURH4 )) ORDER BY
I_CLIE ASC, I_SYST_COMMC ASC ,I_STATS_MESS DESC,
T_EVNT ASC WITH UR
4
14
Leading Connectors (Step 3)
DECLARE IS000B CURSOR FOR SELECT
I_CLIE ,I_TYPE_MESS ,I_STATS_MESS
,T_EVNT FROM VIS97000 WHERE
I_CLIEH1 AND(I_TYPE_MESSH2 OR
(I_TYPE_MESSH3 AND I_EVNT_SOURH4))
ORDER BY I_CLIE ,I_SYST_COMMC
,I_STATS_MESS DESC ,T_EVNT WITH UR
DECLARE IS000B CURSOR FOR SELECT
I_CLIE, I_TYPE_MESS, I_STATS_MESS,
T_EVNT FROM VIS97000 WHERE
I_CLIEH1 AND (I_TYPE_MESSH2 OR
(I_TYPE_MESSH3 AND I_EVNT_SOURH4))
ORDER BY I_CLIE, I_SYST_COMMC,
I_STATS_MESS DESC, T_EVNT WITH UR
VS.
15
Which is Easier to Debug?
DECLARE IS000B CURSOR FOR SELECT
I_CLIE I_TYPE_MESS ,I_STATS_MESS
,T_EVNT FROM VIS97000 WHERE
I_CLIEH1 AND(I_TYPE_MESSH2 OR
(I_TYPE_MESSH3 I_EVNT_SOURH4))
ORDER BY ,I_CLIE ,I_SYST_COMMC
,I_STATS_MESS DESC ,T_EVNT WITH UR

• What three things are wrong with this SQL?

DECLARE IS000B CURSOR FOR SELECT I_CLIE
I_TYPE_MESS, I_STATS_MESS, T_EVNT FROM VIS97000
WHERE I_CLIEH1 AND (I_TYPE_MESSH2 OR
(I_TYPE_MESSH3 I_EVNT_SOURH4 )) ORDER BY,
I_CLIE ASC, I_SYST_COMMC ASC ,I_STATS_MESS DESC,
T_EVNT ASC WITH UR
16
Adding/Nesting/Aligning of Parenthesis (Step 4)
DECLARE IS000B CURSOR FOR SELECT
I_CLIE ,I_TYPE_MESS ,I_STATS_MESS
,T_EVNT FROM VIS97000 WHERE (
I_CLIEH1 AND ( I_TYPE_MESSH2
OR ( I_TYPE_MESSH3 AND
I_EVNT_SOURH4 ) ) ) ORDER BY
I_CLIE ,I_SYST_COMMC ,I_STATS_MESS
DESC ,T_EVNT WITH UR
DECLARE IS000B CURSOR FOR SELECT
I_CLIE ,I_TYPE_MESS ,I_STATS_MESS
,T_EVNT FROM VIS97000 WHERE
I_CLIEH1 AND(I_TYPE_MESSH2 OR
(I_TYPE_MESSH3 AND I_EVNT_SOURH4))
ORDER BY I_CLIE ,I_SYST_COMMC
,I_STATS_MESS DESC ,T_EVNT WITH UR
VS.
17
Which is Easier to Understand?
DECLARE IS000B CURSOR FOR SELECT
I_CLIE ,I_TYPE_MESS ,I_STATS_MESS
,T_EVNT FROM VIS97000 WHERE (
I_CLIEH1 AND ( I_TYPE_MESSH2
OR ( I_TYPE_MESSH3 AND
I_EVNT_SOURH4 ) ) ) ORDER BY
I_CLIE ,I_SYST_COMMC ,I_STATS_MESS
DESC ,T_EVNT WITH UR
IF I_CLIEH1 I_TYPE_MESSH3
I_STATS_MESSH6 I_EVNT_SOURH7 Will
this row qualify?
DECLARE IS000B CURSOR FOR SELECT I_CLIE,
I_TYPE_MESS, I_STATS_MESS, T_EVNT FROM VIS97000
WHERE I_CLIEH1 AND I_TYPE_MESSH2 OR
(I_TYPE_MESSH3 AND I_EVNT_SOURH4) ORDER BY
I_CLIE ASC, I_SYST_COMMC ASC ,I_STATS_MESS DESC,
T_EVNT ASC WITH UR
18
Alignment ofSub Parameters (Step 5)
DECLARE IS000B CURSOR FOR SELECT
I_CLIE ,I_TYPE_MESS ,I_STATS_MESS
,T_EVNT FROM VIS97000 WHERE (
I_CLIEH1 AND ( I_TYPE_MESSH2
OR ( I_TYPE_MESSH3 AND
I_EVNT_SOURH4 ) ) ) ORDER BY
I_CLIE ASC ,I_SYST_COMMC ASC
,I_STATS_MESS DESC ,T_EVNT ASC WITH UR
DECLARE IS000B CURSOR FOR SELECT
I_CLIE ,I_TYPE_MESS ,I_STATS_MESS
,T_EVNT FROM VIS97000 WHERE (
I_CLIE H1 AND ( I_TYPE_MESS
H2 OR ( I_TYPE_MESS H3
AND I_EVNT_SOUR H4 ) ) ) ORDER
BY I_CLIE ASC ,I_SYST_COMMC
ASC ,I_STATS_MESS DESC ,T_EVNT
ASC WITH UR
VS.
19
Which is Clearer?
DECLARE IS000B CURSOR FOR SELECT
I_CLIE ,I_TYPE_MESS ,I_STATS_MESS
,T_EVNT FROM VIS97000 WHERE (
I_CLIE H1 AND ( I_TYPE_MESS
H2 OR ( I_TYPE_MESS H3
AND I_EVNT_SOUR H4 ) ) ) ORDER
BY I_CLIE ASC ,I_SYST_COMMC
ASC ,I_STATS_MESS DESC ,T_EVNT
ASC WITH UR

• What host variable is equal to I_EVNT_SOUR?
• Which ORDER BY column is descending?

DECLARE IS000B CURSOR FOR SELECT I_CLIE,
I_TYPE_MESS, I_STATS_MESS, T_EVNT FROM VIS97000
WHERE I_CLIEH1 AND (I_TYPE_MESSH2 OR
(I_TYPE_MESSH3 AND I_EVNT_SOURH4 )) ORDER BY
I_CLIE ASC, I_SYST_COMMC ASC ,I_STATS_MESS DESC,
T_EVNT ASC WITH UR
20
Add Meaningful Comments (Step 6)
DECLARE IS000B CURSOR FOR SELECT
I_CLIE ,I_TYPE_MESS ,I_STATS_MESS
,T_EVNT FROM VIS97000 WHERE (
I_CLIE H1 AND ( I_TYPE_MESS
H2 OR ( I_TYPE_MESS H3
AND I_EVNT_SOUR H4 ) ) ) -- gt USE
SYSTEM COMMINICATION INDEX lt-- ORDER BY
I_CLIE ASC ,I_SYST_COMMC ASC
,I_STATS_MESS DESC ,T_EVNT ASC
WITH UR
DECLARE IS000B CURSOR FOR SELECT
I_CLIE ,I_TYPE_MESS ,I_STATS_MESS
,T_EVNT FROM VIS97000 WHERE (
I_CLIEH1 AND ( I_TYPE_MESSH2
OR ( I_TYPE_MESSH3 AND
I_EVNT_SOURH4 ) ) ) ORDER BY
I_CLIE ASC ,I_SYST_COMMC ASC
,I_STATS_MESS DESC ,T_EVNT ASC WITH UR
VS.
21
Which Would You Rather Maintain?
DECLARE IS000B CURSOR FOR SELECT
I_CLIE ,I_TYPE_MESS ,I_STATS_MESS
,T_EVNT FROM VIS97000 WHERE (
I_CLIE H1 AND ( I_TYPE_MESS
H2 OR ( I_TYPE_MESS H3
AND I_EVNT_SOUR H4 ) ) ) --gt USE
SYSTEM COMMINICATION INDEX lt-- ORDER BY
I_CLIE ASC ,I_SYST_COMMC ASC
,I_STATS_MESS DESC ,T_EVNT ASC
WITH UR
DECLARE IS000B CURSOR FOR SELECT I_CLIE,
I_TYPE_MESS, I_STATS_MESS, T_EVNT FROM VIS97000
WHERE I_CLIEH1 AND (I_TYPE_MESSH2 OR
(I_TYPE_MESSH3 AND I_EVNT_SOURH4 )) ORDER BY
I_CLIE ASC, I_SYST_COMMC ASC ,I_STATS_MESS DESC,
T_EVNT ASC WITH UR
Remember, This is Simple Stuff...
22
Complex UnstructuredSQL
SELECT PH50L.LOCATION,PH50T.ORDER_TYPE,PH50.I_TYPE
_ORDR,PH50D.T_DATE AS DATE,PH50H.T_HOUR
AS HOUR,COALESCE(PH50_COUNT,0) AS PH50_COUNT FROM
(SELECT I_CODE AS I_LOCT_CORP,C_SHOR_DESC AS
LOCATION,'JOIN' AS JOIN_ALL FROM
PCTL.VCP97160 WHERE I_CLIE'20' AND
I_N_STAN'I_LOCT_CORP_PH') PH50L INNER
JOIN (SELECT SUBSTR(I_CODE,1,1) AS
I_TYPE_ORDR,SUBSTR(C_LONG_DESC,1,15) AS
ORDER_TYPE,'JOIN' AS JOIN_ALL FROM PCTL.VCP97160
WHERE I_CLIE'97' AND I_N_STAN'I_TYPE_OR
DR_PH') PH50T ON PH50L.JOIN_ALLPH50T.JOIN_ALL
INNER JOIN (SELECT T_HOUR,'JOIN' AS JOIN_ALL
FROM (SELECT HOUR(T_CREA) AS T_HOUR FROM
PCTL.VPH97500 WHERE I_CLIE'20' AND T_CREA
BETWEEN CURRENT TIMESTAMP - 2 DAYS AND CURRENT
TIMESTAMP) H2 GROUP BY T_HOUR) PH50H ON
PH50L.JOIN_ALL PH50H.JOIN_ALL INNER
JOIN (SELECT T_DATE,'JOIN' AS JOIN_ALL FROM
(SELECT DATE(T_CREA) AS T_DATE FROM
PCTL.VPH97500 WHERE I_CLIE'20' AND T_CREA
BETWEEN '2004-01-10-08.00.00' AND
'2004-01-11-16.00.00') H2 GROUP BY T_DATE)
PH50D ON PH50L.JOIN_ALLPH50D.JOIN_ALL FULL
OUTER JOIN (SELECT
PH50A.I_TYPE_ORDR,PH50A.T_CREA_HOUR,PH50A.T_CREA_
DATE,RS10.I_LOCT_CORP,COUNT() AS PH50_COUNT FROM
(SELECT I_TYPE_ORDR,HOUR(T_CREA) AS T_CREA_HOUR,
DATE(T_CREA) AS T_CREA_DATE,I_CLIE,I_AC
CN_PATN,I_OPRT_VERF FROM PCTL.VPH97500 WHERE
I_CLIE'20' AND T_CREA BETWEEN '2004-01-10-08.00.0
0' AND '2004-01-10-16.00.00.00000
0') AS PH50A INNER JOIN (SELECT
I_CLIE,I_ACCN_PATN, I_LOCT_CORP_CURR AS
I_LOCT_CORP,I_NU_CONF FROM PCTL.VRS97100) RS10 ON
RS10.I_CLIEPH50A.I_CLIE AND
RS10.I_ACCN_PATNPH50A.I_ACCN_PATN WHERE
(RS10.I_LOCT_CORP'' OR '''') AND
(RS10.I_NU_CONF IN ('',' ') OR '''') AND
(PH50A.I_OPRT_VERF'' OR '''') GROUP BY
PH50A.I_TYPE_ORDR, PH50A.T_CREA_HOUR,
PH50A.T_CREA_DATE,RS10.I_LOCT_CORP) PH50 ON
PH50T.I_TYPE_ORDR PH50.I_TYPE_ORDR AND
PH50H.T_HOURPH50.T_CREA_HOUR AND PH50D.T_DATE
PH50.T_CREA_DATE AND PH50L.I_LOCT_CORPPH5
0.I_LOCT_CORP WHERE('01/10/2004'
'01/10/2004' AND PH50D.T_DATE'01/10/2004' AND
PH50H.T_HOURgt8 AND PH50H.T_HOUR lt16) OR
('01/10/2004'ltgt'01/10/2004' AND
PH50D.T_DATE'01/10/2004' AND
PH50H.T_HOURgt8) OR ('01/10/2004'ltgt'01/10/2004'
AND PH50D.T_DATE'01/10/2004' AND
PH50H.T_HOURlt16) OR ('01/10/2004'ltgt'01/10/2004'
AND PH50D.T_DATEltgt '01/10/2004' AND
PH50D.T_DATE ltgt '01/10/2004') ORDER BY
PH50L.I_LOCT_CORP, PH50D.T_DATE,PH50H.T_HOUR,
PH50T.I_TYPE_ORDR WITH UR
What Does this SQL Do?
Would you like to Modify This?
23
Building SQL in Modules to Manage Complexity
Location Query
Query X
Inner Join
Type Order Query
Inner Join
Hour Query
Inner Join
A SQL statement is an expression. Therefore, a
SQL statement can contain multiple SQL
statements, which is know as a nested table
expression.
Date Query
Full Outer Join
Patient Query
Inner Join
Pharmacy Query
24
Building Complex SQLPiecing It All Together
Building powerful complex SQL statements with
ease!
SELECT PH50L.LOCATION ,PH50T.ORDR_TYPE
,PH50D.T_DATE AS DATE ,PH50H.T_HOUR
AS HOUR ,COALESCE(PH50_COUNT,0) AS
PH50_COUNT FROM PH50L Location
Query INNER JOIN PH50T Type Query ON
PH50L.JOIN_All PH50T.JOIN_All INNER JOIN
PH50H Hour Query ON PH50L.JOIN_All
PH50H.JOIN_All INNER JOIN PH50D Date Query ON
PH50L.JOIN_All PH50D.JOIN_All
FULL OUTER JOIN FROM PH50A Pharmacy
Query INNER JOIN RS10 Patient Query ON (
PH50A.I_CLIE RS10.I_CLIE AND
PH50A.I_ACCN_PATN RS10.I_ACCN_PATN ) WHERE
ORDER BY PH50L.I_LOCT_CORP
,PH50D.T_DATE ,PH50H.T_HOUR
,PH50T.I_TYPE_ORDR WITH UR
25
Complex Structured SQL
--gt GET PHARMACY DATA FOR CRYSTAL REPTS lt
-- SELECT
PH50L.LOCATION

,PH50T.ORDER_TYPE
,PH50T.I_TYPE_ORDR

,PH50D.T_DATE AS DATE
,PH50H.T_HOUR AS
HOUR
,COALESCE(PH50_COUNT,0) AS PH50_COUNT
FROM
--gt
GET ALL CORPORATE LOCATIONS lt----------
(SELECT
I_CODE AS
I_LOCT_CORP
,C_SHOR_DESC AS LOCATION
,'JOIN' AS
JOIN_ALL
FROM PCTL.VCP97160
WHERE

( I_CLIE HV1
AND I_N_STAN HV2
) ) PH50L
continued

continued INNER JOIN
--gt GET ALL
PHARMACY ORDER TYPES lt--------- (SELECT

SUBSTR(I_CODE,1,1) AS
I_TYPE_ORDR
,SUBSTR(C_LONG_DESC,1,15) AS ORDER_TYPE
,'JOIN'
AS JOIN_ALL FROM
PCTL.VCP97160
WHERE
(
I_CLIE HV3
AND I_N_STAN HV4
) ) PH50T
ON

( PH50L.JOIN_ALL
PH50T.JOIN_ALL
) continued

Type Query
Location Query
26
Complex Structured SQL (cont.)
continued INNER JOIN
--gt GET ALL HOURS OF PHARMACY
DISPENSING lt-------- (SELECT
T_HOUR
,'JOIN'
AS JOIN_ALL FROM

(SELECT
HOUR(T_CREA) AS T_HOUR
FROM PCTL.VPH97500
WHERE
( I_CLIE
HV1 AND T_CREA
BETWEEN CURRENT TIMESTAMP
2 DAYS
AND CURRENT TIMESTAMP ) )
H2
GROUP BY
T_HOUR
) PH50H
ON
( PH50L.JOIN_ALL
PH50H.JOIN_ALL )
continued

continued INNER JOIN
--gt GET EACH DATE WITHIN
INPUT RANGE lt-- (SELECT
T_DATE
,'JOIN'
AS JOIN_ALL FROM

(SELECT
DATE(T_CREA) AS T_DATE
FROM PCTL.VPH97500
WHERE
(
I_CLIE HV1 AND T_CREA BETWEEN
HV5 AND HV6 )
) H2
GROUP BY
T_DATE
) PH50D
ON
( PH50L.JOIN_ALL
PH50D.JOIN_ALL )
continued

Date Query
Hour Query
27
Complex Structured SQL (cont.)
continued ----gt GET PATIENT DATA AND LOCATION
lt-------- INNER JOIN
(SELECT
I_CLIE

,I_ACCN_PATN
,I_LOCT_CORP_CURR AS I_LOCT_CORP
,I_NU_CONF
FROM PCTL.VRS97100
) AS RS10
ON

( RS10.I_CLIE PH50A.I_CLIE
AND RS10.I_ACCN_PATN PH50A.I_ACCN_PATN
)
WHERE
(RS10.I_LOCT_CORP HV8
OR ''
HV8
)
AND

( RS10.I_NU_CONF IN
(HV8 ,HV9)
OR ''
HV8
)

AND
(
PH50A.I_OPRT_VERF HV8
OR ''
HV8
)
Patient Query
continued

Patient Query
continued --gt GET ALL PHARMACY/PATIENT DATA
FOR REPORT lt -- FULL OUTER JOIN
----gt GET PHARMACY DATA
lt------------------------ (SELECT

PH50A.I_TYPE_ORDR
,PH50A.T_CREA_HOUR
,PH50A.T_CREA_DATE
,RS10.I_LOCT_CORP
,COUNT()
AS PH50_COUNT FROM

(SELECT
I_TYPE_ORDR
,HOUR(T_CREA) AS
T_CREA_HOUR
,DATE(T_CREA) AS T_CREA_DATE
,I_CLIE
,I_ACCN_PATN
,I_OPRT_VERF
FROM
PCTL.VPH97500
WHERE
( I_CLIE HV1
AND T_CREA BETWEEN
HV7 AND HV8 )
) AS PH50A
continued

Pharmacy Query
28
Complex Structured SQL (cont.)
continued --gt FILTERS OUT DATA NEEDED FOR THE
REPORT lt------- WHERE
( HV10
HV10 -- STAR END DATE AND
PH50D.T_DATE HV10 -- STARTING DATE
AND PH50H.T_HOUR gt HV11 -- STARTING
HOUR AND PH50H.T_HOUR lt HV12
-- ENDING HOUR )

OR
( HV10 ltgt HV10
-- STAR ltgt END DATE AND PH50D.T_DATE HV10
-- STARTING DATE AND
PH50H.T_HOUR gt HV11 -- STARTING HOUR
)
OR
(
HV10 ltgt HV10 -- START ltgt END DATE
AND PH50D.T_DATE HV10 -- ENDING DATE
AND PH50H.T_HOUR lt HV12 -- ENDING
HOUR )
OR

( HV10 ltgt HV10 -- START
ltgt END DATE AND PH50D.T_DATE ltgt HV10 --
STARTING DATE AND PH50D.T_DATE
ltgt HV10 -- ENDING DATE )

ORDER BY

PH50L.I_LOCT_CORP
,PH50D.T_DATE

,PH50H.T_HOUR
,PH50T.I_TYPE_ORDR
WITH UR
Patient Query continued GROUP BY
PH50A.I_TYPE_ORDR
,PH50A.T_CREA_HOUR
,PH50A.T_CREA_DATE
,RS10.I_LOCT_CORP
) PH50 ON
(
PH50T.I_TYPE_ORDR PH50.I_TYPE_ORDR AND
PH50H.T_HOUR PH50.T_CREA_HOUR AND
PH50D.T_DATE PH50.T_CREA_DATE AND
PH50L.I_LOCT_CORP PH50.I_LOCT_CORP )
continued

Patient Query
29
Raw Output from the Complex SQL
30
Pretty Output from the Complex SQL
31
What SQL Features Does Your Shop Commonly Use?

• Inner, Outer, Left, Right JOINs
• Case Constructs
• UNION ALL VIEWs
• Nested table expressions
• Column, Scalar, Casting Functions
• Triggers
• Stored Procedures
• Column Constraints
• Temporary Tables
• Data Types
• Summary Tables (MQTs)
• Cursors WITH HOLD

• UDFs UDTs
• SubSELECTs
• Scrollable Cursors
• Savepoints
• DB2 Extenders
• JDBC SQLJ
• XML
• JSPs EJBs
• Identity Columns
• INSERT with SELECT
• UPDATE with SUBSELECT
• DELETE with SUBSELECT

32
Why Should We StructureStructured Query
Language(SQL) ?

• Improves Logic Readability
• Makes Editing Easier
• Decreases Syntax Logic Errors
• Speeds Debugging
• Improves Maintainability
• Lowers SQL Programming Costs
• Improves Coding Productivity
• Supports Modular Building of Components
• Especially Important for Large Complex SQL

33
What Will the Future Of SQL Mean To You?

• Near Term SQL Changes
• SQL Limits Raised
• New Powerful SQL Features
• More web integration
• Massively Complex SQL
• Programs Entirely in SQL
• SQL OLAP BI Awareness
• New/Improved Extenders
• Enhanced Language Interfaces
• SQL, ODBC, JDBC, SQLJ, XML, SOAP and the next
  language

• Longer Term SQL Changes
• Data Appliances
• Program to Structure SQL
• More Multimedia Support
• More logic in the Database
• Programming Languages with SQL natively integrated

SQL Will Provide Job Security!
34
Whats Your Next Move?

• Start Structuring Your SQL
• Personal Education on SQL
• Personal SQL Experience
• SQL New Feature Function Testing
• Evaluation of SQL Applications
• SQL New Feature Function Exploitation
• Staff Education on SQL
• Push the SQL Limits
• Specialized SQL DBAs Programmers

35
Robert Goodman robert.goodman_at_flhosp.org
Session B7 Thanks for Coming