Re: IN list of where condition using in a cursor

C Chang wrote:
>
> I use an explicit cursor to get multiple rows of records. My qustion is
> how to apply the variable to the IN list in the where clause. My example
> as
> CURSOR c_qty_rcvd ( i_site IN VARCHAR2,
> i_vendor IN VARCHAR2) IS
> SELECT SUM(r.qty), r.unit
> FROM receipts r, lines p, heads h
> WHERE r.site_id= i_site
> AND h.vendor_id = i_vendor <--- replace with h.vendor_id IN
> (...)
> AND (other conditions);
> In the IN list some cases, there is only one variable, sometimes there
> are more than one.
>
> So when I use
> OPEN c_qty_rcvd(v_site,v_vendor);
> How do I prepare the IN list in the Cursor? I have tried to turn
> i_vendor into string such as h.vendor_id IN (i_vendor) in CURSOR and
> v_vendor := ''A'',''B''; before the OPEN but the compiler compalined.
> And I can put v_vendor := '(''A'',''B'')'; Since This cursor is in a
> loop of another cursor, the method mentioned in Tom's aarticle from web
> site (AskTom) seems not quite proper. I really do not know what to do.
> Need anyone help? Thanks.
>
> C Chang
>
> C Chang

Thanks everyone's advices. However, since I have to use the explicit cursor, I figured out the following way to my IN list in the explicit cursor:

Cursor c_qty ( p_siteID IN varchar2, p_vendorID IN varchar2) is

        SELECT SUM(r.qty), r.qty_unit
        FROM receipts r,  lines p,  heads h
        WHERE r.site_id= p_siteID
        AND h.vendor_id IN (select vendor_id            
                             from vendor v,
                             (select db_num
                              from vendor
                              where vendor_id = p_vendorID ) d
                              where v.db_num = d.db_num)
        AND 'other conditions'
       GROUP BY  r.qty_unit, r.site_id;

But the problem is will it be too resource extensive if this cursor been called in another dynamic cursor loop for 8K times?

C Chang Received on Mon Feb 10 2003 - 21:39:09 CST