I think the problem here is the fact that the subranging and the
"list"-ing part are being mixed.

As I see it, they're separate ideas that don't need to be considered
together.

Issue 1: The fact that "In" clauses are evaluated as if they were a
chain of "OR's", as in

Where FieldValue in (1,2,3,4)

Becomes

Where Fieldvalue=1 or FieldValue=2 or FieldValue=3 or Fieldvalue=4.

This makes it impossible to pass the "(1,2,3,4)" part as a parameter in
a prepared SQL statement.

I think what is being proposed is the ability to say

"Where FieldValue in list (:LISTPARAM)"

And then assign :LISTPARAM the value of "1,2,3,4" as a string.

This would allow a user to specify the "1,2,3,4" part (or "1,3,4" or
"1,2,3,4,5") as a parameter string in a filter dialog box without the
program having to compose the query "on the fly" by concatenating a
bunch of strings.

Issue 2: It might be nice (i.e., "syntactic sugar") to be able to say
"1,2,3,4" with "1-4", or more likely, to avoid 1-4 looking like "-3",
with "1..4". The problem is that to turn 1,3..5,7 into something useful,
it has to become "where FieldValue=1 or Fieldvalue between 3 and 5 or
fieldvalue=7. That seems pretty difficult to do as a general case.

Personally, I'd love to see the first feature implemented. I've had
cases where the ability to specify an "In :List" as a parameter would
have been very useful.

-----Original Message-----
From: Firebird-Architect@yahoogroups.com
[mailto:Firebird-Architect@yahoogroups.com] On Behalf Of Jim Starkey
Sent: Sunday, December 03, 2006 12:09pm
To: Firebird-Architect@yahoogroups.com
Subject: Re: [Firebird-Architect] IN LIST


Roman Rokytskyy wrote:

>> Hence, a way to pass indeterminate number of intervals is very great:
>> select * from t where id in list '5-10,20-30,50'
>>
>
> How is it supposed to work with the parameters? If it doesn't, than it
> requires constructing the statement on the fly which does not really

differs

> from a combination of BETWEEN and OR predicates.
>
>

Let me take that a bit further. Parameterizing a statement allows
re-use of cached statements, which is a huge gain in high volume
systems. The Vulcan DSQL implementation was designed to support a
compiled statement cache, though the cache itself has not yet been
implemented. Anything that encourages use of generated statements works

against this. Beefing up the IN LIST syntax to encourage generated
rather than parameterized statements will work against this.

Personally, I haven't seen substantial case for ranges within an IN
LIST. Is it intended only as syntactic sugar, does it allow a user to
do something he or she otherwise couldn't do, or is a convenience
construct?

Once again, this is an example of solution presented without a problem.

If we knew what problem this statement was intended to solve, it would
be a great deal easier to evaluate it.

Could we go back to the beginning with a clear statement of a system
requirement?



Yahoo! Groups Links