Re: RE: how to do a variable in-list of numbers?

its kinda slick. i have large tables. if i use the nested table with the cast function, I can use an index.

ill keep it in mind.

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=3748097f.5471527%40newshost.us.oracle.com&rnum=1&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26q%3Doracle%2Bcast%2Bpl%252Fsql
>
> From: "Igor Neyman" <ineyman_at_perceptron.com>
> Date: 2003/08/29 Fri PM 03:34:34 EDT
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Subject: RE: how to do a variable in-list of numbers?
>
> This method works; I have some code using it.
> The only problem, it could be slow (on large tables), because index on
> <code_column> will not help.
>
> Igor Neyman, OCP DBA
> ineyman_at_perceptron.com
>
>
>
> -----Original Message-----
> Chelur, Jayadas {PBSG}
> Sent: Friday, August 29, 2003 10:45 AM
> To: Multiple recipients of list ORACLE-L
>
> SELECT <columns>
> FROM <sometable>
> WHERE INSTR(','||<list_of_comma_separated_codes>||',' ,
> ','||<code_column>||',' ) > 0;
>
>
> (1). Concatenate Commas at BOTH ENDS of the string containing
> different code values. e.g. If the string is
> '101,102,554,336,678,301,201,199' , the concatenate commas
> on both ends to make it ',101,102,554,336,678,301,201,199,'
>
> this is to make sure that EVERY code ( including the first
> and the LAST ) confirms to the pattern <,><value><,>
>
> (2) For each row in the table, use INSTR() function to see whether
> the pattern <,><value of columna><,> is CONTAINED in the string
>
> INSTR() is used so that pattern matching can be used instead of
> any range checking etc on the string. The actual code values in
> the string can be IN ANY ORDER.
>
> -----Original Message-----
> Sent: Friday, August 29, 2003 10:32 AM
> To: Multiple recipients of list ORACLE-L
>
>
> I need to do an insert select of the form
>
> insert into tab2
> select col1
> from tab1
> where col2 in (inlist of numbers);
>
> I do not know how many values will be in my inlist at runtime. With
> strings
> I just build a big string. How do I build an 'inlist' of numbers at
> runtime?
>
>
> Im using a cursor to determine which values need to be added to my
> inlist. I
> think I can do some kind of cast, but im not familiar with it.
>
> Im on 8i. I do not want to j ust run this inside my cursor. It could
> then
> execute 300-400 times and will run all day.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: <rgaffuri_at_cox.net
> INET: rgaffuri_at_cox.net
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Chelur, Jayadas {PBSG}
> INET: Jayadas.Chelur_at_pepsi.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Igor Neyman
> INET: ineyman_at_perceptron.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <rgaffuri_at_cox.net
  INET: rgaffuri_at_cox.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).