91 | | CREATE TABLE mb3_eigentuemer_suche_union AS |
92 | | |
93 | | SELECT |
94 | | * FROM |
95 | | |
96 | | (SELECT |
97 | | f.flurstueckskennzeichen, |
98 | | f.flurnummer AS flur, |
99 | | f.zaehler AS fs_zaehler, |
100 | | f.nenner AS fs_nenner, |
101 | | f.amtlicheflaeche AS flaeche, |
102 | | f.wkb_geometry AS geom, |
103 | | |
104 | | b.bezeichnung AS bezirkname, |
105 | | g.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt, |
106 | | g.blattart, |
107 | | s.laufendenummer AS bvnr, |
108 | | art.bezeichner AS buchgsart, |
109 | | n.laufendenummernachdin1421 AS name_num, |
110 | | p.nachnameoderfirma AS nachname, |
111 | | p.vorname |
112 | | FROM ax_person p |
113 | | JOIN alkis_beziehungen bpn ON bpn.beziehung_zu = p.gml_id |
114 | | JOIN ax_namensnummer n ON bpn.beziehung_von = n.gml_id |
115 | | JOIN alkis_beziehungen bng ON n.gml_id = bng.beziehung_von |
116 | | JOIN ax_buchungsblatt g ON bng.beziehung_zu = g.gml_id |
117 | | JOIN ax_buchungsblattbezirk b ON g.land = b.land AND g.bezirk = b.bezirk |
118 | | JOIN alkis_beziehungen bgs ON bgs.beziehung_zu = g.gml_id |
119 | | JOIN ax_buchungsstelle s ON s.gml_id = bgs.beziehung_von |
120 | | JOIN ax_buchungsstelle_buchungsart art ON s.buchungsart = art.wert |
121 | | |
122 | | JOIN alkis_beziehungen bf ON s.gml_id = bf.beziehung_zu |
123 | | JOIN ax_flurstueck f ON f.gml_id = bf.beziehung_von |
124 | | |
125 | | UNION |
126 | | |
127 | | SELECT |
128 | | f.flurstueckskennzeichen, |
129 | | f.flurnummer AS flur, |
130 | | f.zaehler AS fs_zaehler, |
131 | | f.nenner AS fs_nenner, |
132 | | f.amtlicheflaeche AS flaeche, |
133 | | f.wkb_geometry AS geom, |
134 | | |
135 | | b.bezeichnung AS bezirkname, |
136 | | g.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt, |
137 | | g.blattart, |
138 | | s.laufendenummer AS bvnr, |
139 | | art.bezeichner AS buchgsart, |
140 | | n.laufendenummernachdin1421 AS name_num, |
141 | | p.nachnameoderfirma AS nachname, |
142 | | p.vorname FROM ax_person p |
143 | | JOIN alkis_beziehungen bpn ON bpn.beziehung_zu = p.gml_id |
144 | | JOIN ax_namensnummer n ON bpn.beziehung_von = n.gml_id |
145 | | JOIN alkis_beziehungen bng ON n.gml_id = bng.beziehung_von |
146 | | JOIN ax_buchungsblatt g ON bng.beziehung_zu = g.gml_id |
147 | | JOIN ax_buchungsblattbezirk b ON g.land = b.land AND g.bezirk = b.bezirk |
148 | | JOIN alkis_beziehungen bgs ON bgs.beziehung_zu = g.gml_id |
149 | | JOIN ax_buchungsstelle s ON s.gml_id = bgs.beziehung_von |
150 | | JOIN ax_buchungsstelle_buchungsart art ON s.buchungsart = art.wert |
151 | | |
152 | | |
153 | | JOIN alkis_beziehungen bg ON s.gml_id = bg.beziehung_von |
154 | | JOIN ax_buchungsstelle bs ON bs.gml_id = bg.beziehung_zu |
155 | | |
156 | | JOIN alkis_beziehungen bf ON bs.gml_id = bf.beziehung_zu |
157 | | |
158 | | JOIN ax_flurstueck f ON f.gml_id = bf.beziehung_von) AS foo |
| 91 | -- DROP VIEW public.qry_mb3_eigentuemer_suche_union; |
| 92 | |
| 93 | CREATE OR REPLACE VIEW public.qry_mb3_eigentuemer_suche_union AS |
| 94 | SELECT foo.flurstueckskennzeichen, |
| 95 | foo.flur, |
| 96 | foo.fs_zaehler, |
| 97 | foo.fs_nenner, |
| 98 | foo.flaeche, |
| 99 | foo.geom, |
| 100 | foo.bezirkname, |
| 101 | foo.gb_blatt, |
| 102 | foo.blattart, |
| 103 | foo.bvnr, |
| 104 | foo.buchgsartwert, |
| 105 | foo.buchgsart, |
| 106 | foo.name_num, |
| 107 | foo.nachname, |
| 108 | foo.vorname |
| 109 | FROM ( SELECT f.flurstueckskennzeichen, |
| 110 | f.flurnummer AS flur, |
| 111 | f.zaehler AS fs_zaehler, |
| 112 | f.nenner AS fs_nenner, |
| 113 | f.amtlicheflaeche AS flaeche, |
| 114 | f.wkb_geometry AS geom, |
| 115 | b.bezeichnung AS bezirkname, |
| 116 | g.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt, |
| 117 | g.blattart, |
| 118 | s.laufendenummer AS bvnr, |
| 119 | art.wert AS buchgsartwert, |
| 120 | art.bezeichner AS buchgsart, |
| 121 | n.laufendenummernachdin1421 AS name_num, |
| 122 | p.nachnameoderfirma AS nachname, |
| 123 | p.vorname |
| 124 | FROM ax_person p |
| 125 | JOIN alkis_beziehungen bpn ON bpn.beziehung_zu = p.gml_id |
| 126 | JOIN ax_namensnummer n ON bpn.beziehung_von = n.gml_id |
| 127 | JOIN alkis_beziehungen bng ON n.gml_id = bng.beziehung_von |
| 128 | JOIN ax_buchungsblatt g ON bng.beziehung_zu = g.gml_id |
| 129 | JOIN ax_buchungsblattbezirk b ON g.land::text = b.land::text AND g.bezirk::text = b.bezirk::text |
| 130 | JOIN alkis_beziehungen bgs ON bgs.beziehung_zu = g.gml_id |
| 131 | JOIN ax_buchungsstelle s ON s.gml_id = bgs.beziehung_von |
| 132 | JOIN v_bs_buchungsart art ON s.buchungsart = art.wert |
| 133 | JOIN alkis_beziehungen bf ON s.gml_id = bf.beziehung_zu |
| 134 | JOIN ax_flurstueck f ON f.gml_id = bf.beziehung_von |
| 135 | WHERE n.endet IS NULL AND g.endet IS NULL AND b.endet IS NULL AND s.endet IS NULL AND f.endet IS NULL |
| 136 | UNION |
| 137 | SELECT f.flurstueckskennzeichen, |
| 138 | f.flurnummer AS flur, |
| 139 | f.zaehler AS fs_zaehler, |
| 140 | f.nenner AS fs_nenner, |
| 141 | f.amtlicheflaeche AS flaeche, |
| 142 | f.wkb_geometry AS geom, |
| 143 | b.bezeichnung AS bezirkname, |
| 144 | g.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt, |
| 145 | g.blattart, |
| 146 | s.laufendenummer AS bvnr, |
| 147 | art.wert AS buchgsartwert, |
| 148 | art.bezeichner AS buchgsart, |
| 149 | n.laufendenummernachdin1421 AS name_num, |
| 150 | p.nachnameoderfirma AS nachname, |
| 151 | p.vorname |
| 152 | FROM ax_person p |
| 153 | JOIN alkis_beziehungen bpn ON bpn.beziehung_zu = p.gml_id |
| 154 | JOIN ax_namensnummer n ON bpn.beziehung_von = n.gml_id |
| 155 | JOIN alkis_beziehungen bng ON n.gml_id = bng.beziehung_von |
| 156 | JOIN ax_buchungsblatt g ON bng.beziehung_zu = g.gml_id |
| 157 | JOIN ax_buchungsblattbezirk b ON g.land::text = b.land::text AND g.bezirk::text = b.bezirk::text |
| 158 | JOIN alkis_beziehungen bgs ON bgs.beziehung_zu = g.gml_id |
| 159 | JOIN ax_buchungsstelle s ON s.gml_id = bgs.beziehung_von |
| 160 | JOIN v_bs_buchungsart art ON s.buchungsart = art.wert |
| 161 | JOIN alkis_beziehungen bg ON s.gml_id = bg.beziehung_von |
| 162 | JOIN ax_buchungsstelle bs ON bs.gml_id = bg.beziehung_zu |
| 163 | JOIN alkis_beziehungen bf ON bs.gml_id = bf.beziehung_zu |
| 164 | JOIN ax_flurstueck f ON f.gml_id = bf.beziehung_von |
| 165 | WHERE n.endet IS NULL AND g.endet IS NULL AND b.endet IS NULL AND s.endet IS NULL AND f.endet IS NULL) foo; |