I never work with views so im a bit newb to how they work and the purpose of a view. Anyway, I'm getting an error on a sp. I think its related to my view. Pretty sure the view is broke and need to rebuild it. At least logically that sounds like the issue to me. I don't know and I dont know how to rebuild it if thats the case.
Errors:
- Msg 207, Level 16, State 1, Procedure OrderItems_vw, Line 16 [Batch Start Line 0] Invalid column name 'Brand'. Msg 207, Level 16, State 1, Procedure OrderItems_vw, Line 18 [Batch Start Line 0] Invalid column name 'Brand'. Msg 207, Level 16, State 1, Procedure OrderItems_vw, Line 3 [Batch Start Line 0] Invalid column name 'Brand'. Msg 4413, Level 16, State 1, Line 66 Could not use view or function 'OrderItemProduction_vw' because of binding errors. Msg 207, Level 16, State 1, Procedure OrderItems_vw, Line 16 [Batch Start Line 0] Invalid column name 'Brand'. Msg 207, Level 16, State 1, Procedure OrderItems_vw, Line 18 [Batch Start Line 0] Invalid column name 'Brand'. Msg 207, Level 16, State 1, Procedure OrderItems_vw, Line 3 [Batch Start Line 0] Invalid column name 'Brand'. Msg 4413, Level 16, State 1, Line 89 Could not use view or function 'OrderItemProduction_vw' because of binding errors. Msg 207, Level 16, State 1, Procedure OrderItems_vw, Line 16 [Batch Start Line 0] Invalid column name 'Brand'. Msg 207, Level 16, State 1, Procedure OrderItems_vw, Line 18 [Batch Start Line 0] Invalid column name 'Brand'. Msg 207, Level 16, State 1, Procedure OrderItems_vw, Line 3 [Batch Start Line 0] Invalid column name 'Brand'. Msg 4413, Level 16, State 1, Line 112 Could not use view or function 'OrderItemProduction_vw' because of binding errors. Msg 207, Level 16, State 1, Procedure OrderItems_vw, Line 16 [Batch Start Line 0] Invalid column name 'Brand'. Msg 207, Level 16, State 1, Procedure OrderItems_vw, Line 18 [Batch Start Line 0] Invalid column name 'Brand'. Msg 207, Level 16, State 1, Procedure OrderItems_vw, Line 3 [Batch Start Line 0] Invalid column name 'Brand'. Msg 4413, Level 16, State 1, Line 135 Could not use view or function 'OrderItemProduction_vw' because of binding errors. Msg 207, Level 16, State 1, Procedure OrderItems_vw, Line 16 [Batch Start Line 0] Invalid column name 'Brand'. Msg 207, Level 16, State 1, Procedure OrderItems_vw, Line 18 [Batch Start Line 0] Invalid column name 'Brand'. Msg 207, Level 16, State 1, Procedure OrderItems_vw, Line 3 [Batch Start Line 0] Invalid column name 'Brand'. Msg 4413, Level 16, State 1, Line 158 Could not use view or function 'OrderItemProduction_vw' because of binding errors.
SP:
DECLARE
-- Add the parameters for the stored procedure here
@StatusType varchar(32) = 'Order',
@Filter varchar(32) = 'Bolingbrook',
@Station varchar(32) = '',
@AttrValue varchar(128) = ''
--AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @Date datetime
SET @Date = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
IF @StatusType = 'Order'
BEGIN
SELECT @Filter AS Filter, NULL AS DueDate, StatusSort, StatusDescription, 0 AS Total
FROM [Status]
WHERE StatusType = @StatusType
UNION
SELECT @Filter AS Filter, NULL AS DueDate, 999.0000 AS StatusSort, 'Total' AS StatusDescription, 0 AS Total
UNION
SELECT @Filter AS Filter, s.DueDate, s.StatusSort, s.StatusDescription, COUNT(i.OrderStatus) AS Total
FROM (
SELECT DISTINCT @Filter AS Facility, o.DueDate, s.StatusSort, s.StatusDescription
FROM Orders o WITH (READUNCOMMITTED)
CROSS JOIN Status s
WHERE s.StatusType = @StatusType AND ((o.DueDate < @Date AND o.OrderStatus NOT IN ('Shipped','Cancelled')) OR o.DueDate >= @Date)) s
LEFT OUTER JOIN Orders i WITH (READUNCOMMITTED) ON i.OrderStatus = s.StatusDescription AND i.DueDate = s.DueDate AND ((i.DueDate < @Date AND i.OrderStatus NOT IN ('Shipped','Cancelled')) OR i.DueDate >= @Date)
GROUP BY s.DueDate, s.StatusSort, s.StatusDescription
UNION
SELECT @Filter AS Filter, DueDate, 999.0000 AS StatusSort, 'Total' AS StatusDescription, COUNT(*) AS Total
FROM Orders WITH (READUNCOMMITTED)
WHERE ((DueDate < @Date AND OrderStatus NOT IN ('Shipped','Cancelled')) OR DueDate >= @Date)
GROUP BY DueDate
ORDER BY Filter, DueDate, StatusSort
END
IF @StatusType = 'Production'
BEGIN
IF @Station = ''
BEGIN
SELECT @Filter AS Filter, NULL AS DueDate, StatusSort, StatusDescription, 0 AS Total
FROM [Status]
WHERE StatusType = @StatusType+'_'+@Filter
UNION
SELECT @Filter AS Filter, NULL AS DueDate, 999.0000 AS StatusSort, 'Total' AS StatusDescription, 0 AS Total
UNION
SELECT s.ProductGroup AS Filter, s.DueDate, s.StatusSort, s.StatusDescription, SUM(ISNULL(i.ProductionQuantity,0)) AS Total--, COUNT(i.ProductionStatus) AS Total
FROM (
SELECT DISTINCT o.ProductGroup, o.DueDate, s.StatusSort, s.StatusDescription
FROM OrderItemProduction_vw o WITH (READUNCOMMITTED)
CROSS JOIN [Status] s
WHERE s.StatusType = @StatusType+'_'+@Filter AND o.ProductGroup = @Filter AND ((o.DueDate < @Date AND o.OrderStatus NOT IN ('Shipped','Cancelled')) OR o.DueDate >= @Date)) s
LEFT OUTER JOIN OrderItemProduction_vw i WITH (READUNCOMMITTED) ON i.ProductGroup = @Filter AND i.ProductionStatus = s.StatusDescription AND i.DueDate = s.DueDate AND ((i.DueDate < @Date AND i.OrderStatus NOT IN ('Shipped','Cancelled')) OR (i.DueDate >= @Date AND OrderStatus NOT IN ('Cancelled')))
GROUP BY s.ProductGroup, s.DueDate, s.StatusSort, s.StatusDescription
UNION
SELECT ProductGroup AS Filter, DueDate, 999.0000 AS StatusSort, 'Total' AS StatusDescription, COUNT(*) AS Total
FROM OrderItemProduction_vw WITH (READUNCOMMITTED)
WHERE ProductGroup = @Filter AND ((DueDate < @Date AND OrderStatus NOT IN ('Shipped','Cancelled')) OR (DueDate >= @Date AND OrderStatus NOT IN ('Cancelled')))
GROUP BY ProductGroup, DueDate
ORDER BY Filter, DueDate, StatusSort
END
ELSE IF @Station = 'UV'
BEGIN
SELECT @Filter+' '+@AttrValue AS Filter, NULL AS DueDate, StatusSort, StatusDescription, 0 AS Total
FROM [Status]
WHERE StatusType = @StatusType+'_'+@Filter
UNION
SELECT @Filter+' '+@AttrValue AS Filter, NULL AS DueDate, 999.0000 AS StatusSort, 'Total' AS StatusDescription, 0 AS Total
UNION
SELECT s.ProductGroup AS Filter, s.DueDate, s.StatusSort, s.StatusDescription, SUM(ISNULL(i.ProductionQuantity,0)) AS Total--, COUNT(i.ProductionStatus) AS Total
FROM (
SELECT DISTINCT o.ProductGroup+' '+o.UV AS ProductGroup, o.DueDate, s.StatusSort, s.StatusDescription
FROM OrderItemProduction_vw o WITH (READUNCOMMITTED)
CROSS JOIN [Status] s
WHERE s.StatusType = @StatusType+'_'+@Filter AND o.ProductGroup = @Filter AND o.UV = @AttrValue AND ((o.DueDate < @Date AND o.OrderStatus NOT IN ('Shipped','Cancelled')) OR o.DueDate >= @Date)) s
LEFT OUTER JOIN OrderItemProduction_vw i WITH (READUNCOMMITTED) ON i.ProductGroup = @Filter AND i.ProductionStatus = s.StatusDescription AND i.DueDate = s.DueDate AND i.UV = @AttrValue AND ((i.DueDate < @Date AND i.OrderStatus NOT IN ('Shipped','Cancelled')) OR (i.DueDate >= @Date AND OrderStatus NOT IN ('Cancelled')))
GROUP BY s.ProductGroup, s.DueDate, s.StatusSort, s.StatusDescription
UNION
SELECT ProductGroup+' '+UV AS Filter, DueDate, 999.0000 AS StatusSort, 'Total' AS StatusDescription, COUNT(*) AS Total
FROM OrderItemProduction_vw WITH (READUNCOMMITTED)
WHERE ProductGroup = @Filter AND UV = @AttrValue AND ((DueDate < @Date AND OrderStatus NOT IN ('Shipped','Cancelled')) OR (DueDate >= @Date AND OrderStatus NOT IN ('Cancelled')))
GROUP BY ProductGroup+' '+UV, DueDate
ORDER BY Filter, DueDate, StatusSort
END
ELSE IF @Station = 'Diecut'
BEGIN
SELECT @Filter+' '+@AttrValue AS Filter, NULL AS DueDate, StatusSort, StatusDescription, 0 AS Total
FROM [Status]
WHERE StatusType = @StatusType+'_'+@Filter
UNION
SELECT @Filter+' '+@AttrValue AS Filter, NULL AS DueDate, 999.0000 AS StatusSort, 'Total' AS StatusDescription, 0 AS Total
UNION
SELECT s.ProductGroup AS Filter, s.DueDate, s.StatusSort, s.StatusDescription, SUM(ISNULL(i.ProductionQuantity,0)) AS Total--, COUNT(i.ProductionStatus) AS Total
FROM (
SELECT DISTINCT o.ProductGroup+' '+o.PaperTrim AS ProductGroup, o.DueDate, s.StatusSort, s.StatusDescription
FROM OrderItemProduction_vw o WITH (READUNCOMMITTED)
CROSS JOIN [Status] s
WHERE s.StatusType = @StatusType+'_'+@Filter AND o.ProductGroup = @Filter AND o.PaperTrim = @AttrValue AND ((o.DueDate < @Date AND o.OrderStatus NOT IN ('Shipped','Cancelled')) OR o.DueDate >= @Date)) s
LEFT OUTER JOIN OrderItemProduction_vw i WITH (READUNCOMMITTED) ON i.ProductGroup = @Filter AND i.ProductionStatus = s.StatusDescription AND i.DueDate = s.DueDate AND i.PaperTrim = @AttrValue AND ((i.DueDate < @Date AND i.OrderStatus NOT IN ('Shipped','Cancelled')) OR (i.DueDate >= @Date AND OrderStatus NOT IN ('Cancelled')))
GROUP BY s.ProductGroup, s.DueDate, s.StatusSort, s.StatusDescription
UNION
SELECT ProductGroup+' '+PaperTrim AS Filter, DueDate, 999.0000 AS StatusSort, 'Total' AS StatusDescription, COUNT(*) AS Total
FROM OrderItemProduction_vw WITH (READUNCOMMITTED)
WHERE ProductGroup = @Filter AND PaperTrim = @AttrValue AND ((DueDate < @Date AND OrderStatus NOT IN ('Shipped','Cancelled')) OR (DueDate >= @Date AND OrderStatus NOT IN ('Cancelled')))
GROUP BY ProductGroup+' '+PaperTrim, DueDate
ORDER BY Filter, DueDate, StatusSort
END
ELSE IF @Station = 'Print'
BEGIN
SELECT @Filter+' '+@AttrValue AS Filter, NULL AS DueDate, StatusSort, StatusDescription, 0 AS Total
FROM [Status]
WHERE StatusType = @StatusType+'_'+@Filter
UNION
SELECT @Filter+' '+@AttrValue AS Filter, NULL AS DueDate, 999.0000 AS StatusSort, 'Total' AS StatusDescription, 0 AS Total
UNION
SELECT s.ProductGroup AS Filter, s.DueDate, s.StatusSort, s.StatusDescription, SUM(ISNULL(i.ProductionQuantity,0)) AS Total--, COUNT(i.ProductionStatus) AS Total
FROM (
SELECT DISTINCT o.ProductGroup+' '+o.Substrate AS ProductGroup, o.DueDate, s.StatusSort, s.StatusDescription
FROM OrderItemProduction_vw o WITH (READUNCOMMITTED)
CROSS JOIN [Status] s
WHERE s.StatusType = @StatusType+'_'+@Filter AND o.ProductGroup = @Filter AND o.Substrate = @AttrValue AND ((o.DueDate < @Date AND o.OrderStatus NOT IN ('Shipped','Cancelled')) OR o.DueDate >= @Date)) s
LEFT OUTER JOIN OrderItemProduction_vw i WITH (READUNCOMMITTED) ON i.ProductGroup = @Filter AND i.ProductionStatus = s.StatusDescription AND i.DueDate = s.DueDate AND i.Substrate = @AttrValue AND ((i.DueDate < @Date AND i.OrderStatus NOT IN ('Shipped','Cancelled')) OR (i.DueDate >= @Date AND OrderStatus NOT IN ('Cancelled')))
GROUP BY s.ProductGroup, s.DueDate, s.StatusSort, s.StatusDescription
UNION
SELECT ProductGroup+' '+Substrate AS Filter, DueDate, 999.0000 AS StatusSort, 'Total' AS StatusDescription, COUNT(*) AS Total
FROM OrderItemProduction_vw WITH (READUNCOMMITTED)
WHERE ProductGroup = @Filter AND Substrate = @AttrValue AND ((DueDate < @Date AND OrderStatus NOT IN ('Shipped','Cancelled')) OR (DueDate >= @Date AND OrderStatus NOT IN ('Cancelled')))
GROUP BY ProductGroup+' '+Substrate, DueDate
ORDER BY Filter, DueDate, StatusSort
END
ELSE IF @Station = 'Press'
BEGIN
SELECT @Filter+' '+@AttrValue AS Filter, NULL AS DueDate, StatusSort, StatusDescription, 0 AS Total
FROM [Status]
WHERE StatusType = @StatusType+'_'+@Filter
UNION
SELECT @Filter+' '+@AttrValue AS Filter, NULL AS DueDate, 999.0000 AS StatusSort, 'Total' AS StatusDescription, 0 AS Total
UNION
SELECT s.ProductGroup AS Filter, s.DueDate, s.StatusSort, s.StatusDescription, SUM(ISNULL(i.ProductionQuantity,0)) AS Total--, COUNT(i.ProductionStatus) AS Total
FROM (
SELECT DISTINCT o.ProductGroup+' '+o.Press AS ProductGroup, o.DueDate, s.StatusSort, s.StatusDescription
FROM OrderItemProduction_vw o WITH (READUNCOMMITTED)
CROSS JOIN [Status] s
WHERE s.StatusType = @StatusType+'_'+@Filter AND o.ProductGroup = @Filter AND o.Press = @AttrValue AND ((o.DueDate < @Date AND o.OrderStatus NOT IN ('Shipped','Cancelled')) OR o.DueDate >= @Date)) s
LEFT OUTER JOIN OrderItemProduction_vw i WITH (READUNCOMMITTED) ON i.ProductGroup = @Filter AND i.ProductionStatus = s.StatusDescription AND i.DueDate = s.DueDate AND i.Press = @AttrValue AND ((i.DueDate < @Date AND i.OrderStatus NOT IN ('Shipped','Cancelled')) OR (i.DueDate >= @Date AND OrderStatus NOT IN ('Cancelled')))
GROUP BY s.ProductGroup, s.DueDate, s.StatusSort, s.StatusDescription
UNION
SELECT ProductGroup+' '+Press AS Filter, DueDate, 999.0000 AS StatusSort, 'Total' AS StatusDescription, COUNT(*) AS Total
FROM OrderItemProduction_vw WITH (READUNCOMMITTED)
WHERE ProductGroup = @Filter AND Press = @AttrValue AND ((DueDate < @Date AND OrderStatus NOT IN ('Shipped','Cancelled')) OR (DueDate >= @Date AND OrderStatus NOT IN ('Cancelled')))
GROUP BY ProductGroup+' '+Press, DueDate
ORDER BY Filter, DueDate, StatusSort
END
END
IF @StatusType = 'ShipMethod'
BEGIN
SELECT @Filter AS Filter, NULL AS DueDate, StatusSort, StatusDescription, 0 AS Total
FROM [Status]
WHERE StatusType = @StatusType+'_'+@Filter
UNION
SELECT @Filter AS Filter, NULL AS DueDate, 999.0000 AS StatusSort, 'Total' AS StatusDescription, 0 AS Total
UNION
SELECT @Filter AS Filter, s.DueDate, s.StatusSort, s.StatusDescription, COUNT(i.OrderStatus) AS Total
FROM (
SELECT DISTINCT @Filter AS Facility, o.DueDate, s.StatusSort, s.StatusDescription
FROM Orders o WITH (READUNCOMMITTED)
CROSS JOIN [Status] s
WHERE s.StatusType = @StatusType AND ((o.DueDate < @Date AND o.OrderStatus NOT IN ('Shipped','Cancelled')) OR o.DueDate >= @Date)) s
LEFT OUTER JOIN Orders i WITH (READUNCOMMITTED) ON i.shipMethod = s.StatusDescription AND i.DueDate = s.DueDate AND ((i.DueDate < @Date AND i.OrderStatus NOT IN ('Shipped','Cancelled')) OR i.DueDate >= @Date)
GROUP BY s.DueDate, s.StatusSort, s.StatusDescription
UNION
SELECT @Filter AS Filter, DueDate, 999.0000 AS StatusSort, 'Total' AS StatusDescription, COUNT(*) AS Total
FROM Orders WITH (READUNCOMMITTED)
WHERE ((DueDate < @Date AND OrderStatus NOT IN ('Shipped','Cancelled')) OR DueDate >= @Date)
GROUP BY DueDate
ORDER BY Filter, DueDate, StatusSort
END
IF @StatusType = 'Packaging'
BEGIN
SELECT @Filter AS Filter, NULL AS DueDate, s.StatusSort, s.StatusDescription, 0 AS Total
FROM [Status] s
--INNER JOIN Packaging p ON p.Facility = @Filter AND p.PackagingCode = s.StatusDescription
WHERE StatusType = @StatusType+'_'+@Filter
UNION
SELECT @Filter AS Filter, NULL AS DueDate, 999.0000 AS StatusSort, 'Total' AS StatusDescription, 0 AS Total
UNION
SELECT @Filter AS Filter, s.DueDate, s.StatusSort, s.StatusDescription, COUNT(i.Packaging) AS Total
FROM (
SELECT DISTINCT @Filter AS Facility, o.DueDate, s.StatusSort, s.StatusDescription
FROM Orders o WITH (READUNCOMMITTED)
CROSS JOIN [Status] s
WHERE s.StatusType = @StatusType AND ((o.DueDate < @Date AND o.OrderStatus NOT IN ('Shipped','Cancelled')) OR o.DueDate >= @Date)) s
INNER JOIN Packaging p ON p.PackagingCode = s.StatusDescription
LEFT OUTER JOIN Orders i WITH (READUNCOMMITTED) ON i.Packaging = s.StatusDescription AND i.DueDate = s.DueDate AND ((i.DueDate < @Date AND i.OrderStatus NOT IN ('Shipped','Cancelled')) OR i.DueDate >= @Date)
GROUP BY s.DueDate, s.StatusSort, s.StatusDescription
UNION
SELECT @Filter AS Filter, i.DueDate, 999.0000 AS StatusSort, 'Total' AS StatusDescription, COUNT(*) AS Total
FROM Orders i WITH (READUNCOMMITTED)
INNER JOIN Packaging p ON p.PackagingCode = i.Packaging
WHERE ((i.DueDate < @Date AND i.OrderStatus NOT IN ('Shipped','Cancelled')) OR i.DueDate >= @Date)
GROUP BY i.DueDate
ORDER BY Filter, DueDate, StatusSort
END
END
Here is the view its self:
SELECT TOP (1000) [OrderId]
,[version]
,[OrderNo]
,[OriginalOrderNo]
,[RedoType]
,[CustomerNo]
,[OrderDate]
,[OrderDateString]
,[RequestedDueDate]
,[RequestedDueDateString]
,[Brand]
,[RushService]
,[ShipMethod]
,[ShippingAttention]
,[ShippingAddressee]
,[ShippingAddressLine1]
,[ShippingAddressLine2]
,[ShippingCity]
,[ShippingState]
,[ShippingZip]
,[ShippingCountry]
,[ShippingPhone]
,[OrderNote]
,[ItemNote]
,[DateOrdered]
,[DueDate]
,[Items]
,[TotalItems]
,[Packaging]
,[RandomNumber]
,[ShipDate]
,[ShippingNotes]
,[DateShipped]
,[Carrier]
,[ShipService]
,[Tracking]
,[Units]
,[Weight]
,[RejectCode]
,[RejectReason]
,[OrderReceived]
,[OrderStatus]
,[OrderStatusDate]
,[TransmissionDate]
,[TransmissionResponse]
,[Staging]
,[ItemId]
,[LineNo]
,[ItemNo]
,[SKU]
,[Description]
,[PaperType]
,[PaperTrim]
,[Pages]
,[Quantity]
,[unitPrice]
,[ProductType]
,[ItemNum]
,[ItemStatus]
,[ItemStatusDate]
,[Orientation]
,[Substrate]
,[SubstrateWeight]
,[SubstrateColor]
,[Inks]
,[UV]
,[SKUDescription]
,[ProductGroup]
,[Facility]
,[ProductName]
,[Size]
,[Cost]
,[Price]
,[Retail]
,[UnitWeight]
,[ProductCategory]
,[ProductSKUCode]
,[Permutations]
,[Press]
,[TicketTemplate]
,[isActive]
,[SubItemId]
,[SubLineNo]
,[SubItemNo]
,[SubSKU]
,[SubDescription]
,[SubPaperType]
,[SubPaperTrim]
,[SubPages]
,[SubQuantity]
,[SubunitPrice]
,[SubProductType]
,[SubItemNum]
,[SubItemStatus]
,[SubItemStatusDate]
,[SubOrientation]
,[SubSubstrate]
,[SubSubstrateWeight]
,[SubSubstrateColor]
,[SubInks]
,[SubUV]
,[SubSKUDescription]
,[SubProductGroup]
,[SubFacility]
,[SubProductName]
,[SubSize]
,[SubCost]
,[SubPrice]
,[SubRetail]
,[SubUnitWeight]
,[SubProductCategory]
,[SubProductSKUCode]
,[SubPermutations]
,[SubPress]
,[SubTicketTemplate]
,[SubisActive]
FROM [Collage].[dbo].[OrderItems_vw]
Im getting errors when I try to query the view. So I think this is linked to the root cause:
Msg 207, Level 16, State 1, Procedure OrderItems_vw, Line 16 [Batch Start Line 0] Invalid column name 'Brand'. Msg 207, Level 16, State 1, Procedure OrderItems_vw, Line 18 [Batch Start Line 0] Invalid column name 'Brand'. Msg 207, Level 16, State 1, Procedure OrderItems_vw, Line 3 [Batch Start Line 0] Invalid column name 'Brand'. Msg 4413, Level 16, State 1, Line 118 Could not use view or function 'Collage.dbo.OrderItems_vw' because of binding errors.
