The LIST() solution turned out to have serious issues, so
I adapted your method and it works just fine.

I didn't make this clear in the original posting, but
since this is part of an ETL batch step for a data mart,
there's no possibility (and no reason) for more than one
instance of the loader to be running, ever. So I didn't
actually need the generator and key field in the MYREQUESTS
table. I was also worried about how all of this would be
affected by the fact that commits are done every 1000 or so
inserts, but all seems well.

Do you think there would be any advantage to using a
temporary table for MYREQUESTS?

Thanks for your help!

Michael D. Spence
Mockingbird Data Systems, Inc.

--- In firebird-support@yahoogroups.com, "Michael D. Spence" <firebird@...> wrote:
>
> Set,
>
> I solved it a different way, but since it depends on the
> LIST() builtin I'm not particularly happy with it. And for
> reasons I don't understand, the java SQL stuff changes the
> size of one of the parameters arbitrarily, so that I get
> truncation errors when I try to pass parameters.
>
> I may investigate you method a bit tomorrow. The trouble is
> that this could potentially be a big table over time (hence
> the BIGINTs for the keys instead of INTS), so I'm not sure
> how much time the outer joins might take even with appropriate
> indexes.
>
> Anyway, Thanks!
>
> Michael D. Spence
> Mockingbird Data Systems, Inc.
>
>
> > -----Original Message-----
> > From: firebird-support@yahoogroups.com
> > [mailto:firebird-support@yahoogroups.com] On Behalf Of Svein
> > Erling Tysvær
> > Sent: Monday, March 16, 2009 8:25 AM
> > To: firebird-support@yahoogroups.com
> > Subject: [firebird-support] Re: SQL Puzzle of the Day
> >
> >
> > Hi Michael, at first I didn't see how this could be a puzzle
> > at all, but upon reading a few replies, I understood that you
> > wanted an exact match between two sets.
> >
> > Well, I have a different solution to your problem that
> > includes one additional table. This is done for two reasons:
> >
> > 1) Make it possible to use the same statement every time
> > 2) Save myself from complexity that wreak havoc with my brain cells
> >
> > First, some DDL:
> >
> > CREATE GENERATOR MYREQUESTGEN;
> >
> > CREATE TABLE MYREQUESTS
> > (
> > MYREQUESTKEY INTEGER NOT NULL,
> > PTYROLE Varchar(6) NOT NULL,
> > PARTIESKEY Bigint NOT NULL
> > );
> >
> > You may want to create indexes, I don't know how often you
> > will perform your query, so they may or may not be required.
> >
> > (Normally, I would add another generator as well together
> > with an ID column for a primary key and use a before insert
> > trigger to fill it in, but this is not of importance for your
> > problem, so I left it out.)
> >
> > Now, each time you want to check for an identical set, start
> > by getting the next value of MYREQUESTGEN from your Java
> > application, and then insert your list of values into
> > MYREQUESTS, all with the same value as you got from MYREQUESTGEN.
> >
> > Then you can issue this SELECT, again using the value you got
> > from MYREQUESTGEN as your parameter:
> >
> > SELECT DISTINCT pg.PTYGROUPKEY
> > FROM PARTYGROUP pg
> > JOIN MYREQUESTS mr ON pg.PTYROLE = mr.PTYROLE
> > AND pg.PARTIESKEY = mr.PARTIESKEY
> > WHERE mr.MYREQUESTKEY = :MyRequestKey
> > AND NOT EXISTS(
> > SELECT null
> > FROM PARTYGROUP pg2
> > LEFT JOIN MYREQUESTS mr2 ON pg2.PTYROLE = mr2.PTYROLE
> > AND pg2.PARTIESKEY = mr2.PARTIESKEY
> > AND mr.MYREQUESTKEY = mr2.MYREQUESTKEY
> > WHERE pg.PTYGROUPKEY = pg2.PTYGROUPKEY
> > AND mr2.MYREQUESTKEY is null)
> > AND NOT EXISTS(
> > SELECT null
> > FROM MYREQUESTS mr3
> > LEFT JOIN PARTYGROUP pg3 ON pg3.PTYROLE = mr3.PTYROLE
> > AND pg3.PARTIESKEY = mr3.PARTIESKEY
> > AND pg.PTYGROUPKEY = pg3.PTYGROUPKEY
> > WHERE mr.MYREQUESTKEY = mr3.MYREQUESTKEY
> > AND pg3.PTYGROUPKEY is null)
> >
> > What this select basically does, is to return all PTYGROUPKEY
> > for which there's no record in either of the sets that isn't
> > also in the other. I think this double negation would be the
> > way to find an identical set (or maybe Firebird has some
> > newish operator that I do not know about?).
> >
> > I have tried this SQL on a tiny set of records, and I did get
> > the result I expected. Whether this will be a feasible
> > solution in your application or not, I expect to depend on
> > the selectivity of PTYGROUPKEY.
> >
> > HTH,
> > Set
> >
> > --- In firebird-support@yahoogroups.com, Michael D. Spence 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
> >
> >
> >
>