April 1, 2011 at 4:16 pm
Help please... I need help with my query. This is obviously is not working. What I need to do is take my EXTENDED_LIST_PRICE where SUBJECT_TO_PER_DIEM = 'T' and make the result = PD. For the Cgi.SUBJECT_TO_PER_DIEM = 'F', the value = $
SUBJECT_TO_PER_DIEM is a TRUEFALSE(Char(1))
EXTENDED_LIST_PRICE is a decimal(14,2)
, Case when Cgi.SUBJECT_TO_PER_DIEM = 'F' then Cgi.EXTENDED_LIST_PRICE
when Cgi.SUBJECT_TO_PER_DIEM = 'T' then CAST(extended_list_price AS VARCHAR)+'PD'
END as 'Extended List Price'
Error Message received:
Msg 8114, Level 16, State 5, Line 11
Error converting data type varchar to numeric.
April 1, 2011 at 5:16 pm
It is very simple.
You need to cast whole thing not the particular price alone
SELECT Case when Cgi.SUBJECT_TO_PER_DIEM = 'F' then CAST(Cgi.EXTENDED_LIST_PRICE AS VARCHAR)
when Cgi.SUBJECT_TO_PER_DIEM = 'T' then CAST(extended_list_price AS VARCHAR)+'PD'
end
Thanks
Parthi
April 1, 2011 at 5:20 pm
I don't understand exactly what you are trying to accomplish, but you can't have a CASE expression evaluate to two different types. (SQL Server will attempt to convert them to the same type based on the data type precedence rules. See: http://msdn.microsoft.com/en-us/library/ms190309.aspx)
So you would need to convert both options to VARCHAR if that is what you want.
Since it looks like you just want to add PD to the end when SUBJECT_TO_PER_DIEM = 'T' then you could use this:
CAST(extended_list_price AS VARCHAR)+Case when Cgi.SUBJECT_TO_PER_DIEM = 'T' then 'PD'
ELSE '' END as [Extended List Price]
Note: You really should specify a length on the CAST to VARCHAR, otherwise it will default to 30, which might not be long enough. (It probably will be for this, but you never know.)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply