TSQL no longer working in SQL2012

Question

Post reply

TSQL no longer working in SQL2012

TSQL Tryer

SSCrazy Eights

Points: 8869
More actions
May 27, 2014 at 5:40 am

#307767

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.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

+ CAST(CASE WHEN PER.[GENDER] = 'M' THEN '1'

WHEN PER.[GENDER] = 'F' THEN '2'

ELSE '0' END AS CHAR(1))

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 2

Thanks for the reply. That's not working I'm afraid.

It's still increasing the TEN.[tncy-sys-ref] by 1 or 2 as opposed to just concatenating a 1 or a 2 at the end of TEN.[tncy-sys-ref]

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 3

Could Try I'm guessing at the Varchar Length:

,CAST(TEN.[tncy-sys-ref] AS VARCHAR(50))

+ CAST(CASE WHEN PER.[GENDER] = 'M' THEN '1'

WHEN PER.[GENDER] = 'F' THEN '2'

ELSE '0' END AS CHAR(1))

Or you could use SQL 2012 CONCAT

,CONCAT(CAST(TEN.[tncy-sys-ref] AS VARCHAR(50)),

CAST(CASE WHEN PER.[GENDER] = 'M' THEN '1'

WHEN PER.[GENDER] = 'F' THEN '2'

ELSE '0' END AS CHAR(1)))

Andy

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 4

Got it working -

CONCAT (TEN.[tncy-sys-ref],CASE WHEN PER.[GENDER] = 'M' THEN '1'

WHEN PER.[GENDER] = 'F' THEN '2'

ELSE '0'END) AS 'ID'

Hunterwood SSCrazy Points: 2074 More actions · Answer 5

Hi,

It seems as if TEN.[tncy-sys-ref] is numeric or int?

If you want the result as a char, you probably have to adjust Gilas code by adding another CAST:

CAST(TEN.[tncy-sys-ref] AS CHAR(7 /* or whatever you want */)

On the other hand, if you want to have a numeric response, you need to multiply by 10 (and then you could skip all the cast:ing to char):

(TEN.[tncy-sys-ref] * 10)

/Markus

gbritton1 SSCertifiable Points: 6522 More actions · Answer 6

This is a great example showing that Explicit is better than Implicit.

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 7

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.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MMartin1 One Orange Chip Points: 27764 More actions · Answer 8

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.

----------------------------------------------------

ScottPletcher SSC Guru Points: 100946 More actions · Answer 9

Yep. You just need to CAST the first column as varchar:

,CAST(TEN.[tncy-sys-ref] AS varchar(10))

+ CASE WHEN PER.[GENDER] = 'M' THEN '1'

WHEN PER.[GENDER] = 'F' THEN '2'

ELSE '0'END AS 'ID'

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.