Concatenation problem: two integers and a char

[mikeDA]

Hello to all,

I am writing a query that is attempting to take three fields in a table, and create a new field called "MyKey." I'm doing this using concatenation. The problem: two of these fields, Storage_Facility and Storage_Receipt_Number, are integer fields. The third of these fields, Receipt_Suffix, is a char field. It appears that SQL server will not allow this. I can do it in Microsoft Access, why not in SQL Server? Is there any way around this?

Here's the relevant part of my query:

SELECT MyTable.STORAGE_FACILITY,
MyTable.STORAGE_RECEIPT_NUMBER,
MyTable.STORAGE_RECEIPT_SUFFIX,
(MyTable.STORAGE_FACILITY+MyTable.STORAGE_RECEIPT_ NUMBER+MyTable.STORAGE_RECEIPT_SUFFIX)
AS MyKey,

Etc…

If you can help, great!

Thanks.

[vijaii]

Hello to all,

I am writing a query that is attempting to take three fields in a table, and create a new field called "MyKey." I'm doing this using concatenation. The problem: two of these fields, Storage_Facility and Storage_Receipt_Number, are integer fields. The third of these fields, Receipt_Suffix, is a char field. It appears that SQL server will not allow this. I can do it in Microsoft Access, why not in SQL Server? Is there any way around this?

Here's the relevant part of my query:

SELECT MyTable.STORAGE_FACILITY,
MyTable.STORAGE_RECEIPT_NUMBER,
MyTable.STORAGE_RECEIPT_SUFFIX,
(MyTable.STORAGE_FACILITY+MyTable.STORAGE_RECEIPT_ NUMBER+MyTable.STORAGE_RECEIPT_SUFFIX)
AS MyKey,

Etc…

If you can help, great!

Thanks.

Try any of these....

SELECT MyTable.STORAGE_FACILITY,
MyTable.STORAGE_RECEIPT_NUMBER,
MyTable.STORAGE_RECEIPT_SUFFIX,
(CAST(MyTable.STORAGE_FACILITY AS VARCHAR(10))
+ CAST(MyTable.STORAGE_RECEIPT_ NUMBER AS VARCHAR(10))+
MyTable.STORAGE_RECEIPT_SUFFIX)
AS MyKey,


SELECT MyTable.STORAGE_FACILITY,
MyTable.STORAGE_RECEIPT_NUMBER,
MyTable.STORAGE_RECEIPT_SUFFIX,
(CONVERT(VARCHAR(10),MyTable.STORAGE_FACILITY) +
CONVERT(VARCHAR(10),MyTable.STORAGE_RECEIPT_ NUMBER)+
MyTable.STORAGE_RECEIPT_SUFFIX)
AS MyKey ,