0

I am trying to compare the data between two fields in Cognos BI that are coming from two different datasources. So, I ultimately want the dataset in which said fields are distinct, i.e they do not match.

The fields are, say "[Query1].[Organization ID]" and "[Query2].[CP_HomeOrg]". When running a joined query (using outer joins) , I see something like this:

Employee ID | Organization ID | CP_HomeOrg

  1. 01 | Value1 | Value1
  2. 02 | Value2 | Value2
  3. .
  4. .
  5. .
  6. 0k | (null) | Valuek
  7. 0k+1 | Valuek+1 | Valuek+1
  8. .
  9. .

So, I proceed to write a filter as "[Organization] <> [Query2].[CP_HomeOrg]" but that does not work. It returns a blank dataset. For some reason it does not pick up the (null) values on the second column above.

I went ahead and did a cast( ) statement on the filter, casting as datatype varchar, but that does not help.

(Note: I checked the datatype in Cognos for the Organization ID and it is character of length 16.)

Can anyone please assist?

Vegeta the Prince of Saiyans
  • 113
  • 9
  • In a database you'd need to convert the NULL to something like a blank space before comparing it. To put it another way, Both of these expressions are false: `NULL = NULL`, `NULL <> NULL`. So in SQL Server you would do something like `ISNULL([Organization ID],'') <> ISNULL([CP_HomeOrg],'')`. Have a look for a similar function in Cognos. – Nick.Mc Feb 11 '16 at 02:46
  • I've gone ahead and leveraged the coalesce( ) function in Cognos BI, which does the same thing and seems to be working. Thanks for the hint! – Vegeta the Prince of Saiyans Feb 11 '16 at 04:19

0 Answers0