I found a solution with a stored procedure .

It has one parameter with a list of values separated with a character.

I put the list on a fixed format (spaces and zeros to pad) but it could be changed.

set term ^;

CREATE PROCEDURE RETRIEVEPTYROLE(
LISTVALUES VARCHAR(1024))
RETURNS (
PTYGROUPKEY INTEGER)
AS
DECLARE VARIABLE zlist varchar(1024);
DECLARE VARIABLE zlistitem varchar(20);
declare variable zii integer;
declare variable zptyrole varchar(6);
declare variable zpartieskey integer;
BEGIN
zii=1;
zList = trim(ListValues);
while (zlist <> '') do
BEGIN
zlistitem = substring(:zLIST from 1 for 20);
zptyrole = trim(substring(:zlistitem from 1 for 6));
zpartieskey = cast(substring(:zlistitem from 7 for 14) as integer);
for select p.PTYGROUPKEY
from partygroup p where ptyrole=:zptyrole and partieskey = :zpartieskey
into :PTYGROUPKEY do
suspend;
zii = zii + 21;
zlist = substring(:zlist from :zii for 1024);
end
END^
set term ;^

and to call it :

SELECT p.PTYGROUPKEY
FROM RETRIEVEPTYROLE('A 00000000000005²B 00000000000132') p

Tested with Firebird 2.1

I didn't use any UDF but it can be changed.

The inner query uses indices.

Regards

Alexandre
--- In firebird-support@yahoogroups.com, "Michael D. Spence"
<firebird@...> wrote:

>
> This is better because it can be parameterized,
> but still has some of the same issues. Also,
> suppose the values aren't really BETWEEN two
> values. Or rather, while there's always an
> upper and lower bound, all of the values in
> between may not be represented.
>
> To further illustrate my quandary, let's ignore
> PARTIESKEY for now (or assume that it always has
> the necessary values). Suppose that PTYROLE can
> have the values A,B,C,D,E.
>
> Also, suppose I already have group 1 with B, C, D
> and group 2 with B, D.
>
> Now suppose I have formed another group x, containing
> B and D and I want to see if it's already represented
> in the database. Using BETWEEN will fetch both group
> 1 and group 2.
>
> Also, generating a series of WHERE clauses:
>
> WHERE PTYROLE = B
> OR PTYROLE = D
>
> will do the same thing. What I really need, I
> think, is some way to include a count:
>
> WHERE (PTYROLE = B
> OR PTYROLE = D)
> AND (<number of items returned> = 2)
>
> but I don't see a way to do this. A UDF or a stored
> procedure might work but neither is allowed by the
> situation.
>
> And yes, I do have indices on PTYROLE and PARTIESKEY
> because this is a largish dataset (~100000 rows)
> and over time could be very much larger (10E7 rows).
>
> This table is loaded into memory at startup, but of
> course if it grows sufficiently, I'll have to do the
> LRU thing, so when I get a cache miss, I was hoping
> for a single, quick select to find the target data
> in the database proper. So generating the SQL each
> time then post-processing the result would only be
> done on a cache miss, which isn't as bad as it might
> be.
>
> Thanks, though.
>
> Michael D. Spence
> Mockingbird Data Systems, Inc.
>
>
> > -----Original Message-----
> > From: firebird-support@yahoogroups.com
> > [mailto:firebird-support@yahoogroups.com] On Behalf Of amoradell
> > Sent: Saturday, March 14, 2009 8:48 AM
> > To: firebird-support@yahoogroups.com
> > Subject: [firebird-support] Re: SQL Puzzle of the Day
> >
> >
> >
> > If you can filter with your app, you could try :
> >
> > SELECT PTYGROUPKEY, PTYROLE, PARTIESKEY FROM PARTYGROUP
> > WHERE (PTYROLE between :minptyrole and :maxptyrole)
> > and (PARTIESKEY BETWEEN :minpartieskey and maxpartieskey)
> >
> > You have to find the four values and pass them to you query
> > and therefore filter the pairs in your app.
> >
> > And do you have index on PtyRole ? and PartiesKey ?
> >
> > It could be significant on large datasets.
> >
> > Regards
> >
> > Alexandre
> >
> > --- In firebird-support@yahoogroups.com,
> > "mspencewasunavailable" <firebird@> wrote:
> > >
> > > I have the following table:
> > >
> > > CREATE TABLE PARTYGROUP
> > > (
> > > PTYGROUPKEY Bigint NOT NULL,
> > > PTYROLE Varchar(6) NOT NULL,
> > > PARTIESKEY Bigint NOT NULL,
> > > CONSTRAINT PK_PARTYGROUP_1 PRIMARY KEY (PTYGROUPKEY,PTYROLE)
> > > );
> > >
> > >
> > > For each PTYGROUPKEY, there are 1 or more unique
> > PTYROLE,PARTIESKEY combinations. In my Java code, I have a
> > list of these and would like to find the PTYGROUPKEY that
> > exactly matches this list. I know I can do something like
> > >
> > > SELECT PTYGROUPKEY FROM PARTYGROUP
> > > WHERE (PTYROLE = 'A' AND PARTIESKEY = 5)
> > > OR (PTYROLE = 'B' AND PARTIESKEY = 132)
> > > OR ....
> > >
> > > But (a) I'd rather use a prepared query and (b) this
> > doesn't exclude cases where my list is actually a smaller
> > subset of the set I retrieve with this statement.
> > >
> > > Thanks,
> > >
> > > Michael D. Spence
> > > Mockingbird Data Systems, Inc.
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> > Visit http://www.firebirdsql.org and click the Resources item
> > on the main (top) menu. Try Knowledgebase and FAQ links !
> >
> > Also search the knowledgebases at http://www.ibphoenix.com
> >
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> > Yahoo! Groups Links
> >
> >
> >
>