Convert nvarchar to int to join SQL tables in view

Question

I want to create a view that displays information from two tables connected by fields of different types. One field is nvarchar and the other is int. I know that I need to convert one type to another, but I don’t know how to do it. Any help would be greatly appreciated.

SELECT dbo.co.co_num, dbo.pck_hdr.weight, dbo.STR_ShipTrack.TrackingNumber FROM dbo.co INNER JOIN dbo.pck_hdr ON dbo.co.co_num = dbo.pck_hdr.co_num INNER JOIN dbo.STR_ShipTrack ON dbo.pck_hdr.pack_num = dbo.STR_ShipTrack.Reference1 

Answer 1

Looking at your code, I cannot say what you should do.

The SQL engine will perform automatic conversions for comparison. However, if you decide to convert the character field to an integer, then you get an error.

So just enter your int field in nvarchar:

 cast(IntField as nvarchar(255)) 

Length doesn't matter when comparing nvarchar() .

In your request, you replace:

 ON dbo.pck_hdr.pack_num = dbo.STR_ShipTrack.Reference1 

with:

 ON cast(dbo.pck_hdr.pack_num as nvarchar(255)) = dbo.STR_ShipTrack.Reference1