create view "SpoolVW" ( "SpoolId", "SpoolNo", "ModuleId", "ControlNumber", "DrawingId", "DrawingRevisionNumber", "SpoolRevisionNumber", "SpoolRevisionDate", "InternalDrawingNumber", "SpoolReferenceNumber") as select c1."SpoolId", c1."SpoolNo", c1."ModuleId", c1."ControlNumber", c1."DrawingId", c1."DrawingRevisionNumber", (SELECT TOP 1 SpoolRevisionNumber FROM SpoolRevision WHERE SpoolId = c1.SpoolId ORDER BY Date DESC), (SELECT TOP 1 Date FROM SpoolRevision WHERE SpoolId = c1.SpoolId ORDER BY Date DESC), (SELECT InternalDrawingNumber FROM Drawing D WHERE c1.DrawingId = D.DrawingId), (cast((SELECT InternalDrawingNumber FROM Drawing D WHERE c1.DrawingId = D.DrawingId) AS varchar) + '-' + SpoolNo) from "Spool" c1 with check option