Problem with Case

• Maria Carmen Vilbar

  Ten Centuries

  Points: 1037

  More actions

  April 19, 2006 at 2:41 am

  #365977

  I have this statement:

  SELECT (CASE WHEN (PATINDEX('T%', PERSONNELNUMBER)) = 0 THEN (1000000 + Peoplekey)

  ELSE CAST('T' + CONVERT(VARCHAR(10),10000000 + Peoplekey) as VARCHAR)

  END)

  FROM TBL_MASTER_DATA

  and it results to:

  Server: Msg 245, Level 16, State 1, Line 1

  Syntax error converting the varchar value 'T8799850' to a column of data type int.

  Please help me.

  

  MCVilbar

• paul_richardson

  SSCarpal Tunnel

  Points: 4594

  More actions

  April 19, 2006 at 3:53 am

  #633013

  hi, i think it's to do with the fact that case 1 returns an int, and case 2 returns a varchar,

  i tweaked it to the following (basically returning case 1 as a varchar(10)):-

  SELECT (CASE WHEN (PATINDEX('T%', PERSONNELNUMBER)) = 0 THEN cast((1000000 + Peoplekey) as varchar(10))

  ELSE CAST('T' + CONVERT(VARCHAR(10),10000000 + Peoplekey) as VARCHAR)

  END)

  FROM TBL_MASTER_DATA

  and the error disappeared .

  Hope this helps

  Paul

• Maria Carmen Vilbar

  Ten Centuries

  Points: 1037

  More actions

  April 19, 2006 at 3:59 am

  #633015

  Thanks Paul. It helps me a lot...:-D