Good Morning Cognos Experts,
I'm using Cognos 11.2.3 to Join two queries together using the only data item that the queries have in common.
However, though these character data items are known to contain the exact same values, they are in fact from two different tables.
The queries run successfully alone. But when I attempt to run the JOIN query which is using only "=" comparison of one data item from each query result set, I get this error message:
"Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1_CS_AS" in the equal to operation.
I do not have access to Framework Manager to change the source database. So, I'm looking for ideas of how I might be able to either manipulate the data items prior to the JOIN OR use an expression to populate their values into separate new data items (within their respective queries) which I could then JOIN instead.
Thank You.
What RDBMS? If it's MS SQL Server -- https://learn.microsoft.com/en-us/sql/t-sql/statements/collations?view=sql-server-ver17
So you join looks like
Query1.ColumnA = Query2.ColumnB
Convert one side to the collation of the other side.
Query1.ColumnA COLLATE SQL_Latin1_General_CP1_CS_AS = Query2.ColumnB
Keep in mind that I exerted zero effort to make sure this is correct. You can do your own research to determine which column must be converted to which collation. And I don't know if that can be done directly in a Cognos join. You may need to find another way to make these data sources compatible.
Thanks dougp for the input.
I'll give it a try.
In this case, the data is in Cognos database is SQL (the source originates from an IBM DB2 database).