Set Analysis nulling itself

[#1]

I need to extract data from two tables for a KPI with:

- funcs may have several stats

- stats may have XPTO on a field (ie.: asda sdXPTO adasd)

I want those funcs that have at least ONE stats record that contains 'XPTO' AND none that doesn't contain 'XPTO'

In SQL it would be something like:

SELECT distinct
f.num_seq from func f, status s
where
f.funcid = s.funcid
and
f.func = 'ABC'
and num_seq in (
SELECT
f.num_seq from func f, status s
where
f.funcid = s.funcid
and
f.func = '1A.1.1.1.10.3.5.3.'
and s.spec like '*XPTO*'
)
and num_seq not in (
SELECT
f.num_seq from func f, status s
where
f.funcid = s.funcid
and
f.func = '1A.1.1.1.10.3.5.3.'
and s.spec not like '*XPTO*'
)

I was trying with Set Analysis with this:

{$<SPEC={'*XPTO*'}>-<~SPEC={'*XPTO*'}>}

But apparently the two sets are nulling each other.

Any thoughts?

[#2]

So here's some more information and an example that might help you to help me :).

I've made a file that has just some minor info but that allows me to do the tests that I need. The file is attatched to this reply.

The problem is that all the combination operators are working like they showld except for the one that i need in this case.

I want to have a count of the Funcs tha have booth a stat with risk and another one without risk, but it simply isn't working. The value that showld be showing in the * column showld be 2 but it's allways 0.

Can somebody help please?

Thanks in advance!

[johnw · Accepted solution]

Thanks for the sample file. Unfortunately, I can't figure out why it's failing. It seems simple, and seems like it should work. I'm probably just missing something. But there's a small chance that it's a bug, and it happens because some programmer thought they were being clever, and that if you selected A and then selected NOT A, that these sets were mutually exclusive, so it doesn't even bother with the sets. And that would be true... IF we were just talking about a single field, but not when we're talking about related data, since in your case, for instance, you have functions that are related both to A and to NOT A, and so should still be in the intersection of the two sets.

The symmetric difference (/) appears to work properly, though, which gives us a workaround, since the whole minus the symmetric difference is the intersection:

count(distinct funcId) - count({<description*={"*risk*"}> / <description-={"*risk*"}>} distinct funcId)