IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_MODULE_LIST_39_UEREL]'))
	DROP VIEW [dbo].[V_MODULE_LIST_39_UEREL]
GO
CREATE VIEW [dbo].[V_MODULE_LIST_39_UEREL] ([PARENT_ID], [MOD_ID], [TYPE]) AS 
  SELECT TOP 100000 [PARENT_ID],
    [MOD_ID],
    [Type]
  FROM
    ( SELECT DISTINCT V_MODULE_LIST_UEREL.MODULE parent_id,
      DT411.DI000111 mod_id,
      CAST(1 AS float) [Type]
    FROM DT411 -- Statement ruft alle Listbox-Module der Module aus [V_MODULE_LIST] ab
    JOIN DT406
    ON DT406.DI000969 = DT411.DI000306
    JOIN V_MODULE_LIST_UEREL
    ON DT406.DI000970      = V_MODULE_LIST_UEREL.MODULE
    WHERE LEN(DI000111) = 6
    UNION ALL
    SELECT DISTINCT V_MODULE_LIST_UEREL.MODULE parent_id,
      DT411.DI027481 mod_id,
      2
    FROM DT411 -- Statement ruft alle Makro-Module der Module aus [V_MODULE_LIST] ab
    JOIN DT406
    ON DT406.DI000969 = DT411.DI000306
    JOIN V_MODULE_LIST_UEREL
    ON DT406.DI000970      = V_MODULE_LIST_UEREL.MODULE
    WHERE LEN(DI027481) = 6
    UNION ALL
    SELECT DISTINCT V_MODULE_LIST_UEREL.MODULE parent_id,
      DT404.DI028040 mod_id,
      3
    FROM DT404 -- Statement ruft alle Untermodule der Module aus [V_MODULE_LIST] ab
    JOIN V_MODULE_LIST_UEREL
    ON DT404.DI028100 = V_MODULE_LIST_UEREL.MODULE
    WHERE DT404.DI028100 LIKE V_MODULE_LIST_UEREL.MODULE
    AND LEN(DI028040) = 6
    ) LMU
  UNION ALL
  SELECT TOP 100000 MODULE [PARENT_ID],
    MODULE ,
    0
  FROM V_MODULE_LIST_UEREL
    --connect by parent_id = mod_id
  ORDER BY [PARENT_ID];
GO
