asked on

Conversion failed when converting the nvarchar value '10.96' to data type int.

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?anchorAnswerId=42663296#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(DECIMAL(19,2),LPW))
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.

    SELECT tmpTable.MATNR,
           tmpTable.itemNumber,
           CAST(tmpTable.WPL AS VARCHAR(20)) + ' oz./ft.',
           CASE
               WHEN tmpTable.LPW >= 2 THEN
                   CAST(tmpTable.LPW AS VARCHAR(20)) + ' ft./oz.'
               ELSE
                   CAST(tmpTable.LPW * 12 AS VARCHAR(20)) + ' in./oz.' END
    FROM #tmpTable tmpTable

Open in new window

PortletPaul

Is LPW decimal?

Then don't convert it for the comparison, just on the output e.g.

CASE
WHEN tmpTable.LPW >= 2.0 THEN
format(cast(LPW as decimal(30,4)),'n2') + ' ft./oz.'
ELSE
format(cast(LPW as decimal(30,4)),'n2') + ' in./oz.' END

dbaSQL

ASKER

It is DECIMAL in format, but not in datatype. It is varchar datatype.

I just tried this successfully to just pull it back out of the temp table: CAST(LPW as decimal(8,2))

That works on the front side of the CASE, but I am having problems syntactically on the back half.

WHEN CAST(tmpTable.LPW as decimal(8,2)) >= 2 THEN ------ this is good
CAST(tmpTable.LPW AS VARCHAR(20)) + ' ft./ozt.' END
ELSE
CAST(tmpTable.LPW * 12 AS VARCHAR(20)) + ' in./oz.' END --- haven't got it yet

dbaSQL

ASKER

heh...... is it this easy?

CAST(LPW as decimal(8,2)) * 12

dbaSQL

ASKER

No. I was able to select the data back out of my temp table, but when I run the statement below, it fails with this error. Must be my concatentated label.

Msg 8114, Level 16, State 5, Line 403
Error converting data type varchar to numeric.

SELECT tmpTable.MATNR,
tmpTable.itemNumber,
CAST(tmpTable.WPL AS VARCHAR(20)) + ' ozt./ft.',
CASE
WHEN CAST(tmpTable.LPW as decimal(8,2)) >= 2 THEN
CAST(tmpTable.LPW AS VARCHAR(20)) + ' ft./ozt.'
ELSE
CAST(tmpTable.LPW DECIMAL(8,2)) * 12 + ' in./ozt.' END
FROM #tmpTable tmpTable

dbaSQL

ASKER

Got it. This works, and my values are retained.

CONVERT(VARCHAR(12),CAST(LPW as decimal(8,2)) * 12) + 'XX'

ASKER CERTIFIED SOLUTION

dbaSQL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

great.

If at all possible, keep numeric data numeric until the very last moment. converting back and forth isn't efficient and (as you can clearly see) can cause errors.

e.g. if you placed LPW into that temp table as varchar, perhaps it should have left as decimal instead.

dbaSQL

ASKER

>>If at all possible, keep numeric data numeric v
Completely agreed, Portlet. Unfortunately, the customer did not take heed of my advice.