Re: # of items in a select

[Albert Howard <starbukk@HOTMAIL.COM>]

I'm not familiar with Maximo, but I do know that "IN" can be very
inefficient as the number of items in the select increases.  If
selecting this way is your only way of establishing what needs to be
printed, you should try establishing some kind of flag on the record
within the database that can be toggled and queried (i.e. print_flag =
'Y'), or building a trigger table to store emplids that are to be
printed, and join it with your table at print time.

>Date:         Wed, 3 Jun 1998 19:07:06 -0400
>Reply-To: SQR-USERS@USA.NET
>From: Sephanie Moxley <Stephanie_Moxley@PEOPLESOFT.COM>
>Subject:      Re: # of items in a select
>To: Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
>
>Randle,
>
>I'm not sure of your specific problem, sounds like there is a SQL
>limitation with one of the products you are using.  As a workaround
though,
>I would just go ahead and use "OR" with your "IN" statement, for
example,
>
>select * from ps_personal_data
>  where (emplid in ('x1', x2', 'x3', ...,'x12')
>  or          emplid in ('x13',...,'x24'));
>
>This should work until you get a fix to the real problem.
>-Stephanie
>
>
>
>
> (Embedded
> image moved   Randle Boyd <rboyd@RUAN.COM>
> to file:      06/03/98 02:45 PM
> pic10495.pcx)
>
>
>
>
>Please respond to SQR-USERS@USA.NET
>
>To:   Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
>cc:    (bcc: Stephanie Moxley)
>Subject:  # of items in a select
>
>
>
>
>Greetings,
>
>We are using NT4, Oracle 7.3, Sqr 3, and Maximo 3.x.
>
>we are having a problem with some of our reports.  The problem is
length
>of the $where statement generated by Maximo when the selected records
>option is chosen.
>
>By choosing 'selected records when running a report from Maximo, Maximo
>uses an 'IN' in the select statement
>
>ex.     select xxxx from yyyy
>        where ddd in ('a1', 'a2', 'a3', ....., 'an')
>
>as opposed to an '=' for current record or no input.
>
>The 'IN' statements work fine with up to 12 items included
>        ... where ddd in ('a1', 'a2', 'a3' ....., 'a12')
>
>But once we have 13 items in the 'IN' statement, we get 'SQR 3722
>Couldn't set up cursor.
>
>Does anyone have an idea of what is causing this, or how to fix it?
>
>
>Thanks in Advance,
>Randle
>Ruan Transportation
>(515) 245-2493
>
>
>


______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com