Excuse me, I didnt read the stored proc impossibility...

You could try something like this if you could use expression indices :

SELECT a.PTYGROUPKEY, a.PTYROLE, a.PARTIESKEY
FROM PARTYGROUP a
where ';A5;C123;' like '%;'|| a.PTYROLE||a.PARTIESKEY ||';%'

The first string is the parametized one and the second is the concatenation of the two fields.

--- In firebird-support@yahoogroups.com, "Michael D. Spence" <firebird@...> wrote:
>
> Well, as I said, I can't actually use a stored procedure
> or a UDF.
>
>
>
> > -----Original Message-----
> > From: firebird-support@yahoogroups.com
> > [mailto:firebird-support@yahoogroups.com] On Behalf Of amoradell
> > Sent: Sunday, March 15, 2009 5:02 PM
> > To: firebird-support@yahoogroups.com
> > Subject: [firebird-support] Re: SQL Puzzle of the Day
> >
> >
> > 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
> > > >
> > > >
> > > >
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> > 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
> >
> >
> >
>