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>
CP Wiki

Navigation¶

Objekt Informationen abfragen
