×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

how to re-write this code

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

RE: how to re-write this code

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

RE: how to re-write this code

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

RE: how to re-write this code

(OP)
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.

RE: how to re-write this code

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

RE: how to re-write this code

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close