I am trying to create a relationship between two tables,

Table A, Column A(Data type VARCHAR30)
Table B, Column B(Data type NVARCHAR255)

When I try to create a relationship between these two tables/columns as part of the Access Definition, its throwing an error saying data type mismatch.

Appreciate any inputs on how to handle this.


Thanks
Sdk

------------------------------
Optim Learner
------------------------------

#InfoSphereOptim
#Optim

VARCHAR & NVARCHAR are different datatypes under the covers, so I'm not surprised Optim won't allow the relationship. We have gotten around some issues like this by creating a table VIEW and then casting the NVARCHAR column to be VARCHAR in the View Definition.

Then create the Optim relationship against the View since the column in question will now be cast as VARCHAR. You just need to ensure you allow enough permissions against the View if you intend to delete data from the underlying table. 

Not the prettiest solution, but it should work. Good luck...



------------------------------
Keith Tidball
Progressive Insurance
------------------------------

Thank you Keith. Quick clarification  as I have never done this before​.

"We have gotten around some issues like this by creating a table VIEW and then casting the NVARCHAR column to be VARCHAR in the View Definition"

My understanding of the above statement is that you created a view on the entire table and this was done within the Database? Then this view is included as a table(instead of the actual table) in the list of tables as part of the Access Definition?


------------------------------
Optim Learner
------------------------------

Original Message:
Sent: Mon April 13, 2020 03:22 PM
From: Keith Tidball
Subject: How are 'nvarchar' DataTypes handled in Optim

VARCHAR & NVARCHAR are different datatypes under the covers, so I'm not surprised Optim won't allow the relationship. We have gotten around some issues like this by creating a table VIEW and then casting the NVARCHAR column to be VARCHAR in the View Definition.

Then create the Optim relationship against the View since the column in question will now be cast as VARCHAR. You just need to ensure you allow enough permissions against the View if you intend to delete data from the underlying table. 

Not the prettiest solution, but it should work. Good luck...



------------------------------
Keith Tidball
Progressive Insurance
------------------------------

Creating a View is done within the DBMS and then, yes, you reference the View and not the actual table in your Optim AD. You will need to check off the Optim product option to allow you to create Optim relationships against Views, too. 


------------------------------
Keith Tidball
Progressive Insurance
------------------------------

Original Message:
Sent: Tue April 14, 2020 02:49 PM
From: Optim Learner
Subject: How are 'nvarchar' DataTypes handled in Optim

Thank you Keith. Quick clarification  as I have never done this before​.

"We have gotten around some issues like this by creating a table VIEW and then casting the NVARCHAR column to be VARCHAR in the View Definition"

My understanding of the above statement is that you created a view on the entire table and this was done within the Database? Then this view is included as a table(instead of the actual table) in the list of tables as part of the Access Definition?


------------------------------
Optim Learner
------------------------------


Original Message:
Sent: Mon April 13, 2020 03:22 PM
From: Keith Tidball
Subject: How are 'nvarchar' DataTypes handled in Optim

VARCHAR & NVARCHAR are different datatypes under the covers, so I'm not surprised Optim won't allow the relationship. We have gotten around some issues like this by creating a table VIEW and then casting the NVARCHAR column to be VARCHAR in the View Definition.

Then create the Optim relationship against the View since the column in question will now be cast as VARCHAR. You just need to ensure you allow enough permissions against the View if you intend to delete data from the underlying table. 

Not the prettiest solution, but it should work. Good luck...



------------------------------
Keith Tidball
Progressive Insurance