Missing initial statement from SQL View in Design Mode

Question

I have had repeated problems editing SQL View statements that contain statements prior to the initial SELECT. I need to recreate this in this statement, but I cant seem to remember what it was, and I have no idea how to extract it properly in SSMS. This is a working View, that has been working for more than two years. I just need to make a simple edit.

This is what SSMS 18.8 brings into designer:

SELECT TOP (100) PERCENT od.ProductID, od.LineNumber, od.QtyOrdered, od.QtyInvoiced, od.QtyScheduled, od.QtyPicked, od.QtyShipped, od.QtyBackordered, od.QtyOutstanding, od.GUIDOrderdetail, od.OrderNumber, od._SNValve, CASE WHEN Patindex('%-%', od._SNValve) > 0 THEN Substring(od._SNValve, 0, Patindex('%-%', od._SNValve)) 
           ELSE Substring(od._SNValve, 0, Len(od._SNValve) + 1) END AS First_SN, CASE WHEN Patindex('% %', od._SNValve) > 0 THEN Substring(od._SNValve, Patindex('% %', od._SNValve), Len(od._SNValve)) ELSE '' END AS Addnl_SN, CASE WHEN (Patindex('%-%', od._SNValve) > 0 OR
           Patindex('%,%', od._SNValve) > 0) THEN Substring(od._SNValve, Len(od._SNValve) -
               (SELECT min(NULLIF (xxx, 0))
              FROM   (VALUES (Patindex('%-%', Reverse(od._SNValve))), (Patindex('%,%', Reverse(od._SNValve))), (Patindex('% %', Reverse(od._SNValve)))) AS value(xxx)) + 2, Len(od._SNValve)) ELSE (od._SNValve) END AS Last_SN, o.EntryDate, od.OrderNumber + '-' + Cast(od.LineNumber AS VARCHAR(5)) AS OrderNumOB, Dateadd(hour, 5, 
od._ShipDate) AS _ShipDate2, od._ShipDate, od.Description, od.ShortDescription, od.Completed, od.LineCancelled, od.Price, od.PriceUnit, od.Amount, od.SpecialInstructions, od.InvoiceComment, od.Note, od.InventoryControlType, Cast(od.ActivityDate AS DATE) AS ActivityDate, Cast(od._InPlantDate AS DATE) AS _InPlantDate, od.Reference, 
Cast(o.OrderDate AS DATE) AS OrderDate, o.OrderStatus, Cast(o.StatusDate AS DATE) AS StatusDate, o.StatusChangedBy, Cast(o.RequestedShipDate AS DATE) AS RequestedShipDate, Datepart(year, od._ShipDate) AS RSD_Year, Datepart(month, od._ShipDate) AS RSD_Month, Datepart(day, od._ShipDate) AS RSD_Day, c.NAME, 
o.SchedTotalAmount, o.TotalAmount, o.GUIDOrder, o.WorkflowStatusAbbreviation, Row_number() OVER (ORDER BY o.OrderNumber ASC) AS RowNumberTmp, Datediff(month, EntryDate, Getdate()) AS EntryDateMonthsAgo, Datediff(minute, EntryDate, Getdate()) AS EntryDateMinutesAgo, 
CASE WHEN (od.QtyOrdered = od.QtyShipped AND od.QtyOrdered <> 0) THEN 'Shipped' ELSE CASE WHEN (od.QtyPicked <> 0) THEN 'Picked' ELSE CASE WHEN (od.QtyScheduled <> 0) THEN 'Scheduled' ELSE CASE WHEN (od.QtyInvoiced <> 0) THEN 'Invoiced' ELSE CASE WHEN (od.QtyOrdered <> 0) THEN 'Ordered' ELSE NULL 
END END END END END AS StatusCheck, CASE WHEN od.OrderNumber LIKE 'Kel%' THEN 2 WHEN od.OrderNumber LIKE 'RMA%' OR
od.OrderNumber LIKE 'DC%' OR
od.OrderNumber LIKE 'NW%' OR
od.OrderNumber LIKE 'BT%' OR
od.OrderNumber LIKE 'DC%' OR
od.OrderNumber LIKE 'HF%' OR
(Isnumeric(LEFT(od.ProductID, 1)) = 1 AND Substring(od.ProductID, 2, 1) = '-') THEN 5 WHEN Isnumeric(Substring(od.ProductID, 1, 6)) = 1 OR
(LEFT(od.ProductID, 1) = 'K' AND Isnumeric(Substring(od.ProductID, 2, 4)) = 1) THEN 4 ELSE 1 END AS OrderType, CASE WHEN od.ProductID LIKE 'JS%' OR
od.ProductID LIKE 'PC%' THEN 1 ELSE 0 END AS RailincProduct
FROM   [KT-RD-01\acctivate].acctivate$ktech.dbo.OrderDetail AS od INNER JOIN
           [KT-RD-01\acctivate].acctivate$ktech.dbo.Orders AS o ON od.GUIDOrder = o.GUIDOrder INNER JOIN
           [KT-RD-01\acctivate].acctivate$ktech.dbo.customer AS c ON o.GUIDCustomer = c.GUIDCustomer
WHERE ((o.OrderStatus = 'S') OR
           (o.OrderStatus = 'C') OR
           (o.OrderStatus = 'K') OR
           (o.OrderStatus = '')) AND ((od.QtyOrdered <> 0) AND od.ProductID <> 'shipping')
ORDER BY OrderType DESC, o.RequestedShipDate DESC, OrderNumOB ASC;

