Re: SELECT Question

From: Mtek <mtek_at_mtekusa.com>
Date: Tue, 17 Jun 2008 09:56:15 -0700 (PDT)
Message-ID: <e7b79b85-2182-48be-a478-f7a0e222fa37@k30g2000hse.googlegroups.com>


On Jun 17, 11:35 am, gazzag <gar..._at_jamms.org> wrote:
> On 17 Jun, 17:27, Mtek <m..._at_mtekusa.com> wrote:
>
> > Hi,
>
> > Quick question here, if I have a WHERE clause with an IN condition -
> > WHERE var IN (1, 2, 3)......
>
> > What if the list is dynamic? How would you do that? The column in
> > the table is numeric. Do I have to 'build' the select statement?
>
> > Thanks!
>
> > John
>
> Look into EXECUTE IMMEDIATE and wave scalability goodbye :)
>
> -g

Sounds like a good idea, but can I look through that? Take a look at this query:

  SELECT ol.product_id, co.order_id, co.customer_id, ol.order_item_id, p.code, z.hsc_assignment,

         DECODE(z.salesman_id,NULL,9888,z.salesman_id) salesman_id, z.comments
  FROM customer_order co, order_line ol, product p, zmt_order_info z

  WHERE  ol.order_id = co.order_id
    AND  ol.product_id = p.product_id
    AND  ol.order_item_id = z.order_item_id(+)
    AND  co.date_entered > TO_DATE(p_order_date,'MMDDYYYY')
    AND p.type = 3
UNION
  SELECT ol.product_id, co.order_id, co.customer_id, ol.order_item_id, p.code, z.hsc_assignment,

         DECODE(z.salesman_id,NULL,9888,z.salesman_id) salesman_id, z.comments
  FROM customer_order co, order_line ol, product p, zmt_order_info z

  WHERE  ol.order_id = co.order_id
    AND  ol.product_id = p.product_id
    AND  ol.order_item_id = z.order_item_id(+)
    AND p.type = 3
    AND co.order_id IN (............)
    AND co.customer_id = p_customer_id;

I will need to loop through this CURSOR or SELECT many times. And, the IN clause goes where the ........ are located.

So, is all of this supposed to use an EXECUTE IMMEDIATE statement? Can I loop using that???

Thanks!

John Received on Tue Jun 17 2008 - 11:56:15 CDT

Original text of this message