I recently received some excellent help from another Expert with a decimal manipulation that you see in this ticket. Basically, I needed to control the decimal output to 2 digits or 3, and the Expert gave me great advice: https://www.experts-exchange.com/questions/29114876/VARCHAR-to-DECIMAL-string-manipulation.html#a42663296
But, I am having a problem with the data in one of the subsequent manipulations which is in the code below. I've input a lot of numbers into a temp table using the FORMAT in the above question, where the columns are VARCHAR(255), and the values in the column are 1.00, .50, 5.49, .17, etc. The problem is that I need to reference that character value numerically within the CASE below, and every attempt to do so fails with the error in my subject line.
I tried to correct it myself with this: CONVERT(INT,CONVERT(DECIMA
But the end result was changed --- .01 becomes 0, 2.30 becomes 2, etc. So my attempt to convert the char value so that I can perform the CASE statement CHANGES the data, and thereby invalidates it.
Can somebody assist?
I can cast it as float, but then I lose my decimals -- 1.00 becomes 1, 0.50 becomes 0.5... etc. I need a result that retains the data as I have stored it in the temp table.
CAST(tmpTable.WPL AS VARCHAR(20)) + ' oz./ft.',
WHEN tmpTable.LPW >= 2 THEN
CAST(tmpTable.LPW AS VARCHAR(20)) + ' ft./oz.'
CAST(tmpTable.LPW * 12 AS VARCHAR(20)) + ' in./oz.' END
FROM #tmpTable tmpTable