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.
Quote:
Originally Posted by 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.
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 ,
No comments:
Post a Comment