1 / 35

DB2 Application Development Managing SQL Complexity By Structuring Your SQL

DB2 Application Development Managing SQL Complexity By Structuring Your SQL. Robert Goodman May 11 th , 3:30 – 4:40 pm Session: B7. Managing SQL Complexity By Structuring Your SQL. Major Points The evolution and growth of SQL Why structuring SQL is important

masako
Download Presentation

DB2 Application Development Managing SQL Complexity By Structuring Your SQL

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. DB2 Application DevelopmentManaging SQL ComplexityBy Structuring Your SQL Robert Goodman May 11th, 3:30 – 4:40 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. 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 What’s New In DB2 SQL? V8 Has Over 500 Pages of SQL Changes!

  8. 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 Don’t expand names just because you can Longer names may clutter SQL Don’t abandon name coding standards With New SQL Limits -It’s Not Can I, But Should I? Bigger Isn't Aways Better!

  9. 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 What is Structured SQL?

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

  11. Make T_EVNT 3rd in the ORDER BY list Which is Easier to Edit? 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 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

  12. Construct Indentation (Step 2) 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 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, I_SYST_COMMC, I_STATS_MESS DESC, T_EVNT 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_CLIE=:H1 AND (I_TYPE_MESS=:H2 OR (I_TYPE_MESS=:H3 AND I_EVNT_SOUR=:H4)) 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_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 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_CLIE=:H1 AND(I_TYPE_MESS=:H2 OR (I_TYPE_MESS=:H3 AND I_EVNT_SOUR=:H4)) 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_CLIE=:H1 AND (I_TYPE_MESS=:H2 OR (I_TYPE_MESS=:H3 AND I_EVNT_SOUR=:H4)) ORDER BY I_CLIE, I_SYST_COMMC, I_STATS_MESS DESC, T_EVNT WITH UR VS.

  15. What three things are wrong with this SQL? Which is Easier to Debug? 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 I_EVNT_SOUR=:H4)) 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_CLIE=:H1 AND (I_TYPE_MESS=:H2 OR (I_TYPE_MESS=:H3 I_EVNT_SOUR=:H4 )) 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_CLIE=:H1 AND ( I_TYPE_MESS=:H2 OR ( I_TYPE_MESS=:H3 AND I_EVNT_SOUR=:H4 ) ) ) 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_CLIE=:H1 AND(I_TYPE_MESS=:H2 OR (I_TYPE_MESS=:H3 AND I_EVNT_SOUR=:H4)) 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_CLIE=:H1 AND ( I_TYPE_MESS=:H2 OR ( I_TYPE_MESS=:H3 AND I_EVNT_SOUR=:H4 ) ) ) ORDER BY I_CLIE ,I_SYST_COMMC ,I_STATS_MESS DESC ,T_EVNT WITH UR IF I_CLIE=:H1 I_TYPE_MESS=:H3 I_STATS_MESS=:H6 I_EVNT_SOUR=:H7 Will this row qualify? 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

  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_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 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. What host variable is equal to I_EVNT_SOUR? Which ORDER BY column is descending? 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 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

  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 ) ) ) -- > USE SYSTEM COMMINICATION INDEX <-- 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.

  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 ) ) ) --> USE SYSTEM COMMINICATION INDEX <-- 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 Remember, This is Simple Stuff...

  22. 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_ORDR_PH') PH50T ON PH50L.JOIN_ALL=PH50T.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_ALL=PH50D.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_ACCN_PATN,I_OPRT_VERF FROM PCTL.VPH97500 WHERE I_CLIE='20' AND T_CREA BETWEEN '2004-01-10-08.00.00' AND '2004-01-10-16.00.00.000000') 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_CLIE=PH50A.I_CLIE AND RS10.I_ACCN_PATN=PH50A.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_HOUR=PH50.T_CREA_HOUR AND PH50D.T_DATE= PH50.T_CREA_DATE AND PH50L.I_LOCT_CORP=PH50.I_LOCT_CORP WHERE('01/10/2004'= '01/10/2004' AND PH50D.T_DATE='01/10/2004' AND PH50H.T_HOUR>=8 AND PH50H.T_HOUR <16) OR ('01/10/2004'<>'01/10/2004' AND PH50D.T_DATE='01/10/2004' AND PH50H.T_HOUR>=8) OR ('01/10/2004'<>'01/10/2004' AND PH50D.T_DATE='01/10/2004' AND PH50H.T_HOUR<16) OR ('01/10/2004'<>'01/10/2004' AND PH50D.T_DATE<> '01/10/2004' AND PH50D.T_DATE <> '01/10/2004') ORDER BY PH50L.I_LOCT_CORP, PH50D.T_DATE,PH50H.T_HOUR,PH50T.I_TYPE_ORDR WITH UR Complex UnstructuredSQL What Does this SQL Do? Would you like to Modify This?

  23. Input SQL Result Set 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 --> GET PHARMACY DATA FOR CRYSTAL REPTS < -- 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 --> GET ALL CORPORATE LOCATIONS <---------- (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 --> GET ALL PHARMACY ORDER TYPES <--------- (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 --> GET ALL HOURS OF PHARMACY DISPENSING <-------- (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 --> GET EACH DATE WITHIN INPUT RANGE <-- (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 … ----> GET PATIENT DATA AND LOCATION <-------- 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 … --> GET ALL PHARMACY/PATIENT DATA FOR REPORT < -- FULL OUTER JOIN ----> GET PHARMACY DATA <------------------------ (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. … continued … --> FILTERS OUT DATA NEEDED FOR THE REPORT <------- WHERE ( :HV10 = :HV10-- STAR = END DATE AND PH50D.T_DATE = :HV10 -- STARTING DATE AND PH50H.T_HOUR >= :HV11 -- STARTING HOUR AND PH50H.T_HOUR < :HV12 -- ENDING HOUR ) OR ( :HV10 <> :HV10 -- STAR <> END DATE AND PH50D.T_DATE = :HV10 -- STARTING DATE AND PH50H.T_HOUR >= :HV11 -- STARTING HOUR ) OR ( :HV10 <> :HV10 -- START <> END DATE AND PH50D.T_DATE = :HV10 -- ENDING DATE AND PH50H.T_HOUR < :HV12 -- ENDING HOUR ) OR ( :HV10 <> :HV10 -- START <> END DATE AND PH50D.T_DATE <> :HV10 -- STARTING DATE AND PH50D.T_DATE <> :HV10 -- ENDING DATE ) ORDER BY PH50L.I_LOCT_CORP ,PH50D.T_DATE ,PH50H.T_HOUR ,PH50T.I_TYPE_ORDR WITH UR Complex Structured SQL (cont.) … 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. 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 What SQL Features Does Your Shop Commonly Use?

  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. 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 What Will the Future Of SQL Mean To You? SQL Will Provide Job Security!

  34. What’s 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@flhosp.org Session B7 – Thanks for Coming…

More Related