Error with a Join on Cognos v11.1.13.

[FerdH4]

I've got a situation where I am using four similar key fields from four different tables - all fields are of of similar size and attributes.

I can successfully Join the first two - let's call them FieldA and FieldB.  When I say "successfully, I mean that the Join Query runs without error and the output is correct.

And, I can also successfully Join the second two - FieldC and FieldD.

But when I attempt to Join those two Queries' result sets in a third Join Query - either using FieldA and FieldC, for example or any other combination of two of the four fields - I get this error.

"Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation."

Essentially every field appears to play nicely together until after they have been Joined once.  Then the start banging heads.  I'm not even trying to write in Latin by the way :P

Ideas?
 
 

[cognostechie]

Hi

Don't try 'combination' of fields to decide the join. Join should be decided by the data in the fields. Data will say which field from one table should be joined to which field in another table, either a single field join or a composite join.

Check the data type of those four fields, especially the data types of the fields used in the join.

Ex: If Field1 of Table A is joined to Field3 of Table B and Field2 of Table A is joined to Field4 of Table B then the data type of Field1 and Field3 should be the same. Same applies to Field2 and Field4. If the data type is not the same, you may want to use cast/convert function depending on which database you have.

The error you are getting seems to be generated by the DB, not Cognos.

[FerdH4]

Thanks for the insight.  I'll dig deeper and try the convert (or Cast).