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
>
>
>