Jeff Moden (5/27/2014)
Ryan Keast (5/27/2014)
Hi there,I had the following script that used to work in SQL2000 but now that I have migrated it, it no longer does.
,TEN.[tncy-sys-ref]
+ CASE WHEN PER.[GENDER] = 'M' THEN '1'
WHEN PER.[GENDER] = 'F' THEN '2'
ELSE '0'END AS 'ID'
This used to either add a 1 or a 2 to the end of the TEN.[tncy-sys-ref]. What it does now is actually make an addition.
Example the TEN.[tncy-sys-ref] is 25337.
On SQL2000 it is 253371 for a Male or 253372 for a female.
On SQL2012 it's coming back 25338 for a male and 25339 for a female.
I need it to concatenate instead of addition.
If that's true, then something went haywire with the migration because that code will still work as you have it if TEN.[tncy-sys-ref] is still a VARCHAR() and could only work as is if it was a VARCHAR(). Check the old system that you migrated and see if the column was, infact a VARCHAR() because I think it was and something changed it during the migration.
+1
The number is a higher order data type ...so strings will try to be converted to it.
SELECT 5+ '5' --> 10 /* not '55' */
With 2012, use the CONCAT function - in it everything becomes a string first.
----------------------------------------------------