Error converting data type varchar to numeric issue

Question

Error converting data type varchar to numeric issue

JeremyU

SSChasing Mays

Points: 640
More actions
August 3, 2022 at 11:47 pm

#4072215
I tried a lot of things to fix it, and no success.
I have a field called MASTER_BOL_NUMBER . According to documentation it is CHAR.

I see that inside it has only blanks and numbers

When I try to CAST( MASTER_BOL_NUMBER as numeric)
I am getting an error "Error converting data type varchar to numeric."
I tried also smth like that
```
CAST( IIF(MASTER_BOL_NUMBER='',0,MASTER_BOL_NUMBER) as numeric)
```
and this
```
CAST( IIF(MASTER_BOL_NUMBER IS NULL,0,MASTER_BOL_NUMBER) as numeric)
```
Also no success, I really don't understand why I am getting this error because usually CAST or CONVERT fix this data type issues.
Does someone else know what may help but for those functions?
- This topic was modified 2 years, 3 months ago by JeremyU.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

You could try using TRY_CAST to determine which row values are causing the CAST to fail

select *
from YourTable
where TRY_CAST(MASTER_BOL_NUMBER as numeric) is null;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

JeremyU SSChasing Mays Points: 640 More actions · Answer 2

@steve-2 Collins

I did it and receive the same error.

Issue3

Steve Collins SSCrazy Points: 2157 More actions · Answer 3

If it's the code in your picture which produced the error message then your probable issue imo is a type mismatch between the join columns. Either ar.S1WHS# or wf.L3WHS# is VARCHAR and contains non-numeric values

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

JeremyU SSChasing Mays Points: 640 More actions · Answer 4

@steve-2 Collins it didn't help. I don't think the issue is in the join...

Issue5

Issue6

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

I will put this rather more forcefully than Steve Collins.

If the following code gives you a varchar/numeric conversion error, there is no doubt at all that the datatypes of the columns involved in the joins do not match. 100%.

SELECT MASTER_BOL_NUMBER
FROM WF_AR_LD ar
    LEFT OUTER JOIN WF_LODM wf
        ON ar.AR_LOAD = wf.L3LOD#
           AND ar.SlWHS# = wf.L3WHS#;

It is very rare that you would join two tables on columns with numeric datatypes. It is bad practice to cast a column as numeric without declaring both precision and scale (see documentation).

Rather than saying things like "According to documentation it is CHAR", please find out for yourself. CHAR columns have a length - you need to know that too. Do you have no access to table structures? Can you execute the following?

SELECT *
FROM sys.columns;

This reply was modified 2 years, 3 months ago by Phil Parkin.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin