March 6, 2008 at 8:58 am
Below is a very simple case select statement:
SELECT CASE RANK_CODE WHEN '1' THEN 'officer'
ELSE RANK_CODE END AS Rank_Code
FROM tableA
The problem is that I continue to receive the message "Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'officer' to data type int."......probably due to the Rank_Code column data type being (int,null).
How do I handle this ?....I tried using a cast statement to change the data type of Rank_Code within the select statement (changing the data type in the physical table is not an option) without any success, but not sure if my syntax was right....see below;
SELECT CASE (CAST(RANK_CODE AS varchar(10)))
WHEN '1' THEN CAST('officer' AS varchar) ELSE RANK_CODE END AS Rank_Code
FROM TableA
Any help would greatly be appreciated....thank you !
March 6, 2008 at 10:12 am
Cast rank_code as a varchar.
SELECT CASE RANK_CODE WHEN 1 THEN 'officer'
ELSE cast(RANK_CODE as varchar) END AS Rank_Code_Text
FROM tableA
Also, you should not redefine an existing column in the SELECT. It will lead to confusion since sometimes the column "Rank_Code" is a number and sometimes it's text. The column has been renamed to "Rank_Code_Text" in the example.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply