March 22, 2016 at 10:48 am
I'm trying to create a stored proc using both a function and a view. I'm getting an "invalid column name" error on 2 aliases I'm trying to create, on ReorderLevel and StockingLevel. Neither of which are involved in the function. Honestly, I don't see what's wrong with this, it looks fine to me. Here's the text of the new SP, with the comments removed:
CREATE PROCEDURE dbo.spItemsIssuedToLocBetwDatesItemTypeWithParLevels
@DrugOrSupply nvarchar(10),
@BeginDate datetime,
@EndDate datetime,
@NumDaysBetwDates int,
@NumDaysToStock int
AS
BEGIN
SET NOCOUNT ON;
SELECT fcnItems.DrugOrSupply, fcnItems.InventoryActionID,
fcnItems.ItemPackageID, fcnItems.OldItemNum,
fcnItems.ItemDescr, vw.InventoryIn,
vw.PharmacyOut,
([QtyOnHand]/(@NumDaysBetwDates/@NumDaysToStock)+1) StockingLevel,
((@NumDaysBetwDates/@NumDaysToStock)/2+1) ReorderLevel,
vw.QtyOnHand, IIf([QtyOnHand]<[ReorderLevel],[StockingLevel]-[QtyOnHand],0) NumToOrder
FROM dbo.fnItemsIssuedToLocBetwDatesNeedItemType(@DrugOrSupply, @BeginDate, @EndDate) fcnItems
INNER JOIN vwItemQtysOnHandAllItems_SQLViews vw
ON fcnItems.ItemPackageID = vw.ItemPackageID
END
GO
Kindest Regards, Rod Connect with me on LinkedIn.
March 22, 2016 at 10:54 am
What is the exact error message, please? Could the mystery columns be in the view definition... I assume that vwItemQtysOnHandAllItems_SQLViews is a view?
John
March 22, 2016 at 10:58 am
Unfortunately referencing a column alias in the same select statement is not supported in SQL Server the way it is done in MS Access. So you probably will have to do something like this
CREATE PROCEDURE dbo.spItemsIssuedToLocBetwDatesItemTypeWithParLevels
@DrugOrSupply nvarchar(10),
@BeginDate datetime,
@EndDate datetime,
@NumDaysBetwDates int,
@NumDaysToStock int
AS
BEGIN
SET NOCOUNT ON;
SELECT *,IIf([QtyOnHand]<[ReorderLevel],[StockingLevel]-[QtyOnHand],0) NumToOrder FROM(
SELECT fcnItems.DrugOrSupply, fcnItems.InventoryActionID,
fcnItems.ItemPackageID, fcnItems.OldItemNum,
fcnItems.ItemDescr, vw.InventoryIn,
vw.PharmacyOut,
([QtyOnHand]/(@NumDaysBetwDates/@NumDaysToStock)+1) StockingLevel,
((@NumDaysBetwDates/@NumDaysToStock)/2+1) ReorderLevel,
vw.QtyOnHand
FROM dbo.fnItemsIssuedToLocBetwDatesNeedItemType(@DrugOrSupply, @BeginDate, @EndDate) fcnItems
INNER JOIN vwItemQtysOnHandAllItems_SQLViews vw
ON fcnItems.ItemPackageID = vw.ItemPackageID
)T
END
GO
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 22, 2016 at 11:12 am
John Mitchell-245523 (3/22/2016)
What is the exact error message, please? Could the mystery columns be in the view definition... I assume that vwItemQtysOnHandAllItems_SQLViews is a view?John
Here's the exact error messages:
Msg 207, Level 16, State 1, Procedure spItemsIssuedToLocBetwDatesItemTypeWithParLevels, Line 29
Invalid column name 'ReorderLevel'.
Msg 207, Level 16, State 1, Procedure spItemsIssuedToLocBetwDatesItemTypeWithParLevels, Line 29
Invalid column name 'StockingLevel'.
Kindest Regards, Rod Connect with me on LinkedIn.
March 22, 2016 at 11:20 am
Thank you Sachin, that worked. I've changed the SELECT to look like this:
SELECT fcnItems.DrugOrSupply, fcnItems.InventoryActionID,
fcnItems.ItemPackageID, fcnItems.OldItemNum,
fcnItems.ItemDescr, vw.InventoryIn,
vw.PharmacyOut,
(QtyOnHand/(@NumDaysBetwDates/@NumDaysToStock)+1) StockingLevel,
((@NumDaysBetwDates/@NumDaysToStock)/2+1) ReorderLevel,
vw.QtyOnHand,
IIf(QtyOnHand<((@NumDaysBetwDates/@NumDaysToStock)/2+1),(QtyOnHand/(@NumDaysBetwDates/@NumDaysToStock)+1)-QtyOnHand,0) NumToOrder
FROM dbo.fnItemsIssuedToLocBetwDatesNeedItemType(@DrugOrSupply, @BeginDate, @EndDate) fcnItems
INNER JOIN vwItemQtysOnHandAllItems_SQLViews vw
ON fcnItems.ItemPackageID = vw.ItemPackageID
A bit verbose, but whatever floats SQL Server's boat.
Kindest Regards, Rod Connect with me on LinkedIn.
March 22, 2016 at 11:41 am
Alternatively, you could CROSS APPLY the calculations; those aliases could then be used in the column list.
The FROM clause would become something like this:
FROM dbo.fnItemsIssuedToLocBetwDatesNeedItemType(@DrugOrSupply, @BeginDate, @EndDate) fcnItems
INNER JOIN vwItemQtysOnHandAllItems_SQLViews vw ON fcnItems.ItemPackageID = vw.ItemPackageID
CROSS APPLY (SELECT (QtyOnHand/(@NumDaysBetwDates/@NumDaysToStock)+1) AS StockingLevel,
((@NumDaysBetwDates/@NumDaysToStock)/2+1) AS ReorderLevel) derived_columns
StockingLevel and ReorderLevel could then be used in the column list of the SELECT. If you have to reference a calculation multiple times, this is often easier to read.
Cheers!
March 22, 2016 at 3:47 pm
First, an advice, when you use funcions you force SQLServer to run your query in only ONE thread.
CASE is cleaner to read than IIF and supported in more SQLServer versions
Alias = expression is more readable too
CREATE PROCEDURE dbo.spItemsIssuedToLocBetwDatesItemTypeWithParLevels
@DrugOrSupply nvarchar(10),
@BeginDate datetime,
@EndDate datetime,
@NumDaysBetwDates int,
@NumDaysToStock int
AS
BEGIN
SET NOCOUNT ON;
SELECT fcnItems.DrugOrSupply
,fcnItems.InventoryActionID
,fcnItems.ItemPackageID
,fcnItems.OldItemNum
,fcnItems.ItemDescr
,vw.InventoryIn
,vw.PharmacyOut
,StockingLevel = (QtyOnHand/(@NumDaysBetwDates/@NumDaysToStock)+1)
,ReorderLevel = ((@NumDaysBetwDates/@NumDaysToStock)/2+1)
,vw.QtyOnHand
,NumToOrder = CASE
WHEN QtyOnHand < ((@NumDaysBetwDates/@NumDaysToStock)/2 + 1 )
THEN (QtyOnHand/(@NumDaysBetwDates/@NumDaysToStock) + 1 ) - QtyOnHand
ELSE 0
End
FROM dbo.fnItemsIssuedToLocBetwDatesNeedItemType(@DrugOrSupply, @BeginDate, @EndDate) fcnItems
INNER JOIN vwItemQtysOnHandAllItems_SQLViews vw
ON fcnItems.ItemPackageID = vw.ItemPackageID
END
GO
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply