how to re-write this code
how to re-write this code
(OP)
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
Talk To Other Members
RE: how to re-write this code
CONVERT(value,SQL DataType).
PSQL V8 doesn't support Temporary Tables. You'd have to create and destroy the table yourself.
Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com
RE: how to re-write this code
ML
RE: how to re-write this code
Here's the syntax:
h
Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com
RE: how to re-write this code
returns(i int);
begin
declare :i int;
set :i = 1
while :i <= 11 do
select :i;
set :i = :i + 1;
end while;
end;
...it says I can't put parameters in a select list...That's crazy. Is this true, or is this a false error.
RE: how to re-write this code
ML
RE: how to re-write this code
Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com