Cognos 7.4.1 Selecting from the same field

[Sidney786]

Hi

I am trying to develop a report where status 6 is not present for an account. An account can multiple statuses, but I only want to show acc with no status 6

i.e.

Account Status
=============================
0001 1
0001 2
0001 6
0002 4
0003 1
0003 2
0003 4
0003 6
0004 6

so I will only expect account 0002 to be returned.

Any anyone help?

Sid

 

[blom0344]

Use a filter on status ??

Ties Blom

 

[Sidney786]

Hi

That won't work, because an account may have mulitple statuses, it will return accounts with status 6 where they have other status assigned. I only want to return accounts that don't have status 6 assigned.

Any thoughts?

Sid

 

[blom0344]

I guess this is Impromptu? Not at all familiar with. Does Impromptu allow local joins? It would than be a matter of joining your original query with one like:

 SELECT DISTINCT ACCOUNT FROM SOMETABLE WHERE ACCOUNT NOT IN (SELECT ACCOUNT FROM SOMETABLE WHERE STATUS = 6)

Ties Blom