sql - "Error converting data type nvarchar to numeric." -

-



i'm trying create function returns table bunch of fields. query used in function inserts data temp table inserts returning table. query runs fine when run outside of function when select function "error converting data type nvarchar numeric." error.

the structure returning table same table selects , inserts returning table. below how function defined

alter  function [dbo].[func_prescriberdata](@networkid bigint, @startdate datetime, @enddate datetime)  returns  @rtntable table  (  name nvarchar(1000) null,clinicid int null ,networkprescriberid int null,guidemedpatients int  null, inactivepatients int null,avgage decimal  null,avgphqscore decimal  null,  [%riskassessmenthigh] varchar(10) null ,[%riskassessmentmoderate] varchar(10)  null,[%riskassessmentlow] varchar(10)  null,  toxicologytests int  null,[%consistentresults] varchar(10) null, [%alcohol] varchar(10) null,[%negativeprescribed] varchar(10) null  ,[%illicit] varchar(10)  null,totalpdmps int  null,[%aberrantresults] varchar(10)  null, csareviewed int  null,type nvarchar(1000) null ,medlesstan15 int null,between15and59 int null,between60and100 int null,between101and500 int null,greaterthan500 int null, avgmed decimal null, [bh/am] int null,opiodsbelowthreshold int null,opioidprescreening int null,sedativesantianxiety int null,stimulants int null,other int null)  begin  declare @temptable table (  name nvarchar(1000) null,clinicid int null ,networkprescriberid int null,guidemedpatients int  null, inactivepatients int null,avgage decimal  null,avgphqscore decimal  null,  [%riskassessmenthigh] varchar(10) null ,[%riskassessmentmoderate] varchar(10)  null,[%riskassessmentlow] varchar(10)  null,  toxicologytests int  null,[%consistentresults] varchar(10) null, [%alcohol] varchar(10) null,[%negativeprescribed] varchar(10) null  ,[%illicit] varchar(10)  null,totalpdmps int  null,[%aberrantresults] varchar(10)  null, csareviewed int  null,type nvarchar(1000) null ,medlesstan15 int null,between15and59 int null,between60and100 int null,between101and500 int null,greaterthan500 int null, avgmed decimal null, [bh/am] int null,opiodsbelowthreshold int null,opioidprescreening int null,sedativesantianxiety int null,stimulants int null,other int null)  insert @temptable ( name ,clinicid,networkprescriberid,guidemedpatients, inactivepatients ,avgage ,avgphqscore ,[%riskassessmenthigh],[%riskassessmentmoderate] ,[%riskassessmentlow]  ,toxicologytests ,[%consistentresults], [%alcohol] ,[%negativeprescribed] ,[%illicit],totalpdmps ,[%aberrantresults],  csareviewed ,type,medlesstan15,between15and59,between60and100,between101and500,greaterthan500,avgmed,[bh/am],opiodsbelowthreshold,opioidprescreening,sedativesantianxiety,stimulants,other

it's hard tell issue above script i'm looking way debug issue is. strange thing function runs fine if change 1 of parameters (change networkid) supposed return data in same format.

can me point look?

thanks,

i've found link post entire sql script. can found here

check columns inserted decimal column, in example avgage , avgphqscore

i guessing there whitespace or special character original nvarchar column, brings error.

the easiest way check trying locate 2 columns mapped avgage , avgphqscore , run following statements check whether values nvarchar column implicitly converted decimal:

select original_nvarchar_column table isnumeric(original_nvarchar_column) = 0

if returns, means not value nvarchar column converted decimal, , result in issue got.





wiki

Comments

Post a Comment

Popular posts from this blog

python - Read npy file directly from S3 StreamingBody -

-
i have many .npy (numpy) , .json files saved in s3, need download , load these files. for json files downloading files , loading dictionary using following code: obj = s3.object(bucket, key) response = obj.get() body = response['body'].read().decode('utf-8') data = json.loads(body) that way avoid writing .json file disk , reading it. now want same .npy files, equivalent? currently downloading file, saving temp file on disk , loading np.load avoid if possible. following https://stackoverflow.com/a/28196540/3509999 : obj = s3.object(bucket, key) io.bytesio(obj.get()["body"].read()) f: # rewind file f.seek(0) arr = np.load(f) wiki
Read more

kotlin - Out-projected type in generic interface prohibits the use of metod with generic parameter -

-
i start using kotlin , defined interface this: interface aadapter<vh : recyclerview.viewholder> { fun oncreateaviewholder(parent: viewgroup): vh fun onbindaviewholder(v: vh, position: int) } and when try use on code: class klasa ( private val adapter: aadapter<*> ) { fun dosth(){ //... val vh = this.adapter.oncreateaviewholder(parent) //on below line error adapter.onbindaviewholder(v, position) //... } } i error out-projected type 'aadapter<*>' prohibits use of 'public abstract fun onbindaviewholder(v: t, position: int): unit i tied add "in" or "out" definition i'm confused. how allow it. replace aadapter<*> aadapter<recyclerview.viewholder> . class klasa ( private val adapter: aadapter<recyclerview.viewholder> ) { // ... because have declared aadapter has type parameter vh type subclass of recyclerview....
Read more

Asterisk AGI Python Script to Dialplan does not work -

-
i'm stuck project have check database of sip extension incoming caller id , dial sip extension. below receive when run script on asterisk cli. -- registered sip '9125' @ 192.168.1.102:50142 > saved useragent "zoiper rv2.8.40" peer 9125 [2017-08-22 13:53:33] notice[5095]: chan_sip.c:24558 handle_response_peerpoke: peer '9125' reachable. (17ms / 2000ms) == using sip rtp tos bits 184 == using sip rtp cos mark 5 -- executing [0112617769@from-trunk:1] answer("sip/obitrunk1-0000000d", "") in new stack > 0x1ca50d0 -- probation passed - setting rtp source address 192.168.1.98:16834 -- executing [0112617769@from-trunk:2] agi("sip/obitrunk1-0000000d", "test.py") in new stack -- launched agi script /var/lib/asterisk/agi-bin/test.py args: ['/var/lib/asterisk/agi-bin/test.py'] env line: agi_request: test.py env line: agi_channel: sip/obitrunk1-0000000d env line: agi_language: en env line: a...
Read more