Prev: server time reset
Next: Administration Assistant for Windows Startup/Shutdown Options
From: Mtek on 17 Jun 2008 12:27

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
From: gazzag on 17 Jun 2008 12:35
On 17 Jun, 17:27, Mtek <m...(a)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
From: Shakespeare on 17 Jun 2008 12:40

"gazzag" <gareth(a)jamms.org> schreef in bericht
news:d4eb97aa-40e8-468d-b9e7-df0451a39d69(a)x41g2000hsb.googlegroups.com...
On 17 Jun, 17:27, Mtek <m...(a)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

-------------------------------------
Or write a function returning 1 if the value is ok, 0 if not and use

where your_function(var) = 1;


You can't build an index on it, though (not even function based, because the
function is not deterministic)

Shakespeare


From: Mtek on 17 Jun 2008 12:56
On Jun 17, 11:35 am, gazzag <gar...(a)jamms.org> wrote:
> On 17 Jun, 17:27, Mtek <m...(a)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
From: Mtek on 17 Jun 2008 14:55
On Jun 17, 11:56 am, Mtek <m...(a)mtekusa.com> wrote:
> On Jun 17, 11:35 am, gazzag <gar...(a)jamms.org> wrote:
>
>
>
> > On 17 Jun, 17:27, Mtek <m...(a)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


Guess the best thing for me to do is to create an indexes PLSQL
record, run through 2 cursors putting elements into the record table,
then loop through the table to process the records.......

Ho hum....
 |  Next  |  Last
Pages: 1 2
Prev: server time reset
Next: Administration Assistant for Windows Startup/Shutdown Options