PLEASE tell me I don't have to cursor thru this one by one
v2008, 'map' table has 1.7B records in it: Aid char(16), Bid bigint
Basically, I'm using the function below converting Aid to base 36, like this:
select dbo.fn_basetodec('7A53HKUV
end result: 958431152002393701
I need to perform that conversion on Aid and write the end result to Bid.
example:
Aid 7A53HKUV7A79
Bid 958431152002393701
like i said, map table is pretty freeking big.
Any insight on the most optimal means of performing this update?
Basically, I'm using the function below converting Aid to base 36, like this:
select dbo.fn_basetodec('7A53HKUV
end result: 958431152002393701
I need to perform that conversion on Aid and write the end result to Bid.
example:
Aid 7A53HKUV7A79
Bid 958431152002393701
like i said, map table is pretty freeking big.
Any insight on the most optimal means of performing this update?
CREATE FUNCTION dbo.fn_basetodec
(@val AS VARCHAR(63), @base AS int)
RETURNS BIGINT
AS
BEGIN
RETURN
(SELECT SUM(
(CHARINDEX(
SUBSTRING(@val, LEN(@val) - n + 1, 1),
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') - 1)
* POWER(CAST(@base AS BIGINT), n-1))
FROM Nums
WHERE n <= LEN(@val));
END
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
well, i tried the select to flat file. i got to record 95570 and failed with this:
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type bigint.
surely this goes a little further than just the write to a file. i changed @base to bigint, in the function, but it changed nothing.
do you see what may be the problem?
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type bigint.
surely this goes a little further than just the write to a file. i changed @base to bigint, in the function, but it changed nothing.
do you see what may be the problem?
yes
for a very long varchar, you get a value that is greater than bigint, which is weird since bigint is very big...
try using decimal(30,0) instead
CREATE FUNCTION dbo.fn_basetodec
(@val AS VARCHAR(63), @base AS int)
RETURNS decimal(30,0)
AS
BEGIN
RETURN
(SELECT SUM(1.0*
(CHARINDEX(
SUBSTRING(@val, LEN(@val) - n + 1, 1),
'0123456789ABCDEFGHIJKLMNO
* POWER(CAST(@base AS BIGINT), n-1))
FROM Nums
WHERE n <= LEN(@val));
END
GO
for a very long varchar, you get a value that is greater than bigint, which is weird since bigint is very big...
try using decimal(30,0) instead
CREATE FUNCTION dbo.fn_basetodec
(@val AS VARCHAR(63), @base AS int)
RETURNS decimal(30,0)
AS
BEGIN
RETURN
(SELECT SUM(1.0*
(CHARINDEX(
SUBSTRING(@val, LEN(@val) - n + 1, 1),
'0123456789ABCDEFGHIJKLMNO
* POWER(CAST(@base AS BIGINT), n-1))
FROM Nums
WHERE n <= LEN(@val));
END
GO
ASKER
same failure, again at 95570
i'm just running this:
select Aid,dbo.fn_basetodec(Aid,3
i'm just running this:
select Aid,dbo.fn_basetodec(Aid,3
ASKER
Aid max len is 16
that is very weird,
can you isolate the problematic value of aid?
can you isolate the problematic value of aid?
ASKER
trying to do so... but, not exactly sure how. like i said, the table is pretty huge. still working on it.
ASKER
ok. i'm waaay puzzled. all i did was add the datefield into my select:
select datefield,Aid,dbo.fn_baset
i just wanted to be able to look at the date it was on, when it failed.
it's still running.
i'm at 4430423
i don't want to select * this whole thing to QA. surely the addition of the datetime field into the resultset didn't address whatever was causing the arithmetic overflow
select datefield,Aid,dbo.fn_baset
i just wanted to be able to look at the date it was on, when it failed.
it's still running.
i'm at 4430423
i don't want to select * this whole thing to QA. surely the addition of the datetime field into the resultset didn't address whatever was causing the arithmetic overflow
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>>You will have to identify the record(s) that are too large for your formula and decide what to do with them.
How?
How?
ASKER
well, i just attempted to write the results into a table. you are correct, it blew up again. of course, my insert failed. this particular function is brand new for me.
i'm not exactly sure how to identify the offenders
i'm not exactly sure how to identify the offenders
I was trying to find what the largest int value you can store in sql and I found this
bigint MIN:-2^63 (-9,223,372,036,854,775,80
so if you can reverse engineer 9,223,372,036,854,775,807 through your function logic and see what string that would represent and search for a string of that length and greater than the string or of a longer length you will identify the offenders.
bigint MIN:-2^63 (-9,223,372,036,854,775,80
so if you can reverse engineer 9,223,372,036,854,775,807 through your function logic and see what string that would represent and search for a string of that length and greater than the string or of a longer length you will identify the offenders.
ASKER
sounds good. now i just need to figure out how to implement. i will let you know what i work out
ASKER
it's still running... lord knows how long it's going to take. i just set arithabort and ansi_warnings off. i am writing the Aid and Bid into a table. if/when it completes, one assumes i will have written nulls for those which caused the overflow. only then will i be able to figure out what is causing this, and how to address it
thank you for your input
thank you for your input
you can just do
update table
set bid= dbo.fn_basetodec(aid)
where aid between @x and @x + 20000
and just have a counter that advances @x in 20000 in a loop