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_Facilit y 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.STORAG E_FACILITY+MyTa ble.STORAGE_REC EIPT_NUMBER+MyT able.STORAGE_RE CEIPT_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_Facilit y 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.STORAG E_FACILITY+MyTa ble.STORAGE_REC EIPT_NUMBER+MyT able.STORAGE_RE CEIPT_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.S TORAGE_FACILITY AS VARCHAR(10))
+ CAST(MyTable.ST ORAGE_RECEIPT_ NUMBER AS VARCHAR(10))+
MyTable.STORAGE _RECEIPT_SUFFIX )
AS MyKey,


SELECT MyTable.STORAGE _FACILITY,
MyTable.STORAGE _RECEIPT_NUMBER ,
MyTable.STORAGE _RECEIPT_SUFFIX ,
(CONVERT(VARCHA R(10),MyTable.S TORAGE_FACILITY ) +
CONVERT(VARCHAR (10),MyTable.ST ORAGE_RECEIPT_ NUMBER)+
MyTable.STORAGE _RECEIPT_SUFFIX )
AS MyKey ,