Re: IN list of where condition using in a cursor

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Fri, 07 Feb 2003 11:36:40 -0800
Message-ID: <3E440AC8.2924EB9@exesolutions.com>

Karsten Farrell wrote:

> 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

Have you tried this?

CREATE TABLE morgan
  stuff VARCHAR2(5));

INSERT INTO test VALUES ('A');
INSERT INTO test VALUES ('B');
INSERT INTO test VALUES ('C');

COMMIT; DECLARE
x VARCHAR2(5) := 'A' || ',' || 'B';
i PLS_INTEGER;

BEGIN
   SELECT COUNT(*)
   INTO i
   FROM morgan
   WHERE stuff IN x;
   DBMS_OUTPUT.PUT_LINE(i);
END;
/

No records are returned.

The only method that will work, other than dynamic SQL is as follows:

DECLARE
x VARCHAR2(5) := 'A' || ',' || 'B';
i PLS_INTEGER;

BEGIN
  INSERT INTO tmp VALUES ('A');
  INSERT INTO tmp VALUES ('B');

   SELECT COUNT(*)
   INTO i
   FROM morgan
   WHERE stuff IN (

      SELECT stuff
      FROM tmp);

   DBMS_OUTPUT.PUT_LINE(i);
END;
/

Which is what Sybrand suggested.

Daniel Morgan Received on Fri Feb 07 2003 - 13:36:40 CST