SQL
CREATE OR REPLACE VIEW webviewer.mc_vandtype_v AS
SELECT
rownum id,
sub.*,
CASE
WHEN sub.vandtype = 'A' THEN '255 0 0' -- rød
WHEN sub.vandtype = 'B' THEN '255 255 0' -- gul
WHEN sub.vandtype = 'C' THEN '0 255 0' -- grøn
WHEN sub.vandtype = 'D' THEN '0 0 255' -- blå
WHEN sub.vandtype = 'AB' THEN '255 125 0' -- orange
WHEN sub.vandtype = 'ABx' THEN '255 212 0' -- lys orange
WHEN sub.vandtype = 'Ax' THEN '255 125 125' -- lys rød
WHEN sub.vandtype = 'Bx' THEN '255 255 125' -- lys gul
WHEN sub.vandtype = 'Cx' THEN '125 255 125' -- lys grøn
WHEN sub.vandtype = 'Dx' THEN '125 125 255' -- lys blå
WHEN sub.vandtype = 'CDx' THEN '0 255 255' -- cyan
END rgb,
ROW_NUMBER() OVER (PARTITION BY sub.borid, sub.indtagsid ORDER BY DECODE(sub.vandtype,'X',4,'DX',3,'AB',2,1), sub.dato DESC ) prio
FROM
(
SELECT
DISTINCT
b.borid,
i.indtagsid,
i.indtagsnr AS indtag_nr,
CASE WHEN COUNT( DISTINCT anl.anlaegid ) OVER ( PARTITION BY b.borid,i.indtagsid ) > 1 THEN 'Knyttet til flere anlæg' ELSE anl.anlaegsnavn END AS anlaegs_navn,
CASE WHEN COUNT( DISTINCT anl.anlaegid ) OVER ( PARTITION BY b.borid,i.indtagsid ) = 1 THEN anl.anlaegid END AS anlaeg_id,
CASE WHEN COUNT( DISTINCT anl.anlaegid ) OVER ( PARTITION BY b.borid,i.indtagsid ) = 1 THEN anl2.anlaegsnavn END AS overanlaegs_navn,
CASE WHEN COUNT( DISTINCT anl.anlaegid ) OVER ( PARTITION BY b.borid,i.indtagsid ) = 1 THEN anl.virksomhedstype END AS virksomhedstype_kode,
CASE WHEN COUNT( DISTINCT anl.anlaegid ) OVER ( PARTITION BY b.borid,i.indtagsid ) = 1 THEN vl.virksomhedstype1992 END AS virksomhedstype_tekst,
CASE WHEN COUNT( DISTINCT anl.anlaegid ) OVER ( PARTITION BY b.borid,i.indtagsid ) = 1 THEN anl.kommunenr2007 END AS kommune_nr,
CASE WHEN COUNT( DISTINCT anl.anlaegid ) OVER ( PARTITION BY b.borid,i.indtagsid ) = 1 THEN k.kommunenavn END AS kommune_navn,
CASE WHEN COUNT( DISTINCT anl.anlaegid ) OVER ( PARTITION BY b.borid,i.indtagsid ) = 1 THEN ial.lang_tekst END AS indtag_anvendelse_tekst,
CASE
WHEN bf.dybdetopindtag IS NULL AND bf.dybdebundindtag IS NULL AND prv.top IS NULL AND prv.bund IS NULL THEN boringsdybde-2
WHEN bf.dybdetopindtag IS NULL AND bf.dybdebundindtag IS NULL AND prv.top IS NULL THEN prv.bund-2
WHEN bf.dybdetopindtag IS NULL AND bf.dybdebundindtag IS NULL THEN prv.top
WHEN bf.dybdetopindtag IS NULL THEN bf.dybdebundindtag-2
ELSE bf.dybdetopindtag
END indtag_top_dybde,
prv.dato,
CASE
WHEN bf.dybdetopindtag IS NULL AND bf.dybdebundindtag IS NULL AND prv.top IS NULL AND prv.bund IS NULL THEN 'Boringdybde minus 2 m anvendt'
WHEN bf.dybdetopindtag IS NULL AND bf.dybdebundindtag IS NULL AND prv.top IS NULL THEN 'Max. prøve-bund minus 2 m anvendt'
WHEN bf.dybdetopindtag IS NULL AND bf.dybdebundindtag IS NULL THEN 'Min. prøve-top anvendt'
WHEN bf.dybdetopindtag IS NULL THEN 'Dybdebund minus 2 m. anvendt'
ELSE 'Dybdetop anvendt'
END indtag_top_dybde_forklaret,
CASE
WHEN bf.dybdebundindtag IS NULL AND bf.dybdetopindtag IS NULL AND prv.bund IS NULL AND prv.top IS NULL THEN boringsdybde
WHEN bf.dybdebundindtag IS NULL AND bf.dybdetopindtag IS NULL AND prv.bund IS NULL THEN prv.top+2
WHEN bf.dybdebundindtag IS NULL AND bf.dybdetopindtag IS NULL THEN prv.bund
WHEN bf.dybdebundindtag IS NULL THEN bf.dybdetopindtag+2
ELSE bf.dybdebundindtag
END indtag_bund_dybde,
CASE
WHEN bf.dybdebundindtag IS NULL AND bf.dybdetopindtag IS NULL AND prv.bund IS NULL AND prv.top IS NULL THEN 'Boringdybde anvendt'
WHEN bf.dybdebundindtag IS NULL AND bf.dybdetopindtag IS NULL AND prv.bund IS NULL THEN 'Min. prøve-top plus 2 m anvendt'
WHEN bf.dybdebundindtag IS NULL AND bf.dybdetopindtag IS NULL THEN 'Max. prøve-bund anvendt'
WHEN bf.dybdebundindtag IS NULL THEN 'Dybdetop plus 2 m. anvendt'
ELSE 'Dybdebund anvendt'
END indtag_bund_dybde_forklaret,
prv.top AS proeve_top,
prv.bund AS proeve_bund,
ana.*,
CASE
WHEN ana.nitrat IS NULL AND ana.ilt <1 AND ana.jern <0.2 AND ana.sulfat>0.1 THEN 'CDX'
WHEN ana.nitrat >1 AND ana.ilt >1 AND ana.jern <0.2 AND ana.sulfat>0.1 THEN 'A'
WHEN ana.nitrat >1 AND ana.ilt <1 AND ana.jern <0.2 AND ana.sulfat>0.1 THEN 'B'
WHEN ana.nitrat >1 AND ana.ilt >1 AND ana.jern IS NULL AND ana.sulfat IS NULL THEN 'A'
WHEN ana.nitrat >1 AND ana.ilt <1 AND ana.jern IS NULL AND ana.sulfat IS NULL THEN 'B'
WHEN ana.nitrat >1 AND ana.ilt >1 AND ana.jern IS NULL AND ana.sulfat>0.1 THEN 'A'
WHEN ana.nitrat >1 AND ana.ilt <1 AND ana.jern IS NULL AND ana.sulfat>0.1 THEN 'B'
WHEN ana.nitrat >1 AND ana.ilt >1 AND ana.jern <0.2 AND ana.sulfat IS NULL THEN 'A'
WHEN ana.nitrat >1 AND ana.ilt <1 AND ana.jern <0.2 AND ana.sulfat IS NULL THEN 'B'
WHEN ana.nitrat >1 AND ana.ilt IS NULL AND ana.jern <0.2 AND ana.sulfat>0.1 THEN 'AB'
WHEN ana.nitrat >1 AND ana.ilt IS NULL AND ana.jern <0.2 AND ana.sulfat IS NULL THEN 'AB'
WHEN ana.nitrat >1 AND ana.ilt IS NULL AND ana.jern >0.2 AND ana.sulfat>0.1 THEN 'ABx'
WHEN ana.nitrat >1 AND ana.ilt IS NULL AND ana.jern >0.2 AND ana.sulfat IS NULL THEN 'ABx'
WHEN ana.nitrat >1 AND ana.ilt >1 AND ana.jern >0.2 AND ana.sulfat>0.1 THEN 'Ax'
WHEN ana.nitrat >1 AND ana.ilt >1 AND ana.jern >0.2 AND ana.sulfat IS NULL THEN 'Ax'
WHEN ana.nitrat >1 AND ana.ilt <1 AND ana.jern >0.2 AND ana.sulfat>0.1 THEN 'Bx'
WHEN ana.nitrat >1 AND ana.ilt <1 AND ana.jern >0.2 AND ana.sulfat IS NULL THEN 'Bx'
WHEN ana.nitrat <1 AND ana.ilt <5 AND ana.jern >0.002 AND ana.sulfat>20 THEN 'C'
WHEN ana.nitrat <1 AND ana.ilt <5 AND ana.jern IS NULL AND ana.sulfat>20 THEN 'C'
WHEN ana.nitrat <1 AND ana.ilt IS NULL AND ana.jern >0.002 AND ana.sulfat>20 THEN 'C'
WHEN ana.nitrat <1 AND ana.ilt IS NULL AND ana.jern IS NULL AND ana.sulfat>20 THEN 'C'
WHEN ana.nitrat <1 AND ana.ilt >5 AND ana.jern >0.002 AND ana.sulfat>20 THEN 'Cx'
WHEN ana.nitrat <1 AND ana.ilt >5 AND ana.jern IS NULL AND ana.sulfat>20 THEN 'Cx'
WHEN ana.nitrat <1 AND ana.ilt <5 AND ana.jern <0.1 AND ana.sulfat>20 THEN 'Cx'
WHEN ana.nitrat <1 AND ana.ilt IS NULL AND ana.jern <0.1 AND ana.sulfat>20 THEN 'Cx'
WHEN ana.nitrat <1 AND ana.ilt <5 AND ana.jern >0.002 AND ana.sulfat<20 THEN 'D'
WHEN ana.nitrat <1 AND ana.ilt <5 AND ana.jern IS NULL AND ana.sulfat<20 THEN 'D'
WHEN ana.nitrat <1 AND ana.ilt IS NULL AND ana.jern >0.002 AND ana.sulfat<20 THEN 'D'
WHEN ana.nitrat <1 AND ana.ilt IS NULL AND ana.jern IS NULL AND ana.sulfat<20 THEN 'D'
WHEN ana.nitrat <1 AND ana.ilt >5 AND ana.jern >0.002 AND ana.sulfat<20 THEN 'Dx'
WHEN ana.nitrat <1 AND ana.ilt >5 AND ana.jern IS NULL AND ana.sulfat<20 THEN 'Dx'
WHEN ana.nitrat <1 AND ana.ilt <5 AND ana.jern <0.1 AND ana.sulfat<20 THEN 'Dx'
WHEN ana.nitrat <1 AND ana.ilt IS NULL AND ana.jern <0.1 AND ana.sulfat<20 THEN 'Dx'
END vandtype,
prv.kationsumberegnet,
prv.anionsumberegnet,
CASE
WHEN prv.anionsumberegnet IS NULL THEN NULL
WHEN prv.anionsumberegnet = 0 THEN -99999
ELSE (prv.kationsumberegnet-prv.anionsumberegnet)/(prv.kationsumberegnet + prv.anionsumberegnet)*100
END ionbalance,
pfl.lang_tekst proeveformaal_tekst,
REPLACE('http://jupiter.geus.dk/cgi-bin/analysisTimeSeries.exe/svg?dgunr='||REPLACE(b.dgunr,' ')||'[+]indtagsid='||TO_CHAR(i.indtagsid)||'[+]stofnr=1551[+]stofnr=2081[+]stofnr=305[+]stofnr=1176[+]stofnr=2041[+]stofnr=2142[+]stofnr=251[+]stofnr=2501[+]stofnr=9939','[+]','&') url_tidsserie
FROM
mis.bor$boring b
INNER JOIN
mis.gvk$proeve prv
ON prv.borid = b.borid
INNER JOIN
(
-- grupperet på prøveID
SELECT
2*( (MAX( CASE WHEN t.stofnr = 1551 THEN t.maengde/40.1 END ) +
MAX( CASE WHEN t.stofnr = 2081 THEN t.maengde/24.3 END )) /
MAX (CASE WHEN t.stofnr = 305 AND t.maengde > 0 THEN t.maengde/61 END) ) forvitringsindeks,
MAX( CASE WHEN t.stofnr = 251 THEN t.maengde END ) ilt,
MAX( CASE WHEN t.stofnr = 1176 THEN t.maengde END ) nitrat,
MAX( CASE WHEN t.stofnr = 2041 THEN t.maengde END ) jern,
MAX( CASE WHEN t.stofnr = 2142 THEN t.maengde END ) sulfat,
MAX( CASE WHEN t.stofnr = 2501 THEN t.maengde END ) methan,
MAX( CASE WHEN t.stofnr = 9939 THEN t.maengde END ) dihydrogensulfid,
MAX( CASE WHEN t.stofnr = 1011 THEN t.maengde END ) ammonium,
t.proeveid
FROM
mis.gvk$analyse t
WHERE
t.stofnr IN (1011,1551,2081,305,1176,2041,2142,251,2501,9939)
AND
(t.kvalitetssikring IS NULL OR t.kvalitetssikring IN (0,1,2,3,7,9,10,11 ))
AND
(t.attribut IS NULL OR t.attribut NOT IN ('<','!','/','A','B','C','D','E','S'))
GROUP BY
t.proeveid
HAVING
MAX( CASE WHEN t.stofnr = 1176 THEN t.maengde END ) > 0 -- nitrat
AND MAX( CASE WHEN t.stofnr = 2041 THEN t.maengde END ) > 0 -- jern
AND MAX( CASE WHEN t.stofnr = 2142 THEN t.maengde END ) > 0 -- sulfat
) ana
ON prv.proeveid = ana.proeveid
LEFT OUTER JOIN
mis.bor$indtag i
ON i.borid = prv.borid
AND i.indtagsid = prv.indtagsid
LEFT OUTER JOIN
mis.drv$indtag_anlaeg indanl
ON indanl.borid = i.borid
AND indanl.indtagsid = i.indtagsid
LEFT OUTER JOIN
mis.drv$anlaeg anl
ON anl.anlaegid = indanl.anlaegid
LEFT OUTER JOIN
mis.drv$virksomhedstype_liste vl
ON vl.kode = anl.virksomhedstype
LEFT OUTER JOIN
mis.drv$anlaeg anl2
ON anl2.anlaegid = anl.overanlaeg
LEFT OUTER JOIN
mis.bor$indtaganvend_liste ial
ON ial.kode = indanl.indtagsanvendelse
LEFT OUTER JOIN
mis.gen$kommune2007 k
ON k.kommunenr = anl.kommunenr2007
LEFT OUTER JOIN
mis.gen$vandproeveformaal_liste pfl
ON pfl.kode = prv.formaal
LEFT OUTER JOIN
(SELECT
borid,
indtagsid,
MIN (top) AS dybdetopindtag, -- der kan være flere filtre på indtaget - benyt øverste som top
MAX (bund) AS dybdebundindtag -- der kan være flere filtre på indtaget - benyt nederste som bund
FROM
mis.bor$filter
GROUP BY
borid, indtagsid
) bf
ON bf.borid = i.borid
AND bf.indtagsid = i.indtagsid
WHERE
(prv.proevestatus_kode IS NULL OR prv.proevestatus_kode IN (2,4,6))
-- debug AND prv.proeveid = 100346
AND
b.xutm32euref89 <> 0
) sub
ORDER BY
sub.borid, sub.indtagsid, DECODE(sub.vandtype,'X',4,'DX',3,'AB',2,1), sub.dato DESC;
GEUS - Øster Voldgade 10 - 1350 København K - Siden vedligeholdes af bpj@geus.dk