Hi everyone!

This might not be a straightforward Firebird question, but I'm hoping there's a feature I'm unaware of that can help me beyond plain-vanilla SQL.

I have two tables.  The first is a list of names of "critical parameters," and the second relates certain object IDs, critical parameter names, and critical parameter values:

CREATE TABLE CRITICALPARAMS
(
PARAM Varchar(32) NOT NULL,
INDX INTEGER NOT NULL,
CONSTRAINT PK_CRITICALPARAMS_1 PRIMARY KEY (PARAM),
CONSTRAINT UNQ_CRITICALPARAMS_1 UNIQUE (INDX)
);

CREATE TABLE CRITICALPARAMVALS
(
ID INTEGER NOT NULL,
PARAM Varchar(32) NOT NULL,
VAL Float NOT NULL,
CONSTRAINT PK_CRITICALPARAMVALS_1 PRIMARY KEY (DATAPOINTHASH,PARAM)
);

Let's suppose we have a four-dimensional space:

insert into CRITICALPARAMS values ('a', 1);

insert into CRITICALPARAMS values ('b', 2);

insert into CRITICALPARAMS values ('c', 3);

insert into CRITICALPARAMS values ('foo', 4);

...and a handful of objects in that space:

insert into CRITICALPARAMVALS values (1, 'a', 0.0);

insert into CRITICALPARAMVALS values (1, 'b', 0.0);

insert into CRITICALPARAMVALS values (1, 'c', 2.0);

insert into CRITICALPARAMVALS values (1, 'foo', 99.0);

insert into CRITICALPARAMVALS values (2, 'a', 0.0);

insert into CRITICALPARAMVALS values (2, 'b', 0.0);

insert into CRITICALPARAMVALS values (2, 'c', 2.0);

insert into CRITICALPARAMVALS values (2, 'foo', 99.0);

insert into CRITICALPARAMVALS values (3, 'a', 0.0);

insert into CRITICALPARAMVALS values (3, 'b', 0.0);

insert into CRITICALPARAMVALS values (3, 'c', 1.0);

insert into CRITICALPARAMVALS values (3, 'foo', 98.0);

insert into CRITICALPARAMVALS values (4, 'a', 0.0);

insert into CRITICALPARAMVALS values (4, 'b', 0.0);

insert into CRITICALPARAMVALS values (4, 'c', 1.0);

insert into CRITICALPARAMVALS values (4, 'foo', 98.0);

insert into CRITICALPARAMVALS values (5, 'a', 0.0);

insert into CRITICALPARAMVALS values (5, 'b', 0.0);

insert into CRITICALPARAMVALS values (5, 'c', 2.0);

insert into CRITICALPARAMVALS values (5, 'foo', 98.0);

The problem is to partition the critical parameter space, grouping together all object IDs that have the same parameter values.  We can think of using a "seed" object ID, and asking what other IDs belong to the same partition as the seed object.

In our example, objects 1 and 2 form a partition, 3 and 4 form another, and 5 forms a third.  All five objects are equal in the critical parameters a and b, but differ in parameters c and foo.

Is there any way to solve this using plain-vanilla SQL?  How about a recursive CTE?

I've solved the problem crudely, using EXECUTE STATEMENT in a stored procedure, looping through the seed's critical parameter values and manually constructing a big SQL statement with as many WHERE clauses as critical parameters, but that solution doesn't scale as I go up to around 500-1000 critical parameters (or more!).

My current attempt has petered out at the following point -- I first define a view that can give me a partition along a single critical parameter (TEST_FLOAT_EQ is a selectable stored proc that compares two floats for 'good enough!' equality):

CREATE VIEW VGROUPIDBYPARAM (SEEDID, GROUPMEMBERID, CRITPARAMINDX)
AS
select a.id as seedid, b.id as groupmemberid, c.INDX as critparamindx
from CRITICALPARAMVALS a join CRITICALPARAMVALS b
on a.PARAM=b.param and (exists (select isequal from TEST_FLOAT_EQ(a.val, b.val, 1e-5) where ISEQUAL=1))
join CRITICALPARAMS c on b.param=c.PARAM;

...and then I want to use the VGROUPIDBYPARAM view inductively, in something like the following partially-complete select:

SELECT a1.SEEDID, a6.GROUPMEMBERID
FROM VGROUPIDBYPARAM a1 join VGROUPIDBYPARAM a2 on a1.SEEDID=a2.SEEDID and a1.GROUPMEMBERID=a2.GROUPMEMBERID
join VGROUPIDBYPARAM a3 on a1.SEEDID=a3.SEEDID and a2.GROUPMEMBERID=a3.GROUPMEMBERID
join VGROUPIDBYPARAM a4 on a1.SEEDID=a4.SEEDID and a3.GROUPMEMBERID=a4.GROUPMEMBERID
join VGROUPIDBYPARAM a5 on a1.SEEDID=a5.SEEDID and a4.GROUPMEMBERID=a5.GROUPMEMBERID
join VGROUPIDBYPARAM a6 on a1.SEEDID=a6.SEEDID and a5.GROUPMEMBERID=a6.GROUPMEMBERID

...
where a1.CRITPARAMINDX=1
and a2.CRITPARAMINDX=2
and a3.CRITPARAMINDX=3
and a4.CRITPARAMINDX=4
and a5.CRITPARAMINDX=5
and a6.CRITPARAMINDX=6
...

At the end of this inductive process (which I'm hoping a recursive CTE can imitate), the only surviving records that made it through the pile of JOINS have group member ID's belong to the same partition as the seed ID.

Many thanks to anyone that can help me solve this efficiently!

Elias Sabbagh
Victor Technologies, LLC
ehs AT sabbagh.com