COGNOiSe.com - The IBM Cognos Community

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

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.

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.

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

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

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

Quote from: hespora on 27 Sep 2016 03:13:03 AM
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.

Ah. this seems promising - thanks!

Quote from: HalfBloodPrince on 27 Sep 2016 08:18:51 AM
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

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.