Casting error
-
We have a DTS package that runs daily and runs without a hitch. All of a sudden it bombed last night because of a casting error: Error casting varchar to bigint. Here's the code in the DTS package. Overall concept is that we load data from one source, then go update specific values from a different source. This has been running smoothly for a year or so, and just 'hiccupped'. I have gone into the table that it is retreiving the values from and looked at the loannumber(varchar 15) to check the ISNUMERIC and are all numbers. The LoanNumber data length is 12. The reason of the casting back and forth (I can only assume because I didn't write it) was to ensure no alphanumeric characters appear in that field. What puzzles me is that the source of the data format has not changed (this has worked for years) and there are no alpha characters in the data. Can anyone help? I'm stuck on this one.
UPDATE CollExceptions
SET NetBookBalance = ISNULL
((SELECT TOP 1 CurrentBalance
FROM LoanExtraction.dbo.Balance
WHERE LoanNumber = CAST(CAST(LTRIM(RTRIM(ObligationNo)) AS bigint) AS varchar)), 0)
WHERE (NetBookBalance IS NULL OR
NetBookBalance = 0) AND (LTRIM(RTRIM(ObligationNo)) <> '') AND (ReportPeriodID IN
(SELECT TOP 1 ReportPeriodID
FROM tblReportPeriod
WHERE (ReportTypeID = '1')
ORDER BY ReportPeriodID DESC))
Thanks!
Joanie
-
Keep in mind that Isnumeric() returns true for many things that aren't actually a number. It even considers a "Tab" character to be valid. You can rewrite your Isnumeric() function, which is what many of us do, but to find out if that is the problem, try to verify that that is the problem by doing something like SELECT Cast(LTrim(RTrim(ObligationNo )) AS bigint) from whichever table that column can be found.
-
Here is an example, and as you can see there is not an easy answer as some workarounds for ISNUMERIC = 1 work for some and not for others, the ones that cause errors are commented:
DECLARE @ObligationNo varchar(15)
SET @ObligationNo = ' - '
SELECT @ObligationNo AS ObligationNo
, ISNUMERIC(@ObligationNo) AS IsNumber
, CAST(CAST(@ObligationNo AS bigint) AS varchar) AS Casted
--, CAST(CAST(FLOOR(@ObligationNo) AS bigint) AS varchar) AS CastedFloor
SET @ObligationNo = CHAR(9)+' 1 '
SELECT @ObligationNo AS ObligationNo
, ISNUMERIC(@ObligationNo) AS IsNumber
--, CAST(CAST(@ObligationNo AS bigint) AS varchar) AS Casted
, CAST(CAST(FLOOR(@ObligationNo) AS bigint) AS varchar) AS CastedFloor
SET @ObligationNo = ' . '
SELECT @ObligationNo AS ObligationNo
, ISNUMERIC(@ObligationNo) AS IsNumber
--, CAST(CAST(@ObligationNo AS bigint) AS varchar) AS Casted
--, CAST(CAST(FLOOR(@ObligationNo) AS bigint) AS varchar) AS CastedFloor
SET @ObligationNo = ' 100.001 '
SELECT @ObligationNo AS ObligationNo
, ISNUMERIC(@ObligationNo) AS IsNumber
--, CAST(CAST(@ObligationNo AS bigint) AS varchar) AS Casted
, CAST(CAST(FLOOR(@ObligationNo) AS bigint) AS varchar) AS CastedFloor
SET @ObligationNo = ' 2000 '
SELECT @ObligationNo AS ObligationNo
, ISNUMERIC(@ObligationNo) AS IsNumber
, CAST(CAST(@ObligationNo AS bigint) AS varchar) AS Casted
, CAST(CAST(FLOOR(@ObligationNo) AS bigint) AS varchar) AS CastedFloor
Andy
-
Also it is true that ISNUMERIC accepts currency symbols and not only tabs, spaces, plus/minus sign and decimal point that David pointed out earlier.
See this code
declare @test-2 table (item varchar(20))
insert @test-2
select ' $ -1123.121 ' union all
select ' € +1123.121 ' union all
select '- ¥ 1123.121 ' union all
select '+ £ 1123.121 ' union all
select ' - ' union all
select CHAR(9) + ' 1 ' union all
select ' . ' union all
select ' 100.001 ' union all
select ' 2000 ' union all
select '1,2' union all--depending on locale. Comma is decimal separator in some countries.
select '123456789'
select item,
isnumeric(item) 'IsNumeric Result',
patindex('%[^0-9]%', item) 'PatIndex Result'-- 0 if only numbers!
from @test
N 56°04'39.16"
E 12°55'05.25" -
just my 2ct to find the bogus data ...
create table #tmp (RNo bigint identity(1,1) not null primary key, ObligationNo varchar(15))
declare @Rowcount bigint
insert into #tmp (ObligationNo )
select ObligationNo
FROM CollExceptions
set @Rowcount = @@Rowcount
declare @ctr bigint
set @ctr = 1
while @ctr < @Rowcount
begin
delete from #tmp where RNo = @ctr
and (CAST(CAST(LTRIM(RTRIM(ObligationNo)) AS bigint) !=0
or CAST(CAST(LTRIM(RTRIM(ObligationNo)) AS bigint) =0 )
set @ctr = @ctr + 1
end
Select top 1 *
from #tmp
order by RNo
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
What happens when ObligationNo is equal to, say "This is a number" ?
N 56°04'39.16"
E 12°55'05.25" -
All -
Thanks SO MUCH for your responses!
I ended up doing a select on that column when David posted that ISNUMERIC validated non-numeric characters. That was a great clue for me to start with to do the following:Select ObligationNo
From CollExceptions
Where (ObligationNo like '%,%' or ObligationNo like '%.%' or ObligationNo like '%$%')
That found the error - a comma in that field "219,238" which was a bogus number. We don't know how that got into that field, but we finally found it and got that data corrected in the host system and our db, and then the DTS package worked like a charm! I thoroughly documented what to do when/if that happens again. I'll work on trying to implement that data validation so it doesn't totally cause the DTS to bomb. I don't know that this has ever happened on this DTS package and I've been supporting this one for 2+ years.
I know in other languages (like VB) there is a IsNAN function (Is Not A Number) to determine whether it is truly is a number. I was thinking SQL's ISNUMERIC was the equivalent. I couldn't find anything that did that the same. Does anyone know of any other function in SQL (T-SQL) that would implicitly determine a true number? There are so many times that would be helpful in our DTS Packages - most of our data is numeric.
Thanks again! I really appreicate your help!
Joanie
-
I 've forgotten to mention the setup of my script.
So it creates an #-temptb and it fills it up with all the data.
Then each while loop deletes 1 row from the #-temptb
When the bogus data is met, it fails, but the #-temptb is still available !
Just select the top 1 row containing the bogus info, that's why the order by is stated.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
If ObligationNo is not ment to contain anything but integer values, why it's varchar?
Just to increase number of lines of code and probability of errors?
_____________
Code for TallyGenerator -
"If ObligationNo is not ment to contain anything but integer values, why it's varchar?
Just to increase number of lines of code and probability of errors?"
Sergiy - Thanks so much for your overwhelming sympathy! Considering that I don't have control over the host system it comes from and didn't design the database originally, and given that are so many other complex applications that use that data, and given that we're short staffed, it's unlikely any of it will be rewritten anytime soon. We'll just continue to have to live with the "increased number of lines of code and the probability of errors" until we have decided to stop sitting around and twiddling our thumbs. We happen to work in a real life environment where sometimes you have to live with things that don't make sense.
-
It's funny, but I never have read from anybody designed those databases, everybody just inherited and does not have a chance to change anything.
In fact it's easier to fix design than write, debug and maintain those horrible queries.
In your case adding of computed column or indexed view containing only integer values could make the queries reliable and simple, without breaking the existing functionality.
P.S. Is the clause (LTRIM(RTRIM(ObligationNo)) <> '') also a part of "inheritanse" or it's done just to slow the query down?
_____________
Code for TallyGenerator -
Joanie, don't let him get to you. While in fairness, Sergiy is often (but not always) correct in his assessments, he also apparently lives in a fantasyland where everyone is hired by a company that is just beginning to use SQL Server, and therefore has full and utter control over the design, where no one is a short term consultant that has to work within the existing framework no matter how badly designed, and where there aren't a million applications already in existence that are running against a database, thus making design changes extremely tricky. He also seems to forget that there is a state in the learning process that is between "I don't have a freaking clue about SQL Server" and "I'm a a SQL Server god". In reality, the vast majority of SQL folks are at some point between the two, and helping them towards the latter goal is why this board is here.
While it's always a good idea to attempt to fix problems with design rather than to work around them, some of us in the real world are perfectly willing to let you know what would help if you do have the option of fixing things, while at the same time helping you solve your immediate problem if you don't. Most of us have been in both pairs of shoes.
-
It's not necessarily a mistake to store the values as varchar. Often ID 'numbers' are not really numbers, even though they may be restricted to a very limited subset of all possible characters (e.g. 0-9). You wouldn't, for example, want to perform any sort of arithmetic on such a value. Sum and average make no sense. Sorting in numeric order might not make sense. The restriction to numerals might be a changeable business rule. The 'numbers' in this case are limited to 12 characters, and (I assume) cannot be negative. So they don't correspond to any numeric datatype offered by SQL server, and would need a check constraint anyway, say (ID > 0) rather than (ID not like '%[^0-9]%'). Storing the data as a 2-to-14 byte varchar(12) could be more efficient than a 16 byte bigint, [EDIT: Sergiy has pointed out that bigint is 8b not 16, which takes the edge off my point a bit!] and not much different from a 9 byte decimal(12,0). If leading zeros were permitted, or if alphabetic (left-to-right regardless of length) sorting/searching on leading characters made sense, then the 'numbers' would clearly be numeric strings and should (presumptively but defeasibly) be stored as such.
In this case, though, I must admit that it looks as though Sergiy is (perhaps accidentally) right in principle, if a little too ready to assume, and berate, imbecility and ignorance in others. The problem should at least be reported and marked as needing a fix, if only to cover yourself. If there aren't the resources to do it, that decision should be made deliberately by the person responsible for resource allocation (which may be you of course - I don't know), and recorded.
In an unguarded moment, I've allowed people to talk me out of database redesigns before, without properly recording the discussion. And guess who gets the blame when bad design throws up really horrible problems? Clue: not the lazy app developer who just wants to avoid trawling through thousands of lines of tedious and repetitious code (why be a procedural programmer then?).
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Thanks, Tim and David! It was just not the best way to end last week, but at least it was resolved.
I totally agree with the desire to change the design - with the system that was purchased, it was designed to handle any type of account number, not just numeric content, so I guess the person who designed our interface took that into account in the event our business rules ever changed instead of casting it going in. Tim - you're right - these "numbers" are not anything we would perform any math calculation on - more string functions in searching for the account number to retrieve data.
Thanks so much for your input and encouragement! I'll keep plugging along and trying to make things as efficient and clean as I can when I can. The documenting idea is great...I sometimes don't think of a master "to do" list. Our team has often discussed it, but haven't done so yet. Perhaps this is the catalyst to start that list!
Thanks again,
Joanie
-
2 Tim,
just one note.
Bigint is 8 byte, not 16.
Varchar can NEVER be more effective than numeric. It uses only 26 combinations out of 128. Ratio is 5:1. 100:20 in basketball terms.
Not to mention overheads related to collations. Even if you use just numbers, this thing works behind the scene.
2 Joanie
Table design is essential part of your query. You can never construct good query based on bad design.
No one can stop you trying, but it's gonna be another fight with windmills.
And it's always good to learn some basics before you blame inheritance. Tables you've got may be bad, but the way you built the query is not better.
Sorry.
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply