DROP VIEW DrawingVWGOcreate view "DrawingVW" ( "ProjectId", "DrawingId", "InternalDrawingNumber", "DrawingTypeId", "DrawingTypeName", "DrawingRevisionNumber", "DrawingRevisionDate", "ParentDrawingId", "ParentRevisionNumber", "ModuleId", "ParentInternalDrawingNumber") as select c1."ProjectId", c1."DrawingId", c1."InternalDrawingNumber", c1."DrawingTypeId", c2."DrawingTypeName", (SELECT TOP 1 DrawingRevisionNumber FROM DrawingRevision WHERE DrawingId = c1.DrawingId ORDER BY Date DESC), (SELECT TOP 1 Date FROM DrawingRevision WHERE DrawingId = c1.DrawingId ORDER BY Date DESC), c1."ParentDrawingId", c1."ParentRevisionNumber", c1."ModuleId", (SELECT InternalDrawingNumber FROM Drawing P WHERE c1.ParentDrawingId = P.DrawingId) from "Drawing" c1, "DrawingType" c2 where c1."DrawingTypeId" = c2."DrawingTypeId" with check option