Re: IN list of where condition using in a cursor

cschang_at_maxinter.net said...
> 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
>

I am not sure I follow what you actually tried, but I think you need to include the commas when you define v_vendor:

v_vendor := ''A''||','||''B'';

Then your cursor where clause becomes:

and h.vendor_id in (v_vendor)

-- 
/Karsten