14

I want to create a view which will display the info from two tables joined by different type fields. The one field is nvarchar and the other one is int. I know i need to convert one type in the other but 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
Charalampos Afionis
  • 193
  • 1
  • 1
  • 11

2 Answers2

31

Looking at your code, I can't tell either what you should do.

The SQL engine will do automatic conversions for the comparison. However, if might decide to convert the character field to an integer -- and then get an error.

So, just cast your int field to nvarchar:

cast(IntField as nvarchar(255))

The length doesn't matter for an nvarchar() comparison.

In your query, you would 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
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I apologize for the incomplete description. The dbo.pck_hdr.pack_num is the int and the dbo.STR_ShipTrack.Reference1 is the varchar(25) field. Can you please show me how I should put the code together? – Charalampos Afionis Apr 29 '13 at 15:42
1

If you're casting from a string to an integer there's always a possibility you'll have a non-numeric content in the source column.

INNER JOIN Tab t on t.ColId = CONVERT(int, CASE WHEN ISNUMERIC(strCol) = 1 THEN strCol ELSE '-1' END)

Similar to @gordon-linhoff answer. But doing the comparison as int rather than string.

David McEleney
  • 3,397
  • 1
  • 26
  • 32