CV10 IDAS

CREATE OR REPLACE VIEW cv10_v_pa_ne_prace(jmeno, pocet) AS
  SELECT tr.JMENO,
  (SELECT COUNT(*) FROM A_O_SNEHURCE.TEZBY tezba, A_O_SNEHURCE.TRPASLICI trpaslici
  WHERE trpaslici.ID_TRPASLIKA=tezba.ID_TRPASLIKA
  AND to_char(tezba.DEN, 'D')>=5
  AND tezba.DEN BETWEEN to_date('01.03.2012','DD.MM.YYYY') AND to_date('31.03.2012','DD.MM.YYYY')) AS pocet
  FROM A_O_SNEHURCE.TRPASLICI tr;

  SELECT * FROM CV10_V_PA_NE_PRACE;

CREATE OR REPLACE VIEW cv10_v_podil_tezby_brez(jmeno, procenta) AS
SELECT UPPER(tabTrpaslici.JMENO) Jmeno, to_char(SUM(SKUTECNOST) / (SELECT SUM(SKUTECNOST) FROM A_O_SNEHURCE.TEZBY tabTezby
LEFT JOIN A_O_SNEHURCE.SACHTY tabSachty ON tabTezby.ID_SACHTY = tabSachty.ID_SACHTY
LEFT JOIN A_O_SNEHURCE.RUDY tabRudy ON tabSachty.ID_RUDY = tabRudy.ID_RUDY
WHERE tabRudy.RUDA = 'železná' AND tabTezby.DEN >= '01.02.12' AND tabTezby.DEN <= '15.03.12'
) * 100, '09.99') || ' %' VYTEZENO_CELKEM FROM A_O_SNEHURCE.TEZBY tabTezby
LEFT JOIN A_O_SNEHURCE.SACHTY tabSachty ON tabTezby.ID_SACHTY = tabSachty.ID_SACHTY
LEFT JOIN A_O_SNEHURCE.RUDY tabRudy ON tabSachty.ID_RUDY = tabRudy.ID_RUDY
LEFT JOIN A_O_SNEHURCE.TRPASLICI tabTrpaslici ON tabTezby.ID_TRPASLIKA = tabTrpaslici.ID_TRPASLIKA
WHERE tabTezby.DEN >= '01.02.12' AND tabTezby.DEN <= '15.03.12' AND tabRudy.RUDA = 'železná'
GROUP BY tabTrpaslici.JMENO, tabRudy.RUDA;

SELECT * FROM CV10_V_PODIL_TEZBY_BREZ;


CREATE OR REPLACE VIEW cv10_v_vlastnosti_2011(jmeno, pocet) AS
SELECT UPPER(jmeno), (SELECT COUNT(*) FROM A_O_SNEHURCE.POZNATKY pz WHERE pz.id_trpaslika = trp.id_trpaslika
AND pz.zaznamenano < TO_DATE('01.01.2012')) AS pocet FROM A_O_SNEHURCE.trpaslici trp
WHERE id_trpaslika NOT IN(SELECT id_trpaslika FROM A_O_SNEHURCE.POZNATKY poz JOIN A_O_SNEHURCE.VLASTNOSTI vl
ON poz.ID_VLASTNOSTI = vl.ID_VLASTNOSTI WHERE VLASTNOST = 'Upovídaný' AND ZAZNAMENANO < TO_DATE('01.01.2012'))
ORDER BY jmeno DESC;

SELECT * FROM cv10_v_vlastnosti_2011;