What is missing is probably some With statement, and a '(' before SELECT, as shown:

With xxxxxxxxxx As

(SELECT TOP (100) PERCENT od.ProductID, od.LineNumber, od.QtyOrdered, od.QtyInvoiced, od.QtyScheduled, od.QtyPicked, od.QtyShipped, od.QtyBackordered, od.QtyOutstanding, od.GUIDOrderdetail, od.OrderNumber, od._SNValve, CASE WHEN Patindex('%-%', od._SNValve) > 0 THEN Substring(od._SNValve, 0, Patindex('%-%', od._SNValve)) 
           ELSE Substring(od._SNValve, 0, Len(od._SNValve) + 1) END AS First_SN, CASE WHEN Patindex('% %', od._SNValve) > 0 THEN Substring(od._SNValve, Patindex('% %', od._SNValve), Len(od._SNValve)) ELSE '' END AS Addnl_SN, CASE WHEN (Patindex('%-%', od._SNValve) > 0 OR
           Patindex('%,%', od._SNValve) > 0) THEN Substring(od._SNValve, Len(od._SNValve) -
               (SELECT min(NULLIF (xxx, 0))
              FROM   (VALUES (Patindex('%-%', Reverse(od._SNValve))), (Patindex('%,%', Reverse(od._SNValve))), (Patindex('% %', Reverse(od._SNValve)))) AS value(xxx)) + 2, Len(od._SNValve)) ELSE (od._SNValve) END AS Last_SN, o.EntryDate, od.OrderNumber + '-' + Cast(od.LineNumber AS VARCHAR(5)) AS OrderNumOB, Dateadd(hour, 5, 
od._ShipDate) AS _ShipDate2, od._ShipDate, od.Description, od.ShortDescription, od.Completed, od.LineCancelled, od.Price, od.PriceUnit, od.Amount, od.SpecialInstructions, od.InvoiceComment, od.Note, od.InventoryControlType, Cast(od.ActivityDate AS DATE) AS ActivityDate, Cast(od._InPlantDate AS DATE) AS _InPlantDate, od.Reference, 
Cast(o.OrderDate AS DATE) AS OrderDate, o.OrderStatus, Cast(o.StatusDate AS DATE) AS StatusDate, o.StatusChangedBy, Cast(o.RequestedShipDate AS DATE) AS RequestedShipDate, Datepart(year, od._ShipDate) AS RSD_Year, Datepart(month, od._ShipDate) AS RSD_Month, Datepart(day, od._ShipDate) AS RSD_Day, c.NAME, 
o.SchedTotalAmount, o.TotalAmount, o.GUIDOrder, o.WorkflowStatusAbbreviation, Row_number() OVER (ORDER BY o.OrderNumber ASC) AS RowNumberTmp, Datediff(month, EntryDate, Getdate()) AS EntryDateMonthsAgo, Datediff(minute, EntryDate, Getdate()) AS EntryDateMinutesAgo, 
CASE WHEN (od.QtyOrdered = od.QtyShipped AND od.QtyOrdered <> 0) THEN 'Shipped' ELSE CASE WHEN (od.QtyPicked <> 0) THEN 'Picked' ELSE CASE WHEN (od.QtyScheduled <> 0) THEN 'Scheduled' ELSE CASE WHEN (od.QtyInvoiced <> 0) THEN 'Invoiced' ELSE CASE WHEN (od.QtyOrdered <> 0) THEN 'Ordered' ELSE NULL 
END END END END END AS StatusCheck, CASE WHEN od.OrderNumber LIKE 'Kel%' THEN 2 WHEN od.OrderNumber LIKE 'RMA%' OR
od.OrderNumber LIKE 'DC%' OR
od.OrderNumber LIKE 'NW%' OR
od.OrderNumber LIKE 'BT%' OR
od.OrderNumber LIKE 'DC%' OR
od.OrderNumber LIKE 'HF%' OR
(Isnumeric(LEFT(od.ProductID, 1)) = 1 AND Substring(od.ProductID, 2, 1) = '-') THEN 5 WHEN Isnumeric(Substring(od.ProductID, 1, 6)) = 1 OR
(LEFT(od.ProductID, 1) = 'K' AND Isnumeric(Substring(od.ProductID, 2, 4)) = 1) THEN 4 ELSE 1 END AS OrderType, CASE WHEN od.ProductID LIKE 'JS%' OR
od.ProductID LIKE 'PC%' THEN 1 ELSE 0 END AS RailincProduct
FROM   [KT-RD-01\acctivate].acctivate$ktech.dbo.OrderDetail AS od INNER JOIN
           [KT-RD-01\acctivate].acctivate$ktech.dbo.Orders AS o ON od.GUIDOrder = o.GUIDOrder INNER JOIN
           [KT-RD-01\acctivate].acctivate$ktech.dbo.customer AS c ON o.GUIDCustomer = c.GUIDCustomer
WHERE ((o.OrderStatus = 'S') OR
           (o.OrderStatus = 'C') OR
           (o.OrderStatus = 'K') OR
           (o.OrderStatus = '')) AND ((od.QtyOrdered <> 0) AND od.ProductID <> 'shipping')
ORDER BY OrderType DESC, o.RequestedShipDate DESC, OrderNumOB ASC;

Answer 1

Instead of using design mode, try right click on the view, script as drop and create and just change the script and re-run it to create correct version of the view.

Answer 2

It was a different question entirely, but you anwered it by accident with:

EXEC sp_helptext 'YourView'