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#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

LVL 18
dbaSQLAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
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
0
dbaSQLAuthor Commented:
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
0
dbaSQLAuthor Commented:
heh...... is it this easy?

CAST(LPW as decimal(8,2)) * 12
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

dbaSQLAuthor Commented:
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
0
dbaSQLAuthor Commented:
Got it.  This works, and my values are retained.

CONVERT(VARCHAR(12),CAST(LPW as decimal(8,2)) * 12) + 'XX'
0
dbaSQLAuthor Commented:
Thank you for looking, PortletPaul, but I found my own solution.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
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.
0
dbaSQLAuthor Commented:
>>If at all possible, keep numeric data numeric v
Completely agreed, Portlet.  Unfortunately, the customer did not take heed of my advice.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.