Update script errors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Update script errors

Author

Topic

ROShea
Starting Member

2 Posts

Posted - 2010-12-02 : 09:39:06

Hello,

Been fighting with this for a while now, here's the problem:

I have a Select script which takes numeric values from a varchar field which will be used to update a numeric (float) field.
The Select script works fine and displays the numbers correctly.
***Sample Input Data - cucode, cu_credit_limit, cuuser3***
10033 - 1000 - £1000 Credit Limit
40032 - 0.01 - £0.01 Credit Limit
12796 - 1000 - £1k Credit Limit
13937 - 200 - £200.00 Credit Limit
12359 - 15500 - £15.5k Credit Limit

These values show as 1000.00, 0.01, 1000.00, 200.00, 15500.00 as per the select code here:

select 
cucode, 
cu_credit_limit, 
substring(cuuser3,2,(PATINDEX('% %',cuuser3)-2)) as Sort3Search,
PATINDEX('%k%',cuuser3) as K_Index,
PATINDEX('%.%',cuuser3) as Decimal_Index
,SearchSort3 = case 
	--NO K NO DECIMAL--
	WHEN (patindex('%k%',cuuser3)=0 AND PATINDEX('%.%',cuuser3)=0)
	THEN CAST(substring(cuuser3,2,(PATINDEX('% %',cuuser3)-2))AS decimal(10,2))
	--K NO DECIMAL--
	WHEN (patindex('%k%',cuuser3)>0 AND PATINDEX('%.%',cuuser3)=0)
	THEN CAST(substring(cuuser3,2,(PATINDEX('%k%',cuuser3)-2))AS decimal(10,2))*1000 
	--DECIMAL NO K
	WHEN (PATINDEX('%.%',cuuser3)>0 AND patindex('%k%',cuuser3)=0)
	THEN CAST(Substring(cuuser3,2,(PATINDEX('%.%',cuuser3)+2))AS decimal(10,2))
	--DECIMAL & K
	WHEN (PATINDEX('%.%',cuuser3)>0 AND patindex('%k%',cuuser3)>0)
	THEN CAST(substring(cuuser3,2,(PATINDEX('%K%',cuuser3)-2))AS decimal(10,2))*1000
	END
from sl_accounts
where 
len(cuuser3) > 0
and (left(cucode,1) = '1' or left(cucode,1) = '4')
and left(cuuser3,1) = '£'

But when I try and run this Update code

Update SL_Accounts
Set cu_credit_limit =
	CASE
	--NO K NO DECIMAL--
	WHEN (patindex('%k%',cuuser3)=0 AND PATINDEX('%.%',cuuser3)=0)
	THEN CAST(substring(cuuser3,2,(PATINDEX('% %',cuuser3)-2))AS float)
	--K NO DECIMAL--
	WHEN (patindex('%k%',cuuser3)>0 AND PATINDEX('%.%',cuuser3)=0)
	THEN CAST(substring(cuuser3,2,(PATINDEX('%k%',cuuser3)-2))AS float)*1000 
	--DECIMAL NO K
	WHEN (PATINDEX('%.%',cuuser3)>0 AND patindex('%k%',cuuser3)=0)
	THEN CAST(Substring(cuuser3,2,(PATINDEX('%.%',cuuser3)+2))AS float)
	--DECIMAL & K
	WHEN (PATINDEX('%.%',cuuser3)>0 AND patindex('%k%',cuuser3)>0)
	THEN CAST(substring(cuuser3,2,(PATINDEX('%K%',cuuser3)-2))AS float)*1000
	END
where 
len(cuuser3) > 0
and (left(cucode,1) = '1' or left(cucode,1) = '4')
and left(cuuser3,1) = '£'

It shows:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.

Any help is very much appreciated as I've been at this now for hours with no luck!

Thank You,
Ryan O'Shea

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-02 : 17:22:53

The results of your select statement have the letter 'k' in a few results and incorrectly translates 200.00 into 20.0

your results look like this which are not correct...:


cucode	cu_credit_limit	Sort3Search	K_Index	Decimal_Index	SearchSort3
10033	1000.00	1000	0	0	1000.00
12359	15500.00	15.5k	6	4	15500.00
12796	1000.00	1k	3	0	1000.00
13937	200.00	200.00	0	5	200.00
40032	0.01	0.01	0	3	0.01

I think this works though...


Select 
cucode, 
cu_credit_limit, cuuser3,
SearchSort3 = 
Case When charindex('k',cuuser3)>0 
then convert(numeric(12,2),Substring(Replace(Replace(cuuser3,'k',''),'£',''),1,charindex(' ',Replace(Replace(cuuser3,'k',''),'£',''),1)-1))*1000
else Substring(Replace(Replace(cuuser3,'k',''),'£',''),1,charindex(' ',Replace(Replace(cuuser3,'k',''),'£',''),1)-1)
end
from sl_accounts
where 
len(cuuser3) > 0
and (left(cucode,1) = '1' or left(cucode,1) = '4')
and left(cuuser3,1) = '£'

--results of above
cucode cu_credit_limit cuuser3 SearchSort3
10033 1000.00 £1000 Credit Limit 1000.00
12359 15500.00 £15.5k Credit Limit 15500.00
12796 1000.00 £1k Credit Limit 1000.00
13937 200.00 £200.00 Credit Limit 200.00
40032 0.01 £0.01 Credit Limit 0.01
[/code]

Poor planning on your part does not constitute an emergency on my part.

ROShea
Starting Member

2 Posts

Posted - 2010-12-03 : 04:06:47

Many thanks!

Looks like a much more elegant solution than my hash attempt.

Just tried to use it to update the changes and it works!

Thank you for spending time working it out for me dataguru1971.

A much happier,
Ryan O'Shea

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources