Max string array size for a DW agrument?

[Ken Metz]

PB 9.0.2, Oracle 9.2.0.6

I use a string array for an argument to a datawindow.

On occasion I get an error something like: 'max array size 1000'

Or maybe more like 'maximum number of arguments in argument list is 1000'

This only happens if the user has not narrowed their selection in a
criteria window.

Is there a way around this?

TIA

Ken Metz
Kansas City Power & Light

[Jerry Siegel]

It's an Oracle limit on the number of items in a list.
Maybe you can code the SQL with a sub-select
where key_column in (SELECT key_column FROM your_table WHERE same criteria
you used to get the list of keys)

"Ken Metz" <kennet...@kcpl.com> wrote in message
news:434bf5af$1@forums-2-dub...

[Boris Gasin[TeamSybase]]

The workaround would probably involve adding another arg and changing
the where clause. Could you post a code sample ?

On 11 Oct 2005 10:29:13 -0700, "Ken Metz" <kennet...@kcpl.com>
wrote:

[Doug Porter]

you could also try the str2tbl function shown in this thread by Oracle DB guru Tom Kyte:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:210612357425#28960621306776

Doug Porter
DailyAccess Corporation

"Ken Metz" <kennet...@kcpl.com> wrote in message news:434bf5af$1@forums-2-dub...

[Ken Balakrishnan]

Yet another option is to retrieve the records in batches of 1000, returning
2 from the retrievestart event to preserve the existing records.

Of course the easiest solution, if you can get away with it, is to just
alert the user that there's a 1000-record limit and cut off the array at
that point.

Ken

"Doug Porter" <doug_porterATdailyaccessDOTnospamDOTcom> wrote in message
news:434c0c0e$1@forums-2-dub...

[Ken Metz]

Thanks, I got the answer I needed.
What I am trying to populate is a selection list.
The user clicks on a check box for the jobs they want to select.
1000 is way too long a list anyway.

I don't know if this is any help or not:

SQL> desc table_one;
Name Null? Type
----------------------------------------- -------- ------------------
key1 VARCHAR2(8)
key2(same as key1, different format) VARCHAR2(8)
col1 VARCHAR2(4)
col2 VARCHAR2(1)
col3 VARCHAR2(2)
col4_hrs NUMBER(9,2)
col5_hrs NUMBER(9,2)
col6_hrs NUMBER(9,2)
DATE_RECEIVED DATE
col7 VARCHAR2(1)
col8 VARCHAR2(6)
col9

The user can select:
option from col1 and/or
option from col2 and/or
option from col3 and/or
option from col7 and/or
option from col8 and/or
option from col9 and/or
summation of hours limit for col4_hrs
(first x records that col4 hours sum to some number input by the user)
and/or
summation of hours limit for col5_hrs and/or
summation of hours limit for col6_hrs and/or
records within a date range

Instead of trying to work that into a single SQL,

I do a select based on cols 1,2,3,7,8,9 first.

Then sort that list (datastore) depending on hrs column entered.

The do a loop to get the key1 list that
matches the date range or maxes out the hours total
requested or maxes out the total number of jobs requested by the user.

Then, I use that list (array) of key1 values in another
select against table_one, joined with one other table:

dw_report.Retrieve(is_key1_array[])

Since I now know this is an Oracle error, I can prevent the list from
exceeding 1000.

Ken Metz

"Boris Gasin[TeamSybase]" <nospambgasinatdynamictechgroupdotcom@> wrote in
message news:hj2ok1hrb56gue1hn...@4ax.com...

[Terry Dykstra]

Instead of using a massive IN, you could use EXISTS. Something like :

select * from mytable m
where exists (select something from anothertable a
where m.colx = a.colz)

--
Terry Dykstra (TeamSybase)
http://powerbuilder.codeXchange.sybase.com/
http://www.pb9books.com
product enhancement requests:
http://my.isug.com/cgi-bin/1/c/submit_enhancement

"Ken Metz" <kennet...@kcpl.com> wrote in message

news:434c16b4$1@forums-2-dub...