SELECT
  bh.BOREHOLEID id,
  MDSYS.SDO_GEOMETRY(2001,25832,MDSYS.SDO_POINT_TYPE(bh.XUTM32EUREF89,bh.YUTM32EUREF89,NULL),NULL,NULL) geom,
  bh.BOREHOLENO,
  bh.DRILLBORNO,
  bh.PURPOSE,
  bh.USE,
  bh.DRILENDATE,
  bh.DRILLEDFOR,
  bh.LOCATION,
  bh.BORHPOSTC,
  bh.XUTM32EUREF89,
  bh.YUTM32EUREF89,
  /*
  c_doctypes.LONGTEXT doctype,
  bd.VERSIONNO,
  'https://data.geus.dk/JupiterWWW/document.jsp?fileid='||bd.FILEID report, 
  */
  c_regions.LONGTEXT region, 
  GREATEST( MAX(lith.BOTTOM), bh.DRILLDEPTH) AS dyb_max,
  TO_CHAR(SYSDATE,'YYYY-MM-DD') udtraeksdato
FROM borehole bh
LEFT JOIN lithsamp lith ON bh.boreholeno = lith.boreholeno
INNER JOIN boredoc bd ON bh.boreholeno = bd.boreholeno AND bd.doctype IN ('B','BG') /* Borerapport og borerapport geoteknisk - se https://data.geus.dk/tabellerkoder/koder.html?codetype=717 */
INNER JOIN code c_doctypes ON bd.doctype = c_doctypes.code AND c_doctypes.codetype = 717  /* select codetype from jupfield where tablename = 'BOREDOC' and columnname = 'DOCTYPE'; */
LEFT JOIN code c_regions ON TO_CHAR(bh.region) = c_regions.code 
INNER JOIN storedoc sd ON bd.fileid = sd.fileid
WHERE
   (bh.DATAOWNER='GE' OR bh.DATAOWNER IS NULL) /* Kun ved GEUS som dataowner, kan man indberette jordprøver, når der ikke findes jordprøver i forvejen */
GROUP BY 
  bh.BOREHOLENO,
  bh.BOREHOLEID,
  bh.DRILLBORNO,
  bh.PURPOSE,
  bh.USE,
  bh.DRILENDATE,
  bh.DRILLEDFOR,
  bh.LOCATION,
  bh.BORHPOSTC,
  bh.XUTM32EUREF89,
  bh.YUTM32EUREF89,
  /*
  c_doctypes.LONGTEXT,
  bd.VERSIONNO,
  bd.FILEID,
  */
  c_regions.LONGTEXT,
  bh.DRILLDEPTH
HAVING 
  SUM(CASE WHEN lith.ROCKSYMBOL NOT IN ('b','x') THEN 1 ELSE 0 END) = 0 /* Der må ikke være jordprøver med rocksymbol forskellig fra b eller x */
  AND SUM(CASE WHEN lith.ROCKSYMBOL IS NULL OR lith.ROCKSYMBOL IN ('b','x') THEN 1 ELSE 0 END) = 1 /* Der må kun være 1 jordprøve med enten b, x eller tom rocksymbol */