I write maximum 50 members in the IN Clause
i think it will not be a problem
or i can use the EXISTS clause instead of IN clause.

----- Original Message -----
From: "Helen Borrie" <helebor@...>
To: <firebird-support@yahoogroups.com>
Sent: Tuesday, September 14, 2004 10:31 AM
Subject: Re: [firebird-support] Sql Expression IN Clause Limit


> At 09:07 AM 14/09/2004 +0300, you wrote:
> >Hi,
> >I am using firebird 1.5.1
> >i want to ask a question to firebird users.
> >
> >is there any limit about "IN" clause
> >
> >example
> >======
> >select field1, field2 from table1
> >where
> > (field1 in (param1, param2, param3.... paramN)) and
> > (field2 in (Fparam1, Fparam2, Fparam3.... FparamN))
>
> This isn't a valid use of parameters. The argument for the IN() predicate
> has to be a list of constants. Either, you provide the constants or you
> provide a subsquery that will return that list.
>
>
> >how many params i can write to IN clause
>
> None. IN() takes one argument, a list.
>
> >is there any limit about IN clause
>
> IN(<list of constants>) has a limit of 1024 list members.
>
> IN(<scalar subquery>) is limited by the final length of the plan (when
> expanded), which is 48K bytes. The number of values possible with
"blowing
> up" is therefore dependent on not just the size of the items returned in
> the list but things like the lengths of index names, the amount of other
> stuff in the query, and so for.
>
> Generally, limit your use of IN() for searches predicated on a small set
of
> constants. IN(<subquery>) will be optimised to an EXISTS() predicate
> anyway, so you might as well use this from the start, and save some
> processing cycles.
>
> >and how many characters can be sql expression length
> >is there any limit about sql expression length
>
> Not to an sql expression length, per se, but each SQL statement is limited
> to 64K bytes and they must not cause a plan that exceeds 48 Kb. IN(<list
> of constants>) does have a habit of causing large plans, since each item
in
> the list will generate its own separate usage of any index on the other
> operand in the comparison. It resolves like this
>
> WHERE AColumn in ('a', 'b', 'c', 'd', 'e', ...)
> resolves to
> WHERE AColumn = 'a' OR 'Acolumn = 'b' OR AColumn = 'c' OR ....
>
> with a plan like
> PLAN (ATable INDEX (RDB$PRIMARY7,RDB$PRIMARY7,RDB$PRIMARY7,RDB$PRIMARY7,
...))
>
> ./heLen
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
>