#1
  1. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2003
    Posts
    506
    Rep Power
    12

    tough time with cursors


    I am getting an error to which I am sure is because of a subquery within a cursor declaration. That may sound way wacked out to someone who knows what they are doing, but I was told that you cannot have a variable that hasn't been set yet in the cursor declaration, so I wasn't sure on another way to do it.

    Here is the code.
    Code:
    	--cursor declaration
    	cursor c_rentalInvoices is 
    		select rid.invoice_number, rid.date_returned
    		from rental_invoices ri, rental_invoice_details rid 
    		Where 
    			ri. member_number = Select member_number From members Where last_name = p_lastName AND first_name = p_firstName
    			And
    			ri. invoice_number = rid.invoice_number;
    Thanks for any help that you can give me
  2. #2
  3. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    308
    Rep Power
    12
    you can use the subquery into declaration of cursors, see the following example:

    create or replace function no_of_employee(p_deptno in number)
    return number is

    cursor c_emp is
    select * from emp
    where deptno = (select deptno from dept where deptno=p_deptno);

    begin

    return null;

    end;
    /
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2003
    Posts
    506
    Rep Power
    12
    so there doens't really seem to be anything missing from the cursor that I am using then. Hmm I guess that is a good thing, I just have to look elsewhere for the problem.

    Thanks for the help

IMN logo majestic logo threadwatch logo seochat tools logo