January 24, 2008 at 12:16 pm
Hope this is the correct forum, newbie here.
Anyway, I have this little update in my stored proc that just keeps bombing (see bold).
update TRX920_FDB_ITEM set cau_ty='NDDC',msr_ds='ML',
dos_exp_ds='ML',
dos_lbl_ds='ML',
com_dos_qy=convert( decimal(16,4) ,(SUBSTRING(SUBSTRING(dsp_sth_ds,CharIndex('/', dsp_sth_ds)+1 , 2000),1,CharIndex(' ',SUBSTRING(dsp_sth_ds,CharIndex('/', dsp_sth_ds)+1 , 2000))-1)))
where gic_med_id in (select gic_med_id from TRX960_FDB_MASTER_INVENTORY
where (fom_cd = 'CONC' or fom_cd = 'ELIX' or fom_cd = 'LIQD' or fom_cd = 'LQCR' or fom_cd = 'SOLN' or fom_cd = 'SOLR'
or fom_cd = 'SUSP' or fom_cd = 'SUSR' or fom_cd = 'SYRP' or fom_cd='INJ' or fom_cd='SYRD'))
AND dsp_sth_ds IS NOT NULL AND dsp_sth_ds not like '%' + '/ML%'
AND (dsp_sth_ds like '%' + '/%' + '0% %' OR dsp_sth_ds like '%' + '/%' + '1% %' OR
dsp_sth_ds like '%' + '/%' + '2% %' OR dsp_sth_ds like '%' + '/%' + '3% %' OR
dsp_sth_ds like '%' + '/%' + '4% %' OR dsp_sth_ds like '%' + '/%' + '5% %' OR
dsp_sth_ds like '%' + '/%' + '6% %' OR dsp_sth_ds like '%' + '/%' + '7% %' OR
dsp_sth_ds like '%' + '/%' + '8% %' OR dsp_sth_ds like '%' + '/%' + '9% %')
and cau_ty is null;
If I run it as a select, it also bombs. If I remove the bolded convert from either statement, it has zero rows affected.
Yes, I have tried wrapping it in an IsNull construct, it still bombs with
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
Any help would be appreciated!
Jay
January 24, 2008 at 12:22 pm
It means you have some strings that aren't strictly numeric in the select.
If you run:
select dsp_sth_ds
from (whatever table that field is in)
where isnumeric(dsp_sth_ds) = 0
You'll find out which rows are causing the problem.
If it doesn't find any, then somewhere in your string functions, you're creating a non-number. In that case, run the "where isnumeric() = 0" with the string function inside the parentheses. I recommend taking one section of it at a time, so you can easily find which part is creating the problem.
Most likely, though, there's a row (or a dozen) in the table, where that field has text or something else that isn't a number.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 24, 2008 at 12:40 pm
Oh, I guarantee you NONE of dsp_sth_ds are numeric, they are all things like "1,000 mg/ 5 ml" or 0.2% etc etc etc.
That is why the convert uses charindex to find the "/" and then it just wants the number after the slash. If it can't find a "/", then it wants to start and the beginning and go until it gets to a " ".
My guess is that it is choking when it hits "1,000 mg", b/c it cannot convert "1,000" to a numeric.
I will give your suggestions a whirl and see what I come up with, thanks!
January 24, 2008 at 12:42 pm
Oh yeah, that SQL executes without an error on SQL 2000 DB...
January 24, 2008 at 2:08 pm
Makes sense. Try running it on the string function, just without the final convert to numeric. That should give you what you need.
Definitely feel for you on that one. Complex string functions to pull numbers out of them are a pain.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 24, 2008 at 5:52 pm
it is really a pain to extract numbers from strings and even more complexity if the numeral standard is different from the standard one. i had done a project for a comp in portugal and there they use to put comma (,) instead of decimal (.) to show cents
u can use replace function in order to get rid of these kind of issues. in order to make sure that u r not extracting any alphabet instead of number from string, use patindex, charindex and isnumeric()
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply