tc3596
Technical User
- Mar 16, 2001
- 283
I am very knowledgable in SQL Server. I have written this code in SQL Server and need it to be re-created using pervasive v.8 as a stored procedure. I will then call that stored procedure in crystal reports v 8.5 for a report. Thanks for your help.
Declare @ItemNo varchar(15), @Qty decimal(13, 6)
--run the indented Bill of material for eac item and each location
Declare @i int
Declare @Temp Table(Level int, Master_Item char(30), Comp_Item_No char(30), Item_Description char(30), Qty_Per_Par decimal(13, 6), NewQty decimal(13, 6))
DECLARE titles_cursor CURSOR FOR
SELECT Item_No, Qty FROM MSFRCFIL_SQL
WHERE ITEM_NO Like 'M%' --AND LOC = 'AK'
And Month(Substring(Convert(varchar, Due_Dt), 5, 2) + '/' + right(Convert(varchar, Due_Dt), 2) + '/' + Left(Convert(varchar, Due_Dt), 4)) = 12
order by ITEM_NO asc,LOC asc
OPEN titles_cursor
FETCH NEXT FROM titles_cursor INTO @ItemNo, @Qty
WHILE @@FETCH_STATUS = 0
BEGIN
Set @i = 1
While @i <= 11
Begin
If @i = 1
Begin
Insert Into @Temp
Select
@i, @ItemNo, BMP.Comp_Item_No, ITEM.Item_Desc_1, BMP.Qty_Per_Par, BMP.Qty_Per_Par * @Qty
From
BMPRDSTR_SQL AS BMP
Inner Join IMITMIDX_SQL AS ITEM ON ITEM.Item_No = BMP.Comp_Item_No
Where
BMP.Item_No = @ItemNo
End
Else
Begin
Insert Into @Temp
Select
@i, @ItemNo, BMP.Comp_Item_No, ITEM.Item_Desc_1, BMP.Qty_Per_Par, BMP.Qty_Per_Par * @Qty
From
BMPRDSTR_SQL AS BMP
Inner Join IMITMIDX_SQL AS ITEM ON ITEM.Item_No = BMP.Comp_Item_No
Inner Join @Temp AS TMP ON TMP.Comp_Item_No = BMP.Item_No
Where
TMP.Level = (@i - 1)
End
Set @i = @i + 1
End
FETCH NEXT FROM titles_cursor INTO @ItemNo, @Qty
END
CLOSE titles_cursor
DEALLOCATE titles_cursor
select * from @Temp
Declare @ItemNo varchar(15), @Qty decimal(13, 6)
--run the indented Bill of material for eac item and each location
Declare @i int
Declare @Temp Table(Level int, Master_Item char(30), Comp_Item_No char(30), Item_Description char(30), Qty_Per_Par decimal(13, 6), NewQty decimal(13, 6))
DECLARE titles_cursor CURSOR FOR
SELECT Item_No, Qty FROM MSFRCFIL_SQL
WHERE ITEM_NO Like 'M%' --AND LOC = 'AK'
And Month(Substring(Convert(varchar, Due_Dt), 5, 2) + '/' + right(Convert(varchar, Due_Dt), 2) + '/' + Left(Convert(varchar, Due_Dt), 4)) = 12
order by ITEM_NO asc,LOC asc
OPEN titles_cursor
FETCH NEXT FROM titles_cursor INTO @ItemNo, @Qty
WHILE @@FETCH_STATUS = 0
BEGIN
Set @i = 1
While @i <= 11
Begin
If @i = 1
Begin
Insert Into @Temp
Select
@i, @ItemNo, BMP.Comp_Item_No, ITEM.Item_Desc_1, BMP.Qty_Per_Par, BMP.Qty_Per_Par * @Qty
From
BMPRDSTR_SQL AS BMP
Inner Join IMITMIDX_SQL AS ITEM ON ITEM.Item_No = BMP.Comp_Item_No
Where
BMP.Item_No = @ItemNo
End
Else
Begin
Insert Into @Temp
Select
@i, @ItemNo, BMP.Comp_Item_No, ITEM.Item_Desc_1, BMP.Qty_Per_Par, BMP.Qty_Per_Par * @Qty
From
BMPRDSTR_SQL AS BMP
Inner Join IMITMIDX_SQL AS ITEM ON ITEM.Item_No = BMP.Comp_Item_No
Inner Join @Temp AS TMP ON TMP.Comp_Item_No = BMP.Item_No
Where
TMP.Level = (@i - 1)
End
Set @i = @i + 1
End
FETCH NEXT FROM titles_cursor INTO @ItemNo, @Qty
END
CLOSE titles_cursor
DEALLOCATE titles_cursor
select * from @Temp