Subject | RE: [firebird-support] Recursive CTE question |
---|---|

Author | Svein Erling Tysvær |

Post date | 2015-04-08T07:16:52Z |

>Can the question be rephrased as you being interested in which sets are identical, Elias? If I understand things correctly,Sorry, the above select returns equal sets, but also sets where B is a superset of A. You need to add

>I would assume "double negation" to be the "simple" answer you're looking for:

>

>with tmp(id) as

>(select distinct id from CRITICALPARAMVALS)

>

>select t.id, t2.id

>from tmp t

>join tmp t2 on t.id < t2.id

>where not exists(select * from CRITICALPARAMVALS a

> where t.id = a.id

> and not exists(select * from CRITICALPARAMVALS b

> where t2.id = b.id

> and a.PARAM=b.param and (exists (select isequal from TEST_FLOAT_EQ(a.val, b.val, 1e-5) where ISEQUAL=1))))

and not exists(select * from CRITICALPARAMVALS a

where t2.id = a.id

and not exists(select * from CRITICALPARAMVALS b

where t.id = b.id

and a.PARAM=b.param and (exists (select * from TEST_FLOAT_EQ(a.val, b.val, 1e-5) where ISEQUAL=1))))

for it to only find identical sets.

Needless to say, such a query can be a bit slow with huge tables. Moreover, I don't know how TEST_FLOAT_EQ is implemented or why you use this rather than

a.val BETWEEN b.val - 0.00001 and b.val + 0.00001

HTH,

Set