Welcome
Guest
, you are in:
<root>
•
Anmelden
CP Wiki
Navigation
¶
CP Solutions
Startseite
domizil+
Startseite
Administration
Administration
Neue Seite
Alle Seiten
Kategorien
Navigations-Pfade
Datei Manager
Neuer Benutzer
Suche im wiki
»
Zurück
Objekt Informationen abfragen
Modified on 05/05/2009 15:07
by Manuel Siebert
Categorized as
Objektmanagement
,
SQL
====Ziel:==== Mit dieser SQL Anweisung werden die Informationen eines Objektes abgefragt (Anzahl der Arbeiten, Bescheide, Vermerke etc.) <code sql>DECLARE @OB_ID AS VARCHAR(36) SET @OB_ID = :OB_ID SELECT COD.COD_KEY1 AS ART, COD.COD_KEY2 AS SORTIERUNG, COD.COD_TEXT1 AS NAME, 'ANZAHL' = (SELECT count(rs.RS_ID) FROM NET_RECHT_SACHEN rs with(nolock) WHERE rs.OB_ID = @OB_ID), 0 AS BUTTON_NEW, 0 AS BUTTON_LISTE FROM CP_CONFIG CON with(nolock) INNER JOIN CP_CONFIG_DETAIL COD with(nolock) ON(CON.CON_ID = COD.CON_ID) WHERE CON.CON_NUMBER = 1530 AND COD.COD_KEY1 = 101 --Rechtsachen AND COD.COD_KEY3 = 1 UNION ALL SELECT COD.COD_KEY1 AS ART, COD.COD_KEY2 AS SORTIERUNG, COD.COD_TEXT1 AS NAME, 'ANZAHL' = (SELECT count(ver.VER_ID) FROM NET_VERMERK ver with(nolock) WHERE ver.OB_ID = @OB_ID), 0 AS BUTTON_NEW, 0 AS BUTTON_LISTE FROM CP_CONFIG CON with(nolock) INNER JOIN CP_CONFIG_DETAIL COD with(nolock) ON(CON.CON_ID = COD.CON_ID) WHERE CON.CON_NUMBER = 1530 AND COD.COD_KEY1 = 102 --Vermerke AND COD.COD_KEY3 = 1 UNION ALL SELECT COD.COD_KEY1 AS ART, COD.COD_KEY2 AS SORTIERUNG, COD.COD_TEXT1 AS NAME, 'ANZAHL' = (SELECT count(ve.VE_ID) FROM NET_VERTRAG ve with(nolock) LEFT OUTER JOIN NET_OBJEKT ob with(nolock) ON(ob.OB_ID = ve.OB_ID) LEFT OUTER JOIN NET_PER_EINHEIT_ZU pei with(nolock) ON(pei.PEI_ID = ve.PEI_ID) LEFT OUTER JOIN NET_EINHEIT eh with(nolock) ON(pei.EH_ID = eh.EH_ID) WHERE (ve.OB_ID = @OB_ID OR ve.VE_ID IN (SELECT VE_ID FROM NET_VERTRAG_OBJEKT_ZU with(nolock) WHERE OB_ID = @OB_ID)) OR eh.OB_ID = @OB_ID), 0 AS BUTTON_NEW, 0 AS BUTTON_LISTE FROM CP_CONFIG CON with(nolock) INNER JOIN CP_CONFIG_DETAIL COD with(nolock) ON(CON.CON_ID = COD.CON_ID) WHERE CON.CON_NUMBER = 1530 AND COD.COD_KEY1 = 103 --Verträge AND COD.COD_KEY3 = 1 UNION ALL SELECT COD.COD_KEY1 AS ART, COD.COD_KEY2 AS SORTIERUNG, COD.COD_TEXT1 AS NAME, 'ANZAHL' = (SELECT count(be.SV_ID) FROM NET_BELASTUNGEN be with(nolock) WHERE be.OB_ID = @OB_ID), 0 AS BUTTON_NEW, 0 AS BUTTON_LISTE FROM CP_CONFIG CON with(nolock) INNER JOIN CP_CONFIG_DETAIL COD with(nolock) ON(CON.CON_ID = COD.CON_ID) WHERE CON.CON_NUMBER = 1530 AND COD.COD_KEY1 = 104 --Belastungen AND COD.COD_KEY3 = 1 UNION ALL SELECT COD.COD_KEY1 AS ART, COD.COD_KEY2 AS SORTIERUNG, COD.COD_TEXT1 AS NAME, 'ANZAHL' = (SELECT count(auf.AUF_ID) FROM NET_AUFTRAG2 auf with(nolock) WHERE auf.OB_ID = @OB_ID), 0 AS BUTTON_NEW, 0 AS BUTTON_LISTE FROM CP_CONFIG CON with(nolock) INNER JOIN CP_CONFIG_DETAIL COD with(nolock) ON(CON.CON_ID = COD.CON_ID) WHERE CON.CON_NUMBER = 1530 AND COD.COD_KEY1 = 105 --Aufträge AND COD.COD_KEY3 = 1 UNION ALL SELECT COD.COD_KEY1 AS ART, COD.COD_KEY2 AS SORTIERUNG, COD.COD_TEXT1 AS NAME, 'ANZAHL' = (SELECT count(ha.HA_ID) FROM NET_HAFTUNG ha with(nolock) WHERE ha.OB_ID = @OB_ID), 0 AS BUTTON_NEW, 0 AS BUTTON_LISTE FROM CP_CONFIG CON with(nolock) INNER JOIN CP_CONFIG_DETAIL COD with(nolock) ON(CON.CON_ID = COD.CON_ID) WHERE CON.CON_NUMBER = 1530 AND COD.COD_KEY1 = 106 --Haftung AND COD.COD_KEY3 = 1 UNION ALL SELECT COD.COD_KEY1 AS ART, COD.COD_KEY2 AS SORTIERUNG, COD.COD_TEXT1 AS NAME, 'ANZAHL' = (SELECT count(ar.AR_ID) FROM NET_ARBEIT ar with(nolock) WHERE ar.OB_ID = @OB_ID), 0 AS BUTTON_NEW, 0 AS BUTTON_LISTE FROM CP_CONFIG CON with(nolock) INNER JOIN CP_CONFIG_DETAIL COD with(nolock) ON(CON.CON_ID = COD.CON_ID) WHERE CON.CON_NUMBER = 1530 AND COD.COD_KEY1 = 107 --Arbeiten AND COD.COD_KEY3 = 1 UNION ALL SELECT COD.COD_KEY1 AS ART, COD.COD_KEY2 AS SORTIERUNG, COD.COD_TEXT1 AS NAME, 'ANZAHL' = (SELECT count(sc.SC_ID) FROM NET_SCHADEN sc with(nolock) WHERE sc.OB_ID = @OB_ID), 0 AS BUTTON_NEW, 0 AS BUTTON_LISTE FROM CP_CONFIG CON with(nolock) INNER JOIN CP_CONFIG_DETAIL COD with(nolock) ON(CON.CON_ID = COD.CON_ID) WHERE CON.CON_NUMBER = 1530 AND COD.COD_KEY1 = 108 --Schäden AND COD.COD_KEY3 = 1 UNION ALL SELECT COD.COD_KEY1 AS ART, COD.COD_KEY2 AS SORTIERUNG, COD.COD_TEXT1 AS NAME, 'ANZAHL' = (SELECT count(ma.MA_ID) FROM NET_MANGEL ma with(nolock) WHERE ma.OB_ID = @OB_ID), 0 AS BUTTON_NEW, 0 AS BUTTON_LISTE FROM CP_CONFIG CON with(nolock) INNER JOIN CP_CONFIG_DETAIL COD with(nolock) ON(CON.CON_ID = COD.CON_ID) WHERE CON.CON_NUMBER = 1530 AND COD.COD_KEY1 = 130 --MÄNGEL AND COD.COD_KEY3 = 1 UNION ALL SELECT COD.COD_KEY1 AS ART, COD.COD_KEY2 AS SORTIERUNG, COD.COD_TEXT1 AS NAME, 'ANZAHL' = (SELECT count(inv.INV_ID) FROM NET_INVERTAR inv with(nolock) WHERE inv.OB_ID = @OB_ID OR(inv.INV_ID IN (SELECT INV_ID FROM NET_INVENTAR_OBJEKT_ZU with(nolock) WHERE OB_ID = @OB_ID))), 0 AS BUTTON_NEW, 0 AS BUTTON_LISTE FROM CP_CONFIG CON with(nolock) INNER JOIN CP_CONFIG_DETAIL COD with(nolock) ON(CON.CON_ID = COD.CON_ID) WHERE CON.CON_NUMBER = 1530 AND COD.COD_KEY1 = 120 --Inventar AND COD.COD_KEY3 = 1 UNION ALL SELECT COD.COD_KEY1 AS ART, COD.COD_KEY2 AS SORTIERUNG, COD.COD_TEXT1 AS NAME, 'ANZAHL' = (SELECT count(bt.BT_ID) FROM NET_BESCHEID_TERMIN bt with(nolock) WHERE bt.OB_ID = @OB_ID AND bt.BT_KATEGORIE = 'Bescheid'), 0 AS BUTTON_NEW, 0 AS BUTTON_LISTE FROM CP_CONFIG CON with(nolock) INNER JOIN CP_CONFIG_DETAIL COD with(nolock) ON(CON.CON_ID = COD.CON_ID) WHERE CON.CON_NUMBER = 1530 AND COD.COD_KEY1 = 112 --Bescheid AND COD.COD_KEY3 = 1 UNION ALL SELECT COD.COD_KEY1 AS ART, COD.COD_KEY2 AS SORTIERUNG, COD.COD_TEXT1 AS NAME, 'ANZAHL' = (SELECT count(bt.BT_ID) FROM NET_BESCHEID_TERMIN bt with(nolock) WHERE bt.OB_ID = @OB_ID AND bt.BT_KATEGORIE = 'Termin'), 0 AS BUTTON_NEW, 0 AS BUTTON_LISTE FROM CP_CONFIG CON with(nolock) INNER JOIN CP_CONFIG_DETAIL COD with(nolock) ON(CON.CON_ID = COD.CON_ID) WHERE CON.CON_NUMBER = 1530 AND COD.COD_KEY1 = 113 --Termin AND COD.COD_KEY3 = 1 UNION ALL SELECT COD.COD_KEY1 AS ART, COD.COD_KEY2 AS SORTIERUNG, COD.COD_TEXT1 AS NAME, 'ANZAHL' = (SELECT count(az.AZ_ID) FROM NET_AUSSTATTUNG_ZU az with(nolock) WHERE az.OB_ID = @OB_ID AND az.EH_ID is null), 0 AS BUTTON_NEW, 0 AS BUTTON_LISTE FROM CP_CONFIG CON with(nolock) INNER JOIN CP_CONFIG_DETAIL COD with(nolock) ON(CON.CON_ID = COD.CON_ID) WHERE CON.CON_NUMBER = 1530 AND COD.COD_KEY1 = 140 --Ausstattung AND COD.COD_KEY3 = 1 UNION ALL SELECT COD.COD_KEY1 AS ART, COD.COD_KEY2 AS SORTIERUNG, COD.COD_TEXT1 AS NAME, 'ANZAHL' = (SELECT count(ppl.OB_ID) FROM NET_PARKPLATZ ppl with(nolock) WHERE ppl.OB_ID = @OB_ID), 0 AS BUTTON_NEW, 0 AS BUTTON_LISTE FROM CP_CONFIG CON with(nolock) INNER JOIN CP_CONFIG_DETAIL COD with(nolock) ON(CON.CON_ID = COD.CON_ID) WHERE CON.CON_NUMBER = 1530 AND COD.COD_KEY1 = 150 --Parkplätze AND COD.COD_KEY3 = 1 UNION ALL SELECT COD.COD_KEY1 AS ART, COD.COD_KEY2 AS SORTIERUNG, COD.COD_TEXT1 AS NAME, 'ANZAHL' = (SELECT count(itd.ITD_ID) FROM NET_INTERESSENT_DETAIL itd with(nolock) WHERE itd.OB_ID = @OB_ID), 0 AS BUTTON_NEW, 0 AS BUTTON_LISTE FROM CP_CONFIG CON with(nolock) INNER JOIN CP_CONFIG_DETAIL COD with(nolock) ON(CON.CON_ID = COD.CON_ID) WHERE CON.CON_NUMBER = 1530 AND COD.COD_KEY1 = 160 --Interessenten AND COD.COD_KEY3 = 1 UNION ALL SELECT COD.COD_KEY1 AS ART, COD.COD_KEY2 AS SORTIERUNG, COD.COD_TEXT1 AS NAME, 'ANZAHL' = (SELECT count(wgz.GFZ_ID) FROM NET_WF_GF_ZU wgz with(nolock) WHERE wgz.GFZ_FREMD_ID = @OB_ID), 0 AS BUTTON_NEW, 0 AS BUTTON_LISTE FROM CP_CONFIG CON with(nolock) INNER JOIN CP_CONFIG_DETAIL COD with(nolock) ON(CON.CON_ID = COD.CON_ID) WHERE CON.CON_NUMBER = 1530 AND COD.COD_KEY1 = 170 --Geschäftsfall AND COD.COD_KEY3 = 1 UNION ALL SELECT COD.COD_KEY1 AS ART, COD.COD_KEY2 AS SORTIERUNG, COD.COD_TEXT1 AS NAME, 'ANZAHL' = (SELECT count(obb.OBB_ID) FROM NET_OBJEKT_BUDGET obb with(nolock) WHERE obb.OB_ID = @OB_ID AND obb.OBB_TYP = 'OBJEKTBUDGET'), 0 AS BUTTON_NEW, 0 AS BUTTON_LISTE FROM CP_CONFIG CON with(nolock) INNER JOIN CP_CONFIG_DETAIL COD with(nolock) ON(CON.CON_ID = COD.CON_ID) WHERE CON.CON_NUMBER = 1530 AND COD.COD_KEY1 = 180 --Objektbudget AND COD.COD_KEY3 = 1 UNION ALL SELECT COD.COD_KEY1 AS ART, COD.COD_KEY2 AS SORTIERUNG, COD.COD_TEXT1 AS NAME, 'ANZAHL' = (SELECT count(au.OB_ID) FROM NET_AUSSCHREIBUNG au with(nolock) WHERE au.OB_ID = @OB_ID AND au.AUS_GBB = 1), 0 AS BUTTON_NEW, 0 AS BUTTON_LISTE FROM CP_CONFIG CON with(nolock) INNER JOIN CP_CONFIG_DETAIL COD with(nolock) ON(CON.CON_ID = COD.CON_ID) WHERE CON.CON_NUMBER = 1530 AND COD.COD_KEY1 = 190 --Ausschreibung Bau AND COD.COD_KEY3 = 1 UNION ALL SELECT COD.COD_KEY1 AS ART, COD.COD_KEY2 AS SORTIERUNG, COD.COD_TEXT1 AS NAME, 'ANZAHL' = (SELECT count(au.OB_ID) FROM NET_AUSSCHREIBUNG au with(nolock) WHERE au.OB_ID = @OB_ID AND au.AUS_GBH = 1), 0 AS BUTTON_NEW, 0 AS BUTTON_LISTE FROM CP_CONFIG CON with(nolock) INNER JOIN CP_CONFIG_DETAIL COD with(nolock) ON(CON.CON_ID = COD.CON_ID) WHERE CON.CON_NUMBER = 1530 AND COD.COD_KEY1 = 195 --Ausschreibung HV AND COD.COD_KEY3 = 1 UNION ALL SELECT COD.COD_KEY1 AS ART, COD.COD_KEY2 AS SORTIERUNG, COD.COD_TEXT1 AS NAME, 'ANZAHL' = (SELECT count(ma.OB_ID) FROM NET_MANGEL ma with(nolock) WHERE ma.OB_ID = @OB_ID), 0 AS BUTTON_NEW, 0 AS BUTTON_LISTE FROM CP_CONFIG CON with(nolock) INNER JOIN CP_CONFIG_DETAIL COD with(nolock) ON(CON.CON_ID = COD.CON_ID) WHERE CON.CON_NUMBER = 1530 AND COD.COD_KEY1 = 200 --Gewährleistung AND COD.COD_KEY3 = 1 UNION ALL SELECT COD.COD_KEY1 AS ART, COD.COD_KEY2 AS SORTIERUNG, COD.COD_TEXT1 AS NAME, 'ANZAHL' = (SELECT count(ma.OB_ID) FROM NET_MASSENMANGEL ma with(nolock) WHERE ma.OB_ID = @OB_ID AND ma.MM_IST_GBB = 1 ), 0 AS BUTTON_NEW, 0 AS BUTTON_LISTE FROM CP_CONFIG CON with(nolock) INNER JOIN CP_CONFIG_DETAIL COD with(nolock) ON(CON.CON_ID = COD.CON_ID) WHERE CON.CON_NUMBER = 1530 AND COD.COD_KEY1 = 210 --Schlussabnahme Bau AND COD.COD_KEY3 = 1 UNION ALL SELECT COD.COD_KEY1 AS ART, COD.COD_KEY2 AS SORTIERUNG, COD.COD_TEXT1 AS NAME, 'ANZAHL' = (SELECT count(ma.OB_ID) FROM NET_MASSENMANGEL ma with(nolock) WHERE ma.OB_ID = @OB_ID AND MM_IST_GBH = 1 ), 0 AS BUTTON_NEW, 0 AS BUTTON_LISTE FROM CP_CONFIG CON with(nolock) INNER JOIN CP_CONFIG_DETAIL COD with(nolock) ON(CON.CON_ID = COD.CON_ID) WHERE CON.CON_NUMBER = 1530 AND COD.COD_KEY1 = 220 --Schlussabnahme HV AND COD.COD_KEY3 = 1 UNION ALL SELECT COD.COD_KEY1 AS ART, COD.COD_KEY2 AS SORTIERUNG, COD.COD_TEXT1 AS NAME, 'ANZAHL' = (SELECT count(obb.OBB_ID) FROM NET_OBJEKT_BUDGET obb with(nolock) WHERE obb.OB_ID = @OB_ID AND obb.OBB_TYP = 'BAUBUDGET'), 0 AS BUTTON_NEW, 0 AS BUTTON_LISTE FROM CP_CONFIG CON with(nolock) INNER JOIN CP_CONFIG_DETAIL COD with(nolock) ON(CON.CON_ID = COD.CON_ID) WHERE CON.CON_NUMBER = 1530 AND COD.COD_KEY1 = 230 --Baukonto AND COD.COD_KEY3 = 1 ORDER BY SORTIERUNG</code>
ScrewTurn Wiki
version 3.0.1.400. Some of the icons created by
FamFamFam
.