how to re-write this code

[tc3596]

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

 

[mirtheil]

Well, from a syntax perspective the syntax in PSQL is different. For example, variables are declared with a colon (":") rather than the at symbol ("@"). Also, to return a recordset from a Stored Procedure, you'll need a "RETURNS" clause describing the fields being returned. ALso, the Convert statement is different in PSQL. For PSQL, it's:
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

 

[MarkatLMFJ]

does PSQL have a TABLES datatype that can be used in place of a temp table?, I thought I saw a tempdb on my pervasive system, what is the role of the tempdb database in Pervasive?

ML

 

[mirtheil]

PSQL V9 supports Temp Tables and uses TempDB for that. PSQL V8 does not support temp tables.
Here's the syntax:

http://www.pervasive.com/library/docs/psql/950/sqlref/sqlref-04-24.html

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[tc3596]

still having issues. I am trying to piece meal this code. I'm doing a simple...

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.

 

[MarkatLMFJ]

tc3596 --- can't help you out I'm fighting with psql myself; but I feel your pain bro, I'm a MS-SQL guru
ML

 

[mirtheil]

If you're still having problems, I would suggest opening a support ticket with Pervasive. I'm sure they can help.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com