Losing Case Statement Values After a Join

[red0116]

Hi All,

I am fairly new to Cognos, so please bear with  me.

I have found that the data item values I specify using a Case statement are being lost after I join its query to another and bring it into the new query. Let me describe the situation:

I have Query_1 and Query_2. In Query_1, I have a data item called "Validation" which is a Case statement with the following evaluation:

CASE
WHEN
([CountA] > 1) OR ([CountB] > 1) OR ([CountAB]=0)
THEN 'FAIL'
WHEN
([CountA] IS NULL) OR ([CountB] IS NULL) OR ([CountAB] IS NULL)
THEN 'FAIL'
ELSE 'PASS'
END

When I view the tabular data for Query_1, I successfully see that the value 'PASS' or 'FAIL' is the output.

I must now join Query_1 and Query_2 in order to produce Query_3. Query_3 uses Query_1.Validation as one of its data items.

When I run the tabular data for Query_3, the output is no longer 'PASS' or 'FAIL', it is coming out as 1 - regardless of whether the original value was 'PASS' or 'FAIL'

Does anyone know what might be the cause of this? Why are my string values turning into numbers, and why specifically 1? Is it because the expressions in my case statements are number datatypes?

Thanks in advance for your help!

[red0116]

To clarify the data types used in the Case statement:

[CountA] and [CountB] are actually Case statements too with an aggregation rule of COUNT applied

CASE WHEN
Status = 'A'
THEN ID

So I am counting the number of IDs with a status of 'A'; ID is a string

Then I am using this count to evaluate whether an ID is pass or fail in Query_1.validation