1 / 16

Rekursiiviset kyselyt DB2:ssa DB2YTR 4.5.2010

Rekursiiviset kyselyt DB2:ssa DB2YTR 4.5.2010. Lauri Pietarinen Relational Consulting Oy. Rekursiiviset kyselyt. Käytetään CTE-rakennetta Common Table Expression DB2 LUW & z/OS V8 lähtien Sama rakenne käytössä myös SQLServer:ssä. Milloin tarvitaan?. Hierarkiat Organisaatio Tuote

jemima
Download Presentation

Rekursiiviset kyselyt DB2:ssa DB2YTR 4.5.2010

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. Rekursiiviset kyselyt DB2:ssaDB2YTR 4.5.2010 Lauri Pietarinen Relational Consulting Oy

  2. Rekursiiviset kyselyt • Käytetään CTE-rakennetta • Common Table Expression • DB2 LUW & z/OS V8 lähtien • Sama rakenne käytössä myös SQLServer:ssä

  3. Milloin tarvitaan? • Hierarkiat • Organisaatio • Tuote • Jne... • Ketjut • Verkot, hilat... • Sukulaisuussuhteet • Jne jne...

  4. WITH numerot(taso) AS ( SELECT1 FROM sysibm.sysdummy1 UNION ALL SELECTtaso+1 FROM numerot WHERE taso < 10 ) SELECTtaso FROM numerot ORDER BY taso;

  5. WITH numerot(taso,summa) AS ( SELECT 1, 1 FROM sysibm.sysdummy1 UNION ALL SELECT taso+1, summa+(taso+1) FROM numerot WHERE taso < 10 ) SELECT taso, summa FROM numerot ORDER BY taso;

  6. WITH numerot(taso, lista) AS ( SELECT 1, CAST('1' AS VARCHAR(1000)) FROM sysibm.sysdummy1 UNION ALL SELECT taso+1, lista||',‘ ||STRIP(CHAR(taso+1)) FROM numerot WHERE taso < 10 ) SELECT taso, listaFROM numerot ORDER BY taso;

  7. CREATE VIEW num(n) AS WITH numerot(taso) AS ( SELECT 1 FROM sysibm.sysdummy1 UNION ALL SELECT taso+1 FROM numerot WHERE taso < 1000000 ) SELECT taso FROM numerot; SELECT n FROM num WHERE n <=20;

  8. CREATE TABLE demo.t1 (s1 INT); CREATE VIEW demo.v1 AS SELECT s1 FROM demo.t1; CREATE VIEW demo.v2 AS SELECT s1 FROM demo.v1; CREATE VIEW demo.v12 AS SELECT v1.s1 FROM demo.v1 AS v1, demo.v2; SELECT bcreator, bname, dcreator, dname FROM sysibm.sysviewdep WHERE bcreator = 'DEMO';

  9. WITH nakyma_riippuvuus (omistaja, taulu, taso, polku, vomistaja, vnimi) AS ( SELECT creator, name, 1, STRIP(creator)||'.'||STRIP(name) , creator, name FROM sysibm.systables taulut WHERE creator = 'DEMO' AND type = 'T' UNION ALL SELECT nr.omistaja, nr.taulu, nr.taso + 1, polku||'->‘ ||STRIP(dcreator)||'.'||STRIP(dname), nr2.dcreator, nr2.dname FROM nakyma_riippuvuus nr, sysibm.sysviewdep nr2 WHERE nr.vomistaja = nr2.bcreator AND nr.vnimi = nr2.bname AND taso < 20 ) SELECT omistaja, taulu, taso, polku, vomistaja, vnimi FROM nakyma_riippuvuus ORDER BY taso ;

  10. DEMO.PROJECT (sample-kannan taulu) projektihierarkia, jossa MAJPROJ kertoo ”isäprojektin”

  11. WITH PROJ_HIER (TASO, JUURI, POLKU, PROJEKTI, HTPV) AS ( SELECT 1 , PROJNO, CAST(PROJNO AS VARCHAR(1000)), PROJNO, PRSTAFF FROM PROJECT WHERE MAJPROJ IS NULL UNION ALL SELECT PH.TASO+1,PH.JUURI, PH.POLKU||'->'||LAPSI.PROJNO, LAPSI.PROJNO, LAPSI.PRSTAFF FROM PROJ_HIER PH, PROJ LAPSI WHERE PH.PROJEKTI = LAPSI.MAJPROJ AND TASO < 20 -- MAX 20 TASOA ) SELECT JUURI, POLKU, PROJEKTI, TASO, SUBSTR(‘ ------',1,TASO*2)||PROJEKTIAS SISENNETTY, HTPV FROM PROJ_HIER PH ORDER BY POLKU;

  12. WITH PROJ_HIER (TASO, JUURI, POLKU, PROJEKTI, HTPV) AS ( SELECT 1 , PROJNO, CAST(PROJNO AS VARCHAR(1000)), PROJNO, PRSTAFF FROM PROJECT WHERE MAJPROJ IS NULL UNION ALL SELECT PH.TASO+1,PH.JUURI, PH.POLKU||'->'||LAPSI.PROJNO, LAPSI.PROJNO, LAPSI.PRSTAFF FROM PROJ_HIER PH, PROJ LAPSI WHERE PH.PROJEKTI = LAPSI.MAJPROJ AND TASO < 20 -- MAX 20 TASOA ) SELECT JUURI, SUM(HTPV) AS KOK_HTPV FROM PROJ_HIER PH GROUP BY JUURI ORDER BY JUURI;

  13. Lisää materiaalia 1. Recursive SQL for Dummies – B.L. “Tink” Tysor - IDUG NA 2005 - Session A1 2. Recursive SQL – Unleash the Power! – Suresh Sane - IDUG EU 2007 - Session E5 3. DB2 UDB for z/OS V8– Everything You Ever Wanted to Know, … and More – SG24-6079 4. DB2 UDB for z/OS Version 8 Performance Topics – SG24-6465 5. Having Fun with Complex SQL – Suresh Sane – IDUG AP 2005 - Session B5 6. Parlez-Vous Klingon – Alexander Kopac – IDUG NA 2007 -Session ALT 7. “Rinse, Lather, Repeat: Utilizing Recursive SQL on DB2 UDB for z/OS” – Daniel L. Luksetich – IDUG Solutions Journal May 2004

More Related