Select those in group where field was always empty

[Patrick77]

Hi,

I realize this is more of a SQL question, but I am using it in a Cognos report.

I am trying to select only those IDs where all of the rows for that ID were always blank/empty/null. For example, if these were my data:

ID LastName FirstName MiddleName 01 Smith    Joe    01 Smith    Joe       C 02 Smith    Joe    02 Smith    Joe    03 Smith    Joe    03 Smith    Joe       O 04 Smith    Joe    04 Smith    Joe       Carter    04 Smith    Joe       C 05 Smith    Joe       S 05 Smith    Joe       Sam

I would only want ID = 02, because that ID never had anything in the MiddleName field. This seems like something that is probably pretty easy, but I haven't been able to figure it out.

I thought about using something like

Code Select Expand

Select *
From myDATA
Where
ID Not In (Select DISTINCT ID FROM myDATA
     WHERE MiddleName Like '[A-Z]%')

But " 'Like [A-Z]%' "  does not work as I would expect - everything is returned.  How can this wildcard ([A-Z]%) be used in Cognos SQL?

Thanks for any ideas.

[hespora]

Thats three queries:

First query, only return ID, put a detail filter on [MiddleName] in null
Second query, only return ID, put a detail filter on [MiddleName] is not null
Third query, pull in an "Except" object from the toolbox, and put first query in upper link, second link in lower link.

[HalfBloodPrince]

Try this

create Query calculation as data Item as
if([MiddleName]='') then
(0)
else
(1)
then add detail filter on it as

total ([Data Item] for [ID] )=0

[AnalyticsWithJay]

You could use a Maximum() or Minimum() function on MiddleName. HalfBloodPrince's approach also works well.

[Patrick77]

That's what I was hoping to avoid.  Thanks.

Thats three queries:

First query, only return ID, put a detail filter on [MiddleName] in null
Second query, only return ID, put a detail filter on [MiddleName] is not null
Third query, pull in an "Except" object from the toolbox, and put first query in upper link, second link in lower link.

[Patrick77]

Ah. this seems promising - thanks!

Try this

create Query calculation as data Item as
if([MiddleName]='') then
(0)
else
(1)
then add detail filter on it as

total ([Data Item] for [ID] )=0

[Invisi]

I think even better would be to solve this issue already in your data warehouse. Consider adding a boolean as attribute to your relevant table, so that you can filter on 'Always empty = TRUE' or something similar.

I wonder about your example, I assume the real attribute you ask this for is something else than the existance of middle name. A more relevant example may give me an idea for a more specific suggestion how to solve it.