Mit folgender SQL-Sequenz können die Koordinaten in allen Tabellen von HE 7.7 auf Minimum und Maximum getestet werden:
/* Überprüfte Tabellen mit Koordinaten:
Schacht
Speicherschacht
Auslass
Einzeleinleiter
Aussengebiet
Regenschreiber
Reihenfolge der Überprüfung:
Minimum aller x-Koordinaten
Minimum aller y-Koordinaten
Maximum aller x-Koordinaten
Maximum aller y-Koordinaten
LittleQuery legt eine Liste mit den Ergebnissen aller Abfragen an
und zusätzlich je eine Tabelle pro Abfrage mit den Ergebnissen.
*/
/* Minimum aller x-Koordinaten —————————————*/
SELECT ‘schacht’ AS TABELLE, name, xkoordinate AS MIN_XKOORDINATE, ‘ ‘ AS MAX_XKOORDINATE
FROM schacht
WHERE xkoordinate = (SELECT min(xkoordinate)
FROM schacht)
UNION ALL
SELECT ‘speicherschacht’ AS TABELLE, name, xkoordinate AS MIN_XKOORDINATE, ‘ ‘ AS MAX_XKOORDINATE
FROM speicherschacht
WHERE xkoordinate = (SELECT min(xkoordinate)
FROM speicherschacht)
UNION ALL
SELECT ‘auslass’ AS TABELLE, name, xkoordinate AS MIN_XKOORDINATE, ‘ ‘ AS MAX_XKOORDINATE
FROM auslass
WHERE xkoordinate = (SELECT min(xkoordinate)
FROM auslass)
UNION ALL
SELECT ‘einzeleinleiter’ AS TABELLE, name, xkoordinate AS MIN_XKOORDINATE, ‘ ‘ AS MAX_XKOORDINATE
FROM einzeleinleiter
WHERE xkoordinate = (SELECT min(xkoordinate)
FROM einzeleinleiter)
UNION ALL
SELECT ‘aussengebiet’ AS TABELLE, name, xkoordinate AS MIN_XKOORDINATE, ‘ ‘ AS MAX_XKOORDINATE
FROM aussengebiet
WHERE xkoordinate = (SELECT min(xkoordinate)
FROM aussengebiet)
UNION ALL
SELECT ‘regenschreiber’ AS TABELLE, name, xkoordinate AS MIN_XKOORDINATE, ‘ ‘ AS MAX_XKOORDINATE
FROM regenschreiber
WHERE xkoordinate = (SELECT min(xkoordinate)
FROM regenschreiber)
UNION ALL
/* Maximum aller x-Koordinaten —————————————*/
SELECT ‘schacht’ AS TABELLE, name, ‘ ‘ AS MIN_XKOORDINATE, xkoordinate AS MAX_XKOORDINATE
FROM schacht
WHERE xkoordinate = (SELECT max(xkoordinate)
FROM schacht)
UNION ALL
SELECT ‘speicherschacht’ AS TABELLE, name, ‘ ‘ AS MIN_XKOORDINATE, xkoordinate AS MAX_XKOORDINATE
FROM speicherschacht
WHERE xkoordinate = (SELECT max(xkoordinate)
FROM speicherschacht)
UNION ALL
SELECT ‘auslass’ AS TABELLE, name, ‘ ‘ AS MIN_XKOORDINATE, xkoordinate AS MAX_XKOORDINATE
FROM auslass
WHERE xkoordinate = (SELECT max(xkoordinate)
FROM auslass)
UNION ALL
SELECT ‘einzeleinleiter’ AS TABELLE, name, ‘ ‘ AS MIN_XKOORDINATE, xkoordinate AS MAX_XKOORDINATE
FROM einzeleinleiter
WHERE xkoordinate = (SELECT max(xkoordinate)
FROM einzeleinleiter)
UNION ALL
SELECT ‘aussengebiet’ AS TABELLE, name, ‘ ‘ AS MIN_XKOORDINATE, xkoordinate AS MAX_XKOORDINATE
FROM aussengebiet
WHERE xkoordinate = (SELECT max(xkoordinate)
FROM aussengebiet)
UNION ALL
SELECT ‘regenschreiber’ AS TABELLE, name, ‘ ‘ AS MIN_XKOORDINATE, xkoordinate AS MAX_XKOORDINATE
FROM regenschreiber
WHERE xkoordinate = (SELECT max(xkoordinate)
FROM regenschreiber);
/* Minimum aller y-Koordinaten —————————————*/
SELECT ‘schacht’ AS TABELLE, name, ykoordinate AS MIN_YKOORDINATE, ‘ ‘ AS MAX_YKOORDINATE
FROM schacht
WHERE ykoordinate = (SELECT min(ykoordinate)
FROM schacht)
UNION ALL
SELECT ‘speicherschacht’ AS TABELLE, name, ykoordinate AS MIN_YKOORDINATE, ‘ ‘ AS MAX_YKOORDINATE
FROM speicherschacht
WHERE ykoordinate = (SELECT min(ykoordinate)
FROM speicherschacht)
UNION ALL
SELECT ‘auslass’ AS TABELLE, name, ykoordinate AS MIN_YKOORDINATE, ‘ ‘ AS MAX_YKOORDINATE
FROM auslass
WHERE ykoordinate = (SELECT min(ykoordinate)
FROM auslass)
UNION ALL
SELECT ‘einzeleinleiter’ AS TABELLE, name, ykoordinate AS MIN_YKOORDINATE, ‘ ‘ AS MAX_YKOORDINATE
FROM einzeleinleiter
WHERE ykoordinate = (SELECT min(ykoordinate)
FROM einzeleinleiter)
UNION ALL
SELECT ‘aussengebiet’ AS TABELLE, name, ykoordinate AS MIN_YKOORDINATE, ‘ ‘ AS MAX_YKOORDINATE
FROM aussengebiet
WHERE ykoordinate = (SELECT min(ykoordinate)
FROM aussengebiet)
UNION ALL
SELECT ‘regenschreiber’ AS TABELLE, name, ykoordinate AS MIN_YKOORDINATE, ‘ ‘ AS MAX_YKOORDINATE
FROM regenschreiber
WHERE ykoordinate = (SELECT min(ykoordinate)
FROM regenschreiber)
UNION ALL
/* Maximum aller y-Koordinaten —————————————*/
SELECT ‘schacht’ AS TABELLE, name, ‘ ‘ AS MIN_YKOORDINATE, ykoordinate AS MAX_YKOORDINATE
FROM schacht
WHERE ykoordinate = (SELECT max(ykoordinate)
FROM schacht)
UNION ALL
SELECT ‘speicherschacht’ AS TABELLE, name, ‘ ‘ AS MIN_YKOORDINATE, ykoordinate AS MAX_YKOORDINATE
FROM speicherschacht
WHERE ykoordinate = (SELECT max(ykoordinate)
FROM speicherschacht)
UNION ALL
SELECT ‘auslass’ AS TABELLE, name, ‘ ‘ AS MIN_YKOORDINATE, ykoordinate AS MAX_YKOORDINATE
FROM auslass
WHERE ykoordinate = (SELECT max(ykoordinate)
FROM auslass)
UNION ALL
SELECT ‘einzeleinleiter’ AS TABELLE, name, ‘ ‘ AS MIN_YKOORDINATE, ykoordinate AS MAX_YKOORDINATE
FROM einzeleinleiter
WHERE ykoordinate = (SELECT max(ykoordinate)
FROM einzeleinleiter)
UNION ALL
SELECT ‘aussengebiet’ AS TABELLE, name, ‘ ‘ AS MIN_YKOORDINATE, ykoordinate AS MAX_YKOORDINATE
FROM aussengebiet
WHERE ykoordinate = (SELECT max(ykoordinate)
FROM aussengebiet)
UNION ALL
SELECT ‘regenschreiber’ AS TABELLE, name, ‘ ‘ AS MIN_YKOORDINATE, ykoordinate AS MAX_YKOORDINATE
FROM regenschreiber
WHERE ykoordinate = (SELECT max(ykoordinate)
FROM regenschreiber);