×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

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

procedure only returning 1 row

procedure only returning 1 row

procedure only returning 1 row

(OP)
CREATE PROCEDURE usp_SubAssembly()

returns(Item_No char(15), Qty decimal(13, 6));

begin


DECLARE BTUCursor CURSOR FOR
 SELECT  Item_No, Qty  FROM MSFRCFIL_SQL
    WHERE ITEM_NO Like 'M%' AND LOC  =  'FG'      
    order by ITEM_NO asc,LOC asc;
    
Declare :Item char(15);
Declare :Qty decimal(13, 6);

OPEN BTUCursor;

       FETCH NEXT FROM BTUCursor INTO :Item, :Qty;     
    
   select :Item, :Qty;
   
    CLOSE BTUCursor;

end


...any thoughts?  I'm new to pervasive but proficient with SQL Server

RE: procedure only returning 1 row

You need to loop through the cursor to get all of the records.  The PSQL documentation is at http://www.pervasive.com/library/docs/psql/950/sqlref/sqlref-04-22.html.

I'm assuming that there's more to the procedure because the example you gave doesn't even need a stored procedure.  WIth PSQL, there's no performance advantage to a stored procedure.  

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

RE: procedure only returning 1 row

(OP)
Yes, there is more.  I have a loop inside this cursor.  Boy, this is a lot easier in sql server.  I need, to loop thru each item number and qty and do another loop for each one of these.

RE: procedure only returning 1 row

(OP)
i feel stupid...

this is not working...I really need assistance.

I put this in..

OPEN BTUCursor;  

       FETCH NEXT FROM BTUCursor INTO :Item, :Qty;       
   
    loop

            select :Item, :Qty;

        FETCH NEXT FROM BTUCursor INTO :Item, :Qty;

        End loop;  
  end;

...this query won't stop (infinite loop)

...if i take out the loop... end loop parts, i get 1 record.

RE: procedure only returning 1 row

(OP)
i just tried this...

CREATE PROCEDURE usp_SubAssembly()

returns(Item_No char(15), Qty decimal(13, 6)) WITH DEFAULT HANDLER ;

begin


DECLARE BTUCursor CURSOR FOR
 SELECT  Item_No, Qty  FROM MSFRCFIL_SQL
    WHERE ITEM_NO Like 'M%' AND LOC  =  'FG'      
    order by ITEM_NO asc,LOC asc FOR READ ONLY ;
    
Declare :Item char(15);
Declare :Qty decimal(13, 6);



OPEN BTUCursor;
  

       FETCH NEXT FROM BTUCursor INTO :Item, :Qty;
       testloop:
LOOP

IF (SQLSTATE = '02000') THEN
LEAVE testloop;
end if;       

   select :Item, :Qty;

FETCH NEXT FROM BTUCursor INTO :Item, :Qty;

End loop;  
    CLOSE BTUCursor;
end;


...ODBC SQLSTATE S1000 Native Error Code = -1
...i am running this thru sql data manager

RE: procedure only returning 1 row

Perhaps we're going about this wrong.  What are you trying to accomplish with this Stored Procedure?  What's the end result?  All of the example I see related to cursors are in conjunction with DELETE and UPDATE and INSERT statements.
The reason I say this is that the SP above can be rewritten as:

CODE

CREATE PROCEDURE usp_SubAssembly()
returns(Item_No char(15), Qty decimal(13, 6)) WITH DEFAULT HANDLER ;
begin

 SELECT  Item_No, Qty  FROM MSFRCFIL_SQL
    WHERE ITEM_NO Like 'M%' AND LOC  =  'FG'      
    order by ITEM_NO asc,LOC asc;
end;

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

RE: procedure only returning 1 row

(OP)
mirtheil,
You responded to my other post...see thread318-1273004

I really appreciate you assisting me with this.  The end result is a display from crystal reports.  No other program is in the mix.  Basically, have the stored procedure as a source of data for the report.  I wanted to write the whole thing in crystal, but that would be really slow and confusing.

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