Object View


CREATE VIEW [dbo].[OM - Object View 1300] AS
  SELECT
    [Type], [Company Name], [ID], [Name], [Modified], [Compiled], [Date], [Time],
    [BLOB Size], [DBM Table No_], [Version List], [Locked], [Locked By], CONVERT(tinyint, 0) Extension
  FROM [Object] o
    LEFT OUTER JOIN [OM - Known Object Export Error] e
      ON o.[Type] = e.[Object Type]
      AND o.[ID] = e.[Object No_]
  WHERE o.[Type] > 0
  AND e.[Object Type] IS NULL
  UNION
  SELECT
    eo.[Object Type], '', eo.[Object No_], eo.[Name], eo.[Modified], eo.[Compiled], [Date], [Time],
    0, 0, [Version List], Locked, [Locked By], CONVERT(tinyint, 1) Extension
  FROM
    (
      SELECT MAX(eo.[Extension No_]) [Extension No_], eo.[Object Type], eo.[Object No_]
      FROM [OM - Extension Object] eo
        INNER JOIN [OM - Extension] e
          ON eo.[Extension No_] = e.[No_]
      GROUP BY eo.[Object Type], eo.[Object No_]
    ) s
    INNER JOIN [OM - Extension Object] eo
      ON s.[Extension No_] = eo.[Extension No_]
      AND s.[Object Type] = eo.[Object Type]
      AND s.[Object No_] = eo.[Object No_]
    LEFT OUTER JOIN [OM - Known Object Export Error] e
      ON eo.[Object Type] = e.[Object Type]
      AND eo.[Object No_] = e.[Object No_]
  WHERE e.[Object Type] IS NULL;

GO