Jeff Geiselman
asked on
Incorrect syntax near the keyword 'DECLARE'
I am writing a stored procedure amd I need to get data from a couple of tables and then add it to a temp table. After this is done, i then want to get more data from some other tables and then append this to the same temp table that i created before and then use this temp table as my dataset. I can get the data separately from the different tables but when I try to declare a cursor to take the data from one temporary table and insert it into another temporary table so i can get the data all in one table, i get an error:
Msg 156, Level 15, State 1, Procedure BFForecastOffsiteReplenish ment, Line 107
Incorrect syntax near the keyword 'DECLARE'.
I have looked through the code and i don't see anything that is wrong. Does anyone know what i should be looking for? Maybe there is a different way to go about this. If you have a suggestion for that please let me know. I have listed my code below.
Msg 156, Level 15, State 1, Procedure BFForecastOffsiteReplenish
Incorrect syntax near the keyword 'DECLARE'.
I have looked through the code and i don't see anything that is wrong. Does anyone know what i should be looking for? Maybe there is a different way to go about this. If you have a suggestion for that please let me know. I have listed my code below.
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'BFForecastOffsiteReplenishment')
BEGIN
DROP Procedure BFForecastOffsiteReplenishment
END
GO
CREATE Procedure BFForecastOffsiteReplenishment
AS
SET NOCOUNT ON
DECLARE
@WorkGrp varchar(3),
@WorkArea varchar(4),
@Area varchar(10),
@ProNum varchar(25),
@Location varchar(10),
@Quantity int,
@Price numeric,
@ExtCost numeric
DECLARE @InventoryTable TABLE
(
WorkGrp varchar(3),
WorkArea varchar(4),
Area varchar(10),
ProNum varchar(25),
Location varchar(10),
Quantity int,
Price numeric,
ExtCost numeric
)
DECLARE @ReserveTable TABLE
(
WorkGrp varchar(3),
WorkArea varchar(4),
Area varchar(10),
ProNum varchar(25),
Location varchar(10),
Quantity int,
Price numeric,
ExtCost numeric
)
DECLARE @ActiveTable TABLE
(
WorkGrp varchar(3),
WorkArea varchar(4),
Area varchar(10),
ProNum varchar(25),
Location varchar(10),
Quantity int,
Price numeric,
ExtCost numeric
)
SELECT
LH.WORK_GRP As WorkGrp,
LH.WORK_AREA As WorkAera,
Case
When LH.WORK_GRP = 'EAA' Or LH.WORK_GRP = 'CPC' Or LH.WORK_GRP = 'CPN' Then 'On-Site'
When LH.WORK_GRP = 'OFF' Then 'Off-Site'
When LH.WORK_GRP = 'RSV' And (LH.WORK_AREA = 'DR01' Or LH.WORK_AREA = 'DR02' Or
LH.WORK_AREA = 'DR03' Or LH.WORK_AREA = 'DR04' Or LH.WORK_AREA = 'DR05' Or
LH.WORK_AREA = 'DR06' Or LH.WORK_AREA = 'DR07' Or LH.WORK_AREA = 'DR14' Or
LH.WORK_AREA = 'DR15' Or LH.WORK_AREA = 'DR16' Or LH.WORK_AREA = 'DR17' Or
LH.WORK_AREA = 'DR18' Or LH.WORK_AREA = 'DR19' Or LH.WORK_AREA = 'DRE1' Or
LH.WORK_AREA = 'DRE2' Or LH.WORK_AREA = 'QA01' Or LH.WORK_AREA = 'R01A' Or
LH.WORK_AREA = 'R01B' Or LH.WORK_AREA = 'R03A' Or LH.WORK_AREA = 'R04A' Or
LH.WORK_AREA = 'R06A' Or LH.WORK_AREA = 'R07A' Or LH.WORK_AREA = 'R07B' Or
LH.WORK_AREA = 'R08A' Or LH.WORK_AREA = 'R09A' Or LH.WORK_AREA = 'R10A' Or
LH.WORK_AREA = 'R11A' Or LH.WORK_AREA = 'R12A' Or LH.WORK_AREA = 'R13A' Or
LH.WORK_AREA = 'R14A' Or LH.WORK_AREA = 'R14B' Or LH.WORK_AREA = 'R16A' Or
LH.WORK_AREA = 'R17A' Or LH.WORK_AREA = 'R18A' Or LH.WORK_AREA = 'R19A' Or
LH.WORK_AREA = 'R61A' Or LH.WORK_AREA = 'R64A' Or LH.WORK_AREA = 'R65A' Or
LH.WORK_AREA = 'R66A' Or LH.WORK_AREA = 'R67A' Or LH.WORK_AREA = 'R68A' Or
LH.WORK_AREA = 'R69A' Or LH.WORK_AREA = 'R70A' Or LH.WORK_AREA = 'R71A' Or
LH.WORK_AREA = 'R72A' Or LH.WORK_AREA = 'R73A' Or LH.WORK_AREA = 'R74A' Or
LH.WORK_AREA = 'R78A' Or LH.WORK_AREA = 'R80A' Or LH.WORK_AREA = 'R81A' Or
LH.WORK_AREA = 'R82A' Or LH.WORK_AREA = 'R83A' Or LH.WORK_AREA = 'R84A' Or
LH.WORK_AREA = 'R85A' Or LH.WORK_AREA = 'R86A' Or LH.WORK_AREA = 'R88A') Then 'On-Site'
When LH.WORK_GRP = 'RSV' And LH.WORK_AREA = 'DR30' Then 'Off-Site'
End As Area,
IM.SKU_DESC As Part_Number,
LH.DSP_LOCN As Location,
PLD.ACTL_INVN_QTY As Quantity,
IM.UNIT_PRICE As Price,
PLD.ACTL_INVN_QTY * IM.UNIT_PRICE AS Extended_Cost
FROM
ITEM_MASTER As IM INNER JOIN
PICK_LOCN_DTL As PLD ON IM.SKU_ID = PLD.SKU_ID INNER JOIN
PICK_LOCN_HDR As PLH ON PLD.LOCN_ID = PLH.LOCN_ID INNER JOIN
LOCN_HDR As LH ON PLH.LOCN_ID = LH.LOCN_ID
ORDER BY
Area, Location
INSERT INTO @ActiveTable
( WorkGrp,
WorkArea,
Area,
ProNum,
Location,
Quantity,
Price,
ExtCost)
DECLARE ActiveInvCursor CURSOR FAST_FORWARD FOR
SELECT
WorkGrp,
WorkArea,
Area,
ProNum,
Location,
Quantity,
Price,
ExtCost
FROM
@ActiveTable
Open ActiveInvCursor
Fetch Next From ActiveInvCursor Into
@WorkGrp,
@WorkArea,
@Area,
@ProNum,
@Location,
@Quantity,
@Price,
@ExtCost
While @@FETCH_STATUS = 0
Begin
Insert Into @InventoryTable
( WorkGrp,
WorkArea,
Area,
Pronum,
Location,
Quantity,
Price,
ExtCost)
Values
( @WorkGrp,
@WorkArea,
@Area,
@ProNum,
@Location,
@Quantity,
@Price,
@ExtCost)
Insert Into @InventoryTable
( WorkGrp,
WorkArea,
Area,
Pronum,
Location,
Quantity,
Price,
ExtCost)
Values
( @WorkGrp,
@WorkArea,
@Area,
@ProNum,
@Location,
@Quantity,
@Price,
@ExtCost)
Fetch Next From ActiveInvCursor Into
@WorkGrp,
@WorkArea,
@Area,
@ProNum,
@Location,
@Quantity,
@Price,
@ExtCost
End
CLOSE ActiveInvCursor
DEALLOCATE ActiveInvCursor
SELECT
LH.WORK_GRP As WorkGrp,
LH.WORK_AREA As WorkAera,
Case
When LH.WORK_GRP = 'EAA' Or LH.WORK_GRP = 'CPC' Or LH.WORK_GRP = 'CPN' Then 'On-Site'
When LH.WORK_GRP = 'OFF' Then 'Off-Site'
When LH.WORK_GRP = 'RSV' And (LH.WORK_AREA = 'DR01' Or LH.WORK_AREA = 'DR02' Or
LH.WORK_AREA = 'DR03' Or LH.WORK_AREA = 'DR04' Or LH.WORK_AREA = 'DR05' Or
LH.WORK_AREA = 'DR06' Or LH.WORK_AREA = 'DR07' Or LH.WORK_AREA = 'DR14' Or
LH.WORK_AREA = 'DR15' Or LH.WORK_AREA = 'DR16' Or LH.WORK_AREA = 'DR17' Or
LH.WORK_AREA = 'DR18' Or LH.WORK_AREA = 'DR19' Or LH.WORK_AREA = 'DRE1' Or
LH.WORK_AREA = 'DRE2' Or LH.WORK_AREA = 'QA01' Or LH.WORK_AREA = 'R01A' Or
LH.WORK_AREA = 'R01B' Or LH.WORK_AREA = 'R03A' Or LH.WORK_AREA = 'R04A' Or
LH.WORK_AREA = 'R06A' Or LH.WORK_AREA = 'R07A' Or LH.WORK_AREA = 'R07B' Or
LH.WORK_AREA = 'R08A' Or LH.WORK_AREA = 'R09A' Or LH.WORK_AREA = 'R10A' Or
LH.WORK_AREA = 'R11A' Or LH.WORK_AREA = 'R12A' Or LH.WORK_AREA = 'R13A' Or
LH.WORK_AREA = 'R14A' Or LH.WORK_AREA = 'R14B' Or LH.WORK_AREA = 'R16A' Or
LH.WORK_AREA = 'R17A' Or LH.WORK_AREA = 'R18A' Or LH.WORK_AREA = 'R19A' Or
LH.WORK_AREA = 'R61A' Or LH.WORK_AREA = 'R64A' Or LH.WORK_AREA = 'R65A' Or
LH.WORK_AREA = 'R66A' Or LH.WORK_AREA = 'R67A' Or LH.WORK_AREA = 'R68A' Or
LH.WORK_AREA = 'R69A' Or LH.WORK_AREA = 'R70A' Or LH.WORK_AREA = 'R71A' Or
LH.WORK_AREA = 'R72A' Or LH.WORK_AREA = 'R73A' Or LH.WORK_AREA = 'R74A' Or
LH.WORK_AREA = 'R78A' Or LH.WORK_AREA = 'R80A' Or LH.WORK_AREA = 'R81A' Or
LH.WORK_AREA = 'R82A' Or LH.WORK_AREA = 'R83A' Or LH.WORK_AREA = 'R84A' Or
LH.WORK_AREA = 'R85A' Or LH.WORK_AREA = 'R86A' Or LH.WORK_AREA = 'R88A') Then 'On-Site'
When LH.WORK_GRP = 'RSV' And LH.WORK_AREA = 'DR30' Then 'Off-Site'
End As Area,
IM.SKU_DESC As ProNum,
LH.DSP_LOCN As Location,
CD.ACTL_QTY As Quantity,
IM.UNIT_PRICE As Price,
CD.ACTL_QTY * IM.UNIT_PRICE AS ExtCost
FROM
ITEM_MASTER IM INNER JOIN
CASE_DTL CD ON IM.SKU_ID = CD.SKU_ID INNER JOIN
CASE_HDR CH ON CD.CASE_NBR = CH.CASE_NBR INNER JOIN
LOCN_HDR LH ON CH.LOCN_ID = LH.LOCN_ID AND LH.WHSE = CH.WHSE INNER JOIN
RESV_LOCN_HDR RLH ON LH.LOCN_ID = RLH.LOCN_ID
ORDER BY
Area, Location
INSERT INTO @ReserveTable
( WorkGrp,
WorkArea,
Area,
Pronum,
Location,
Quantity,
Price,
ExtCost)
DECLARE RsvInventoryCursor CURSOR FAST_FORWARD FOR
SELECT
WorkGrp,
WorkArea,
Area,
ProNum,
Location,
Quantity,
Price,
ExtCost
FROM
@ReserveTable ReserveInventory
Open RsvInventoryCursor
Fetch Next From RsvInventoryCursor Into
@WorkGrp,
@WorkArea,
@Area,
@ProNum,
@Location,
@Quantity,
@Price,
@ExtCost
While @@FETCH_STATUS = 0
Begin
Insert Into @InventoryTable
( WorkGrp,
WorkArea,
Area,
Pronum,
Location,
Quantity,
Price,
ExtCost)
Values
( @WorkGrp,
@WorkArea,
@Area,
@ProNum,
@Location,
@Quantity,
@Price,
@ExtCost)
Fetch Next From RsvInventoryCursor Into
@WorkGrp,
@WorkArea,
@Area,
@ProNum,
@Location,
@Quantity,
@Price,
@ExtCost
End
CLOSE RsvInventoryCursor
DEALLOCATE RsvInventoryCursor
Select
WorkGrp,
WorkArea,
Area,
ProNum,
Location,
Quantity,
Price,
ExtCost
From
@InventoryTable
Order By
Area, Location
GO
GRANT EXEC ON BFForecastOffsiteReplenishment TO PUBLIC
GO
ASKER
I want to insert values into a temporary table, then get more data from the other tables and append this data to the temporary table previously created. Once i have this done then I want to use this as my data set.
Ok. I think this should do it...
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'BFForecastOffsiteReplenishment')
BEGIN
DROP Procedure BFForecastOffsiteReplenishment
END
GO
CREATE Procedure BFForecastOffsiteReplenishment
AS
SET NOCOUNT ON
DECLARE
@WorkGrp varchar(3),
@WorkArea varchar(4),
@Area varchar(10),
@ProNum varchar(25),
@Location varchar(10),
@Quantity int,
@Price numeric,
@ExtCost numeric
DECLARE @InventoryTable TABLE
(
WorkGrp varchar(3),
WorkArea varchar(4),
Area varchar(10),
ProNum varchar(25),
Location varchar(10),
Quantity int,
Price numeric,
ExtCost numeric
)
DECLARE @ReserveTable TABLE
(
WorkGrp varchar(3),
WorkArea varchar(4),
Area varchar(10),
ProNum varchar(25),
Location varchar(10),
Quantity int,
Price numeric,
ExtCost numeric
)
DECLARE @ActiveTable TABLE
(
WorkGrp varchar(3),
WorkArea varchar(4),
Area varchar(10),
ProNum varchar(25),
Location varchar(10),
Quantity int,
Price numeric,
ExtCost numeric
)
SELECT
LH.WORK_GRP As WorkGrp,
LH.WORK_AREA As WorkAera,
Case
When LH.WORK_GRP = 'EAA' Or LH.WORK_GRP = 'CPC' Or LH.WORK_GRP = 'CPN' Then 'On-Site'
When LH.WORK_GRP = 'OFF' Then 'Off-Site'
When LH.WORK_GRP = 'RSV' And (LH.WORK_AREA = 'DR01' Or LH.WORK_AREA = 'DR02' Or
LH.WORK_AREA = 'DR03' Or LH.WORK_AREA = 'DR04' Or LH.WORK_AREA = 'DR05' Or
LH.WORK_AREA = 'DR06' Or LH.WORK_AREA = 'DR07' Or LH.WORK_AREA = 'DR14' Or
LH.WORK_AREA = 'DR15' Or LH.WORK_AREA = 'DR16' Or LH.WORK_AREA = 'DR17' Or
LH.WORK_AREA = 'DR18' Or LH.WORK_AREA = 'DR19' Or LH.WORK_AREA = 'DRE1' Or
LH.WORK_AREA = 'DRE2' Or LH.WORK_AREA = 'QA01' Or LH.WORK_AREA = 'R01A' Or
LH.WORK_AREA = 'R01B' Or LH.WORK_AREA = 'R03A' Or LH.WORK_AREA = 'R04A' Or
LH.WORK_AREA = 'R06A' Or LH.WORK_AREA = 'R07A' Or LH.WORK_AREA = 'R07B' Or
LH.WORK_AREA = 'R08A' Or LH.WORK_AREA = 'R09A' Or LH.WORK_AREA = 'R10A' Or
LH.WORK_AREA = 'R11A' Or LH.WORK_AREA = 'R12A' Or LH.WORK_AREA = 'R13A' Or
LH.WORK_AREA = 'R14A' Or LH.WORK_AREA = 'R14B' Or LH.WORK_AREA = 'R16A' Or
LH.WORK_AREA = 'R17A' Or LH.WORK_AREA = 'R18A' Or LH.WORK_AREA = 'R19A' Or
LH.WORK_AREA = 'R61A' Or LH.WORK_AREA = 'R64A' Or LH.WORK_AREA = 'R65A' Or
LH.WORK_AREA = 'R66A' Or LH.WORK_AREA = 'R67A' Or LH.WORK_AREA = 'R68A' Or
LH.WORK_AREA = 'R69A' Or LH.WORK_AREA = 'R70A' Or LH.WORK_AREA = 'R71A' Or
LH.WORK_AREA = 'R72A' Or LH.WORK_AREA = 'R73A' Or LH.WORK_AREA = 'R74A' Or
LH.WORK_AREA = 'R78A' Or LH.WORK_AREA = 'R80A' Or LH.WORK_AREA = 'R81A' Or
LH.WORK_AREA = 'R82A' Or LH.WORK_AREA = 'R83A' Or LH.WORK_AREA = 'R84A' Or
LH.WORK_AREA = 'R85A' Or LH.WORK_AREA = 'R86A' Or LH.WORK_AREA = 'R88A') Then 'On-Site'
When LH.WORK_GRP = 'RSV' And LH.WORK_AREA = 'DR30' Then 'Off-Site'
End As Area,
IM.SKU_DESC As Part_Number,
LH.DSP_LOCN As Location,
PLD.ACTL_INVN_QTY As Quantity,
IM.UNIT_PRICE As Price,
PLD.ACTL_INVN_QTY * IM.UNIT_PRICE AS Extended_Cost
FROM
ITEM_MASTER As IM INNER JOIN
PICK_LOCN_DTL As PLD ON IM.SKU_ID = PLD.SKU_ID INNER JOIN
PICK_LOCN_HDR As PLH ON PLD.LOCN_ID = PLH.LOCN_ID INNER JOIN
LOCN_HDR As LH ON PLH.LOCN_ID = LH.LOCN_ID
ORDER BY
Area, Location
DECLARE ActiveInvCursor CURSOR FAST_FORWARD FOR
SELECT
WorkGrp,
WorkArea,
Area,
ProNum,
Location,
Quantity,
Price,
ExtCost
FROM
@ActiveTable
Open ActiveInvCursor
Fetch Next From ActiveInvCursor Into
@WorkGrp,
@WorkArea,
@Area,
@ProNum,
@Location,
@Quantity,
@Price,
@ExtCost
While @@FETCH_STATUS = 0
Begin
Insert Into @InventoryTable
( WorkGrp,
WorkArea,
Area,
Pronum,
Location,
Quantity,
Price,
ExtCost)
Values
( @WorkGrp,
@WorkArea,
@Area,
@ProNum,
@Location,
@Quantity,
@Price,
@ExtCost)
Insert Into @InventoryTable
( WorkGrp,
WorkArea,
Area,
Pronum,
Location,
Quantity,
Price,
ExtCost)
Values
( @WorkGrp,
@WorkArea,
@Area,
@ProNum,
@Location,
@Quantity,
@Price,
@ExtCost)
Fetch Next From ActiveInvCursor Into
@WorkGrp,
@WorkArea,
@Area,
@ProNum,
@Location,
@Quantity,
@Price,
@ExtCost
End
CLOSE ActiveInvCursor
DEALLOCATE ActiveInvCursor
INSERT INTO @ReserveTable
( WorkGrp,
WorkArea,
Area,
Pronum,
Location,
Quantity,
Price,
ExtCost)
SELECT
LH.WORK_GRP As WorkGrp,
LH.WORK_AREA As WorkAera,
Case
When LH.WORK_GRP = 'EAA' Or LH.WORK_GRP = 'CPC' Or LH.WORK_GRP = 'CPN' Then 'On-Site'
When LH.WORK_GRP = 'OFF' Then 'Off-Site'
When LH.WORK_GRP = 'RSV' And (LH.WORK_AREA = 'DR01' Or LH.WORK_AREA = 'DR02' Or
LH.WORK_AREA = 'DR03' Or LH.WORK_AREA = 'DR04' Or LH.WORK_AREA = 'DR05' Or
LH.WORK_AREA = 'DR06' Or LH.WORK_AREA = 'DR07' Or LH.WORK_AREA = 'DR14' Or
LH.WORK_AREA = 'DR15' Or LH.WORK_AREA = 'DR16' Or LH.WORK_AREA = 'DR17' Or
LH.WORK_AREA = 'DR18' Or LH.WORK_AREA = 'DR19' Or LH.WORK_AREA = 'DRE1' Or
LH.WORK_AREA = 'DRE2' Or LH.WORK_AREA = 'QA01' Or LH.WORK_AREA = 'R01A' Or
LH.WORK_AREA = 'R01B' Or LH.WORK_AREA = 'R03A' Or LH.WORK_AREA = 'R04A' Or
LH.WORK_AREA = 'R06A' Or LH.WORK_AREA = 'R07A' Or LH.WORK_AREA = 'R07B' Or
LH.WORK_AREA = 'R08A' Or LH.WORK_AREA = 'R09A' Or LH.WORK_AREA = 'R10A' Or
LH.WORK_AREA = 'R11A' Or LH.WORK_AREA = 'R12A' Or LH.WORK_AREA = 'R13A' Or
LH.WORK_AREA = 'R14A' Or LH.WORK_AREA = 'R14B' Or LH.WORK_AREA = 'R16A' Or
LH.WORK_AREA = 'R17A' Or LH.WORK_AREA = 'R18A' Or LH.WORK_AREA = 'R19A' Or
LH.WORK_AREA = 'R61A' Or LH.WORK_AREA = 'R64A' Or LH.WORK_AREA = 'R65A' Or
LH.WORK_AREA = 'R66A' Or LH.WORK_AREA = 'R67A' Or LH.WORK_AREA = 'R68A' Or
LH.WORK_AREA = 'R69A' Or LH.WORK_AREA = 'R70A' Or LH.WORK_AREA = 'R71A' Or
LH.WORK_AREA = 'R72A' Or LH.WORK_AREA = 'R73A' Or LH.WORK_AREA = 'R74A' Or
LH.WORK_AREA = 'R78A' Or LH.WORK_AREA = 'R80A' Or LH.WORK_AREA = 'R81A' Or
LH.WORK_AREA = 'R82A' Or LH.WORK_AREA = 'R83A' Or LH.WORK_AREA = 'R84A' Or
LH.WORK_AREA = 'R85A' Or LH.WORK_AREA = 'R86A' Or LH.WORK_AREA = 'R88A') Then 'On-Site'
When LH.WORK_GRP = 'RSV' And LH.WORK_AREA = 'DR30' Then 'Off-Site'
End As Area,
IM.SKU_DESC As ProNum,
LH.DSP_LOCN As Location,
CD.ACTL_QTY As Quantity,
IM.UNIT_PRICE As Price,
CD.ACTL_QTY * IM.UNIT_PRICE AS ExtCost
FROM
ITEM_MASTER IM INNER JOIN
CASE_DTL CD ON IM.SKU_ID = CD.SKU_ID INNER JOIN
CASE_HDR CH ON CD.CASE_NBR = CH.CASE_NBR INNER JOIN
LOCN_HDR LH ON CH.LOCN_ID = LH.LOCN_ID AND LH.WHSE = CH.WHSE INNER JOIN
RESV_LOCN_HDR RLH ON LH.LOCN_ID = RLH.LOCN_ID
ORDER BY
Area, Location
DECLARE RsvInventoryCursor CURSOR FAST_FORWARD FOR
SELECT
WorkGrp,
WorkArea,
Area,
ProNum,
Location,
Quantity,
Price,
ExtCost
FROM
@ReserveTable ReserveInventory
Open RsvInventoryCursor
Fetch Next From RsvInventoryCursor Into
@WorkGrp,
@WorkArea,
@Area,
@ProNum,
@Location,
@Quantity,
@Price,
@ExtCost
While @@FETCH_STATUS = 0
Begin
Insert Into @InventoryTable
( WorkGrp,
WorkArea,
Area,
Pronum,
Location,
Quantity,
Price,
ExtCost)
Values
( @WorkGrp,
@WorkArea,
@Area,
@ProNum,
@Location,
@Quantity,
@Price,
@ExtCost)
Fetch Next From RsvInventoryCursor Into
@WorkGrp,
@WorkArea,
@Area,
@ProNum,
@Location,
@Quantity,
@Price,
@ExtCost
End
CLOSE RsvInventoryCursor
DEALLOCATE RsvInventoryCursor
Select
WorkGrp,
WorkArea,
Area,
ProNum,
Location,
Quantity,
Price,
ExtCost
From
@InventoryTable
Order By
Area, Location
GO
GRANT EXEC ON BFForecastOffsiteReplenishment TO PUBLIC
GO
ASKER
Ok, that seems to work, however i end up with two tables. Can I then merge these two tables into one so that i am looking at one data set?
After looking further, your cursors are COMPLETELY unnecessary.
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'BFForecastOffsiteReplenishment')
BEGIN
DROP Procedure BFForecastOffsiteReplenishment
END
GO
CREATE Procedure BFForecastOffsiteReplenishment
AS
SET NOCOUNT ON
DECLARE
@WorkGrp varchar(3),
@WorkArea varchar(4),
@Area varchar(10),
@ProNum varchar(25),
@Location varchar(10),
@Quantity int,
@Price numeric,
@ExtCost numeric
DECLARE @InventoryTable TABLE
(
WorkGrp varchar(3),
WorkArea varchar(4),
Area varchar(10),
ProNum varchar(25),
Location varchar(10),
Quantity int,
Price numeric,
ExtCost numeric
)
Insert Into @InventoryTable
( WorkGrp,
WorkArea,
Area,
Pronum,
Location,
Quantity,
Price,
ExtCost)
SELECT
LH.WORK_GRP As WorkGrp,
LH.WORK_AREA As WorkAera,
Case
When LH.WORK_GRP = 'EAA' Or LH.WORK_GRP = 'CPC' Or LH.WORK_GRP = 'CPN' Then 'On-Site'
When LH.WORK_GRP = 'OFF' Then 'Off-Site'
When LH.WORK_GRP = 'RSV' And (LH.WORK_AREA = 'DR01' Or LH.WORK_AREA = 'DR02' Or
LH.WORK_AREA = 'DR03' Or LH.WORK_AREA = 'DR04' Or LH.WORK_AREA = 'DR05' Or
LH.WORK_AREA = 'DR06' Or LH.WORK_AREA = 'DR07' Or LH.WORK_AREA = 'DR14' Or
LH.WORK_AREA = 'DR15' Or LH.WORK_AREA = 'DR16' Or LH.WORK_AREA = 'DR17' Or
LH.WORK_AREA = 'DR18' Or LH.WORK_AREA = 'DR19' Or LH.WORK_AREA = 'DRE1' Or
LH.WORK_AREA = 'DRE2' Or LH.WORK_AREA = 'QA01' Or LH.WORK_AREA = 'R01A' Or
LH.WORK_AREA = 'R01B' Or LH.WORK_AREA = 'R03A' Or LH.WORK_AREA = 'R04A' Or
LH.WORK_AREA = 'R06A' Or LH.WORK_AREA = 'R07A' Or LH.WORK_AREA = 'R07B' Or
LH.WORK_AREA = 'R08A' Or LH.WORK_AREA = 'R09A' Or LH.WORK_AREA = 'R10A' Or
LH.WORK_AREA = 'R11A' Or LH.WORK_AREA = 'R12A' Or LH.WORK_AREA = 'R13A' Or
LH.WORK_AREA = 'R14A' Or LH.WORK_AREA = 'R14B' Or LH.WORK_AREA = 'R16A' Or
LH.WORK_AREA = 'R17A' Or LH.WORK_AREA = 'R18A' Or LH.WORK_AREA = 'R19A' Or
LH.WORK_AREA = 'R61A' Or LH.WORK_AREA = 'R64A' Or LH.WORK_AREA = 'R65A' Or
LH.WORK_AREA = 'R66A' Or LH.WORK_AREA = 'R67A' Or LH.WORK_AREA = 'R68A' Or
LH.WORK_AREA = 'R69A' Or LH.WORK_AREA = 'R70A' Or LH.WORK_AREA = 'R71A' Or
LH.WORK_AREA = 'R72A' Or LH.WORK_AREA = 'R73A' Or LH.WORK_AREA = 'R74A' Or
LH.WORK_AREA = 'R78A' Or LH.WORK_AREA = 'R80A' Or LH.WORK_AREA = 'R81A' Or
LH.WORK_AREA = 'R82A' Or LH.WORK_AREA = 'R83A' Or LH.WORK_AREA = 'R84A' Or
LH.WORK_AREA = 'R85A' Or LH.WORK_AREA = 'R86A' Or LH.WORK_AREA = 'R88A') Then 'On-Site'
When LH.WORK_GRP = 'RSV' And LH.WORK_AREA = 'DR30' Then 'Off-Site'
End As Area,
IM.SKU_DESC As Part_Number,
LH.DSP_LOCN As Location,
PLD.ACTL_INVN_QTY As Quantity,
IM.UNIT_PRICE As Price,
PLD.ACTL_INVN_QTY * IM.UNIT_PRICE AS Extended_Cost
FROM
ITEM_MASTER As IM INNER JOIN
PICK_LOCN_DTL As PLD ON IM.SKU_ID = PLD.SKU_ID INNER JOIN
PICK_LOCN_HDR As PLH ON PLD.LOCN_ID = PLH.LOCN_ID INNER JOIN
LOCN_HDR As LH ON PLH.LOCN_ID = LH.LOCN_ID
UNION ALL
SELECT
LH.WORK_GRP As WorkGrp,
LH.WORK_AREA As WorkAera,
Case
When LH.WORK_GRP = 'EAA' Or LH.WORK_GRP = 'CPC' Or LH.WORK_GRP = 'CPN' Then 'On-Site'
When LH.WORK_GRP = 'OFF' Then 'Off-Site'
When LH.WORK_GRP = 'RSV' And (LH.WORK_AREA = 'DR01' Or LH.WORK_AREA = 'DR02' Or
LH.WORK_AREA = 'DR03' Or LH.WORK_AREA = 'DR04' Or LH.WORK_AREA = 'DR05' Or
LH.WORK_AREA = 'DR06' Or LH.WORK_AREA = 'DR07' Or LH.WORK_AREA = 'DR14' Or
LH.WORK_AREA = 'DR15' Or LH.WORK_AREA = 'DR16' Or LH.WORK_AREA = 'DR17' Or
LH.WORK_AREA = 'DR18' Or LH.WORK_AREA = 'DR19' Or LH.WORK_AREA = 'DRE1' Or
LH.WORK_AREA = 'DRE2' Or LH.WORK_AREA = 'QA01' Or LH.WORK_AREA = 'R01A' Or
LH.WORK_AREA = 'R01B' Or LH.WORK_AREA = 'R03A' Or LH.WORK_AREA = 'R04A' Or
LH.WORK_AREA = 'R06A' Or LH.WORK_AREA = 'R07A' Or LH.WORK_AREA = 'R07B' Or
LH.WORK_AREA = 'R08A' Or LH.WORK_AREA = 'R09A' Or LH.WORK_AREA = 'R10A' Or
LH.WORK_AREA = 'R11A' Or LH.WORK_AREA = 'R12A' Or LH.WORK_AREA = 'R13A' Or
LH.WORK_AREA = 'R14A' Or LH.WORK_AREA = 'R14B' Or LH.WORK_AREA = 'R16A' Or
LH.WORK_AREA = 'R17A' Or LH.WORK_AREA = 'R18A' Or LH.WORK_AREA = 'R19A' Or
LH.WORK_AREA = 'R61A' Or LH.WORK_AREA = 'R64A' Or LH.WORK_AREA = 'R65A' Or
LH.WORK_AREA = 'R66A' Or LH.WORK_AREA = 'R67A' Or LH.WORK_AREA = 'R68A' Or
LH.WORK_AREA = 'R69A' Or LH.WORK_AREA = 'R70A' Or LH.WORK_AREA = 'R71A' Or
LH.WORK_AREA = 'R72A' Or LH.WORK_AREA = 'R73A' Or LH.WORK_AREA = 'R74A' Or
LH.WORK_AREA = 'R78A' Or LH.WORK_AREA = 'R80A' Or LH.WORK_AREA = 'R81A' Or
LH.WORK_AREA = 'R82A' Or LH.WORK_AREA = 'R83A' Or LH.WORK_AREA = 'R84A' Or
LH.WORK_AREA = 'R85A' Or LH.WORK_AREA = 'R86A' Or LH.WORK_AREA = 'R88A') Then 'On-Site'
When LH.WORK_GRP = 'RSV' And LH.WORK_AREA = 'DR30' Then 'Off-Site'
End As Area,
IM.SKU_DESC As ProNum,
LH.DSP_LOCN As Location,
CD.ACTL_QTY As Quantity,
IM.UNIT_PRICE As Price,
CD.ACTL_QTY * IM.UNIT_PRICE AS ExtCost
FROM
ITEM_MASTER IM INNER JOIN
CASE_DTL CD ON IM.SKU_ID = CD.SKU_ID INNER JOIN
CASE_HDR CH ON CD.CASE_NBR = CH.CASE_NBR INNER JOIN
LOCN_HDR LH ON CH.LOCN_ID = LH.LOCN_ID AND LH.WHSE = CH.WHSE INNER JOIN
RESV_LOCN_HDR RLH ON LH.LOCN_ID = RLH.LOCN_ID
ORDER BY
Area, Location
Select
WorkGrp,
WorkArea,
Area,
ProNum,
Location,
Quantity,
Price,
ExtCost
From
@InventoryTable
Order By
Area, Location
GO
GRANT EXEC ON BFForecastOffsiteReplenishment TO PUBLIC
GO
You should mention the values or a query after thiese INSERTs.
like INSERT INTO @ActiveTable values (WorkGrp value,WorkArea value,...) or
INSERT INTO @ActiveTable SELECT WorkGrp,WorkArea,Area,ProN
I have commented these two INSERT statements. You can uncomment and make those two statement complet by adding the VALUES clause or another SELECT query.
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'BFForecastOffsiteReplenishment')
BEGIN
DROP Procedure BFForecastOffsiteReplenishment
END
GO
CREATE Procedure BFForecastOffsiteReplenishment
AS
SET NOCOUNT ON
DECLARE
@WorkGrp varchar(3),
@WorkArea varchar(4),
@Area varchar(10),
@ProNum varchar(25),
@Location varchar(10),
@Quantity int,
@Price numeric,
@ExtCost numeric
DECLARE @InventoryTable TABLE
(
WorkGrp varchar(3),
WorkArea varchar(4),
Area varchar(10),
ProNum varchar(25),
Location varchar(10),
Quantity int,
Price numeric,
ExtCost numeric
)
DECLARE @ReserveTable TABLE
(
WorkGrp varchar(3),
WorkArea varchar(4),
Area varchar(10),
ProNum varchar(25),
Location varchar(10),
Quantity int,
Price numeric,
ExtCost numeric
)
DECLARE @ActiveTable TABLE
(
WorkGrp varchar(3),
WorkArea varchar(4),
Area varchar(10),
ProNum varchar(25),
Location varchar(10),
Quantity int,
Price numeric,
ExtCost numeric
)
SELECT
LH.WORK_GRP As WorkGrp,
LH.WORK_AREA As WorkAera,
Case
When LH.WORK_GRP = 'EAA' Or LH.WORK_GRP = 'CPC' Or LH.WORK_GRP = 'CPN' Then 'On-Site'
When LH.WORK_GRP = 'OFF' Then 'Off-Site'
When LH.WORK_GRP = 'RSV' And (LH.WORK_AREA = 'DR01' Or LH.WORK_AREA = 'DR02' Or
LH.WORK_AREA = 'DR03' Or LH.WORK_AREA = 'DR04' Or LH.WORK_AREA = 'DR05' Or
LH.WORK_AREA = 'DR06' Or LH.WORK_AREA = 'DR07' Or LH.WORK_AREA = 'DR14' Or
LH.WORK_AREA = 'DR15' Or LH.WORK_AREA = 'DR16' Or LH.WORK_AREA = 'DR17' Or
LH.WORK_AREA = 'DR18' Or LH.WORK_AREA = 'DR19' Or LH.WORK_AREA = 'DRE1' Or
LH.WORK_AREA = 'DRE2' Or LH.WORK_AREA = 'QA01' Or LH.WORK_AREA = 'R01A' Or
LH.WORK_AREA = 'R01B' Or LH.WORK_AREA = 'R03A' Or LH.WORK_AREA = 'R04A' Or
LH.WORK_AREA = 'R06A' Or LH.WORK_AREA = 'R07A' Or LH.WORK_AREA = 'R07B' Or
LH.WORK_AREA = 'R08A' Or LH.WORK_AREA = 'R09A' Or LH.WORK_AREA = 'R10A' Or
LH.WORK_AREA = 'R11A' Or LH.WORK_AREA = 'R12A' Or LH.WORK_AREA = 'R13A' Or
LH.WORK_AREA = 'R14A' Or LH.WORK_AREA = 'R14B' Or LH.WORK_AREA = 'R16A' Or
LH.WORK_AREA = 'R17A' Or LH.WORK_AREA = 'R18A' Or LH.WORK_AREA = 'R19A' Or
LH.WORK_AREA = 'R61A' Or LH.WORK_AREA = 'R64A' Or LH.WORK_AREA = 'R65A' Or
LH.WORK_AREA = 'R66A' Or LH.WORK_AREA = 'R67A' Or LH.WORK_AREA = 'R68A' Or
LH.WORK_AREA = 'R69A' Or LH.WORK_AREA = 'R70A' Or LH.WORK_AREA = 'R71A' Or
LH.WORK_AREA = 'R72A' Or LH.WORK_AREA = 'R73A' Or LH.WORK_AREA = 'R74A' Or
LH.WORK_AREA = 'R78A' Or LH.WORK_AREA = 'R80A' Or LH.WORK_AREA = 'R81A' Or
LH.WORK_AREA = 'R82A' Or LH.WORK_AREA = 'R83A' Or LH.WORK_AREA = 'R84A' Or
LH.WORK_AREA = 'R85A' Or LH.WORK_AREA = 'R86A' Or LH.WORK_AREA = 'R88A') Then 'On-Site'
When LH.WORK_GRP = 'RSV' And LH.WORK_AREA = 'DR30' Then 'Off-Site'
End As Area,
IM.SKU_DESC As Part_Number,
LH.DSP_LOCN As Location,
PLD.ACTL_INVN_QTY As Quantity,
IM.UNIT_PRICE As Price,
PLD.ACTL_INVN_QTY * IM.UNIT_PRICE AS Extended_Cost
FROM
ITEM_MASTER As IM INNER JOIN
PICK_LOCN_DTL As PLD ON IM.SKU_ID = PLD.SKU_ID INNER JOIN
PICK_LOCN_HDR As PLH ON PLD.LOCN_ID = PLH.LOCN_ID INNER JOIN
LOCN_HDR As LH ON PLH.LOCN_ID = LH.LOCN_ID
ORDER BY
Area, Location
--INSERT INTO @ActiveTable
--( WorkGrp,
-- WorkArea,
-- Area,
-- ProNum,
-- Location,
-- Quantity,
-- Price,
-- ExtCost)
DECLARE ActiveInvCursor CURSOR FAST_FORWARD FOR
SELECT
WorkGrp,
WorkArea,
Area,
ProNum,
Location,
Quantity,
Price,
ExtCost
FROM
@ActiveTable
Open ActiveInvCursor
Fetch Next From ActiveInvCursor Into
@WorkGrp,
@WorkArea,
@Area,
@ProNum,
@Location,
@Quantity,
@Price,
@ExtCost
While @@FETCH_STATUS = 0
Begin
Insert Into @InventoryTable
( WorkGrp,
WorkArea,
Area,
Pronum,
Location,
Quantity,
Price,
ExtCost)
Values
( @WorkGrp,
@WorkArea,
@Area,
@ProNum,
@Location,
@Quantity,
@Price,
@ExtCost)
Insert Into @InventoryTable
( WorkGrp,
WorkArea,
Area,
Pronum,
Location,
Quantity,
Price,
ExtCost)
Values
( @WorkGrp,
@WorkArea,
@Area,
@ProNum,
@Location,
@Quantity,
@Price,
@ExtCost)
Fetch Next From ActiveInvCursor Into
@WorkGrp,
@WorkArea,
@Area,
@ProNum,
@Location,
@Quantity,
@Price,
@ExtCost
End
CLOSE ActiveInvCursor
DEALLOCATE ActiveInvCursor
SELECT
LH.WORK_GRP As WorkGrp,
LH.WORK_AREA As WorkAera,
Case
When LH.WORK_GRP = 'EAA' Or LH.WORK_GRP = 'CPC' Or LH.WORK_GRP = 'CPN' Then 'On-Site'
When LH.WORK_GRP = 'OFF' Then 'Off-Site'
When LH.WORK_GRP = 'RSV' And (LH.WORK_AREA = 'DR01' Or LH.WORK_AREA = 'DR02' Or
LH.WORK_AREA = 'DR03' Or LH.WORK_AREA = 'DR04' Or LH.WORK_AREA = 'DR05' Or
LH.WORK_AREA = 'DR06' Or LH.WORK_AREA = 'DR07' Or LH.WORK_AREA = 'DR14' Or
LH.WORK_AREA = 'DR15' Or LH.WORK_AREA = 'DR16' Or LH.WORK_AREA = 'DR17' Or
LH.WORK_AREA = 'DR18' Or LH.WORK_AREA = 'DR19' Or LH.WORK_AREA = 'DRE1' Or
LH.WORK_AREA = 'DRE2' Or LH.WORK_AREA = 'QA01' Or LH.WORK_AREA = 'R01A' Or
LH.WORK_AREA = 'R01B' Or LH.WORK_AREA = 'R03A' Or LH.WORK_AREA = 'R04A' Or
LH.WORK_AREA = 'R06A' Or LH.WORK_AREA = 'R07A' Or LH.WORK_AREA = 'R07B' Or
LH.WORK_AREA = 'R08A' Or LH.WORK_AREA = 'R09A' Or LH.WORK_AREA = 'R10A' Or
LH.WORK_AREA = 'R11A' Or LH.WORK_AREA = 'R12A' Or LH.WORK_AREA = 'R13A' Or
LH.WORK_AREA = 'R14A' Or LH.WORK_AREA = 'R14B' Or LH.WORK_AREA = 'R16A' Or
LH.WORK_AREA = 'R17A' Or LH.WORK_AREA = 'R18A' Or LH.WORK_AREA = 'R19A' Or
LH.WORK_AREA = 'R61A' Or LH.WORK_AREA = 'R64A' Or LH.WORK_AREA = 'R65A' Or
LH.WORK_AREA = 'R66A' Or LH.WORK_AREA = 'R67A' Or LH.WORK_AREA = 'R68A' Or
LH.WORK_AREA = 'R69A' Or LH.WORK_AREA = 'R70A' Or LH.WORK_AREA = 'R71A' Or
LH.WORK_AREA = 'R72A' Or LH.WORK_AREA = 'R73A' Or LH.WORK_AREA = 'R74A' Or
LH.WORK_AREA = 'R78A' Or LH.WORK_AREA = 'R80A' Or LH.WORK_AREA = 'R81A' Or
LH.WORK_AREA = 'R82A' Or LH.WORK_AREA = 'R83A' Or LH.WORK_AREA = 'R84A' Or
LH.WORK_AREA = 'R85A' Or LH.WORK_AREA = 'R86A' Or LH.WORK_AREA = 'R88A') Then 'On-Site'
When LH.WORK_GRP = 'RSV' And LH.WORK_AREA = 'DR30' Then 'Off-Site'
End As Area,
IM.SKU_DESC As ProNum,
LH.DSP_LOCN As Location,
CD.ACTL_QTY As Quantity,
IM.UNIT_PRICE As Price,
CD.ACTL_QTY * IM.UNIT_PRICE AS ExtCost
FROM
ITEM_MASTER IM INNER JOIN
CASE_DTL CD ON IM.SKU_ID = CD.SKU_ID INNER JOIN
CASE_HDR CH ON CD.CASE_NBR = CH.CASE_NBR INNER JOIN
LOCN_HDR LH ON CH.LOCN_ID = LH.LOCN_ID AND LH.WHSE = CH.WHSE INNER JOIN
RESV_LOCN_HDR RLH ON LH.LOCN_ID = RLH.LOCN_ID
ORDER BY
Area, Location
--INSERT INTO @ReserveTable
--( WorkGrp,
-- WorkArea,
-- Area,
-- Pronum,
-- Location,
-- Quantity,
-- Price,
-- ExtCost)
DECLARE RsvInventoryCursor CURSOR FAST_FORWARD FOR
SELECT
WorkGrp,
WorkArea,
Area,
ProNum,
Location,
Quantity,
Price,
ExtCost
FROM
@ReserveTable ReserveInventory
Open RsvInventoryCursor
Fetch Next From RsvInventoryCursor Into
@WorkGrp,
@WorkArea,
@Area,
@ProNum,
@Location,
@Quantity,
@Price,
@ExtCost
While @@FETCH_STATUS = 0
Begin
Insert Into @InventoryTable
( WorkGrp,
WorkArea,
Area,
Pronum,
Location,
Quantity,
Price,
ExtCost)
Values
( @WorkGrp,
@WorkArea,
@Area,
@ProNum,
@Location,
@Quantity,
@Price,
@ExtCost)
Fetch Next From RsvInventoryCursor Into
@WorkGrp,
@WorkArea,
@Area,
@ProNum,
@Location,
@Quantity,
@Price,
@ExtCost
End
CLOSE RsvInventoryCursor
DEALLOCATE RsvInventoryCursor
Select
WorkGrp,
WorkArea,
Area,
ProNum,
Location,
Quantity,
Price,
ExtCost
From
@InventoryTable
Order By
Area, Location
GO
GRANT EXEC ON BFForecastOffsiteReplenishment TO PUBLIC
GO
ASKER
That is it, only my price and extcost fields are turning to integer and i am loosing my decimal. Is there a way that I can keep them numeric instead of integer?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Your a genius! Thanks for the help! You saved me a lot of time and research!!!!
I don't think multiple cursors are justified for what you are trying to extract, a simple union query would appear to suffice..
yup... posted that in http://#a23652703
DECLARE ActiveInvCursor CURSOR FAST_FORWARD FOR
The same thing repeats on line 245.
DECLARE RsvInventoryCursor CURSOR FAST_FORWARD FOR
Are you wanting to insert values or declare a cursor???