[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 957
  • Last Modified:

Error converting data type varchar to numeric

Hello,

I have a select statement that I keep getting an "Error converting data type varchar to numeric" and I cannot figure out why I am getting this statement or to even have it run complete without returning the error.  I have am using the same cast statement in an insert procedure and I don't get the error.  The data type of t.fullrecord is char(125).  I am using a substring to select the 20 digit UPC code from the t.fullrecord data field.  I acutally get this error on both Cast statements even when I seperate them out.  If I drop the second Cast statement that is in the Where clause and run the procedure it will start diplaying data and then blank i out and give me the error message.

Any ideas how I can figure out which records are giving me the error?  And/or how I can have the script run without rutruning the error message and just give me the valid data reocrds?  I would really like to be able to figure out which records are causing the error.

-- begin script code

SELECT t.StoreId, t.TransDate
    ,CAST(SUBSTRING(t.FullRecord,22,20) AS DECIMAL(20,0))   -- UPC
    ,t.FullRecord
    ,SUM(CAST(SUBSTRING(t.FullRecord,53,2) AS SmallInt))      -- Qnty
FROM dbo.TLogAscii t
WHERE t.RecordCode IN (SELECT Code FROM TransCode WHERE TransCodeId     BETWEEN 37 AND 52)
      and t.TransDate BETWEEN '1/2/11' AND '1/15/11'      
      AND CAST(SUBSTRING(t.FullRecord,22,20) AS DECIMAL(20,0)) IN
                        (7230200051, 7230200058, 4679826161, 6638018025, 15033
                        ,3810001617, 3810011144, 7910092561, 7419860964
                        ,64046101044, 3810013059, 72363388628, 74217400800, 9498379156)

---- end script code

Thanks in advance for the help.
0
JohnJMA
Asked:
JohnJMA
1 Solution
 
Ephraim WangoyaCommented:


try running this query

select FullRecord
from TLogAscii
where ISNUMERIC(SUBSTRING(FullRecord,22,20)) <> 1

Should give you the offending records
0
 
JohnJMAAuthor Commented:
Ewangoya, thanks, I kept using the CAST with the ISNUMERIC.. ISNUMERIC(CAST(...)) and that was causing my problem  user error.  Your solution was right on and I can get the script running by using the ISNUMERIC(..) = 1 (leaving out the CAST) returns only the valid records.  So simple and I was drawing a blank.  Thanks again.

John
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now