Solved

Error converting data type varchar to numeric

Posted on 2013-07-01
3
1,898 Views
Last Modified: 2013-07-02
Hello All,
I have the below code;
USE [FwReports]
GO

/****** Object:  StoredProcedure [dbo].[VirtuaEMR]    Script Date: 07/01/2013 15:18:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





alter PROCEDURE [dbo].[VirtuaEMR](
 @FacID varchar(6),
 @MStart datetime,
 @MEnd datetime
) as

declare @t table (
 ResidentNumber varchar(9),
 Date varchar(8),
 Quantity numeric(12,2),
 UnitPrice  numeric(12,2),
 TransactionID VARCHAR(16),OTC INT,IVTYPE VARCHAR(1),CCType varchar(1), NDC varchar(11)

)

set nocount on
declare @f varchar(6), @PatID varchar(11),@FirstDay datetime, @LastDay datetime
declare @dispensedt datetime, @rono int, @rxno int, @ttlprice money, @creditamt money,@prid integer,@pstartdt 

datetime,@penddt datetime,@prrate numeric(9,4),@pdays integer,@perdiemratedesc varchar(50)
SET ANSI_WARNINGS OFF

 
 declare @mE table (
 comparevalue varchar(20)
) 
insert into @mE(comparevalue)
select distinct GPIDrugGroup+GPIDrugClass+GPIDrugSubClass+GPIDrugNameCd  from PerDiemFormulary
INNER JOIN PerDiemRates ON PerDiemFormulary.PerDiemRateId = PerDiemRates.PerDiemRateID
WHERE PerDiemFormulary.FacId=@FacID AND PerDiemFormularyType ='E'AND
   CDID='*' and Product='*' 


declare @nonformularyE table (
 comparevalue varchar(20)
) 
insert into @nonformularyE(comparevalue)
select distinct GPIDrugGroup+GPIDrugClass+GPIDrugSubClass+GPIDrugNameCd+Product  from PerDiemFormulary 
INNER JOIN PerDiemRates ON PerDiemFormulary.PerDiemRateId = PerDiemRates.PerDiemRateID

where PerDiemFormulary.FacID=@FacID and   PerDiemFormularyType ='E' and CDID='*' and Product <> '*'

declare c1 cursor for
 select pde.facid, pde.patid,pde.perdiemrateid, pdr.PerDiemRate,
			  case 
				when StartDt > @mstart then StartDt 
				else @mstart 
				end as starting,
			 case 
  when EndDt is null then @mend
  when EndDt > @mend then @mend
  else EndDt
 end as ending,(DATEDIFF("day", 
	  CASE
	  WHEN startdt < @MStart THEN @MStart 
	  ELSE startdt
	  END,
	   CASE
	   when EndDt is null then @mend
	  WHEN EndDt > @mend then @mend
	  ELSE EndDt
	  END) 
	  + 1
	) as pdays,pde.startdt as pstartdt,pde.enddt as penddt,pdr.perdiemratedesc
from PatientPerDiemEligibility  pde inner join PerDiemRates pdr on pde.PerDiemRateId = pdr.PerDiemRateID
where 
 StartDt <= @mend and
 (EndDt is null or EndDt >= @mstart) and
 pde.FacID =@FacID

open c1

fetch next from c1 into @f, @patid,@prid,@prrate,@firstday, @lastday,@pdays,@pstartdt,@penddt,@perdiemratedesc
while @@fetch_status = 0 begin
 insert into @t (ResidentNumber,Date,Quantity,UnitPrice,TransactionID,OTC,IVTYPE,CCType,NDC)
				  
				select MedRecNo,convert(varchar, DispenseDt, 1)Date ,HRXS.Qty,(ttlprice - coalesce(creditamt, 0))unitprice,'25950','',
				hrxs.IVType,ISNULL(Compounds.CCType,''),case when len(ltrim(coalesce(hrxs.ccid,''))) = 0 then ndc else 

				hrxs.ccid end
				 from hrxs
				 left join Patients p on hrxs.PatID = p.PatID and hrxs.FacID = p.FacID
				 left join Compounds on Compounds.CCID = hrxs.CCID 

				 where hrxs.facid = @f and hrxs.patid = @patid and udrx = 0 and MOP='FACI' 
				 AND
				  dateadd(day, dayssupply -1, dispensedt) >= @firstday and
				  dispensedt <= @lastday and
				  dispensedt between @MStart and @MEnd and 
				  transtype in ('p','b','q','u','r')
				  --group by hrxs.PatID,DispenseDt,hrxs.Qty,TtlPrice,hrxs.IVType,Compounds.CCType,ndc,hrxs.CCID				  
 fetch next from c1 into @f, @patid,@prid,@prrate,@firstday, @lastday,@pdays,@pstartdt,@penddt,@perdiemratedesc
end
close c1
deallocate c1
UPDATE @t SET OTC = 0
UPDATE @t SET OTC = 1 WHERE NDC IN
   (SELECT NDC FROM Drug..KeyIdentifiers WHERE RxOtcInd IN ('O','P'))
UPDATE @t SET OTC = 2 WHERE IVTYPE ='1'
UPDATE @t SET OTC = 2 WHERE CCType ='I'
UPDATE @t SET OTC = 3 WHERE NDC IN
   (select perdiemitemno from perdiemrates where facid =  @FacID)   

DELETE FROM @t WHERE NDC IN(SELECT CDID FROM PerDiemFormulary WHERE FacID=@FacID)
delete from @t where NDC in
( select NDC from KeyIdentifiers where GPIDrugGroup+GPIDrugClass+GPIDrugSubClass+GPIDrugNameCd
 in (select * from @mE))
  delete from @t where NDC IN
 ( select NDC from KeyIdentifiers where 

GPIDrugGroup+GPIDrugClass+GPIDrugSubClass+GPIDrugNameCd+RIGHT(KeyIdentifiers.GPI, 4)
 in (select * from @nonformularyE))
--select * from @t (CONVERT(varchar,b.BillAmt,0)
select REPLICATE(' ',26)+ 
REPLICATE(' ',9 - LEN(residentnumber)) + 
residentnumber 
+ REPLICATE(' ',29)+ 
TransactionID+ REPLICATE(' ',16-LEN(TransactionID)) + REPLICATE(' ',30) + 
 convert(varchar,Quantity) + REPLICATE(' ',12-LEN(Quantity))+ 
 sum(UnitPrice) + REPLICATE(' ',12-LEN (convert(varchar(12),UNITPRICE,0))) + 
 REPLICATE(' ',13)
+ DATE + REPLICATE(' ',8-LEN(DATE))+ 
 REPLICATE(' ',37) AS RECORD
 from @t group by ResidentNumber,TransactionID,Quantity,UnitPrice,date
 




GO

Open in new window


I get the below error when I execute:
Msg 8114, Level 16, State 5, Procedure VirtuaEMR, Line 119
Error converting data type varchar to numeric.


Please help as this is critical and my brain is all dead.
0
Comment
Question by:Star79
  • 2
3 Comments
 
LVL 13

Expert Comment

by:alpmoon
ID: 39291561
I am guessing that one of these columns in KeyIdentifiers table is numeric (maybe more than one):

GPIDrugGroup
GPIDrugClass
GPIDrugSubClass
GPIDrugNameCd
0
 

Author Comment

by:Star79
ID: 39291604
All those columd are varchar.The error is occuring only in the last part where I use the replicate function on the select statement when I do a sum(unitprice)
0
 
LVL 13

Accepted Solution

by:
alpmoon earned 500 total points
ID: 39291914
You are right, I had looked at wrong line.

The problem is Quantity, it is not a string. You need to convert it to varchar first. Also sum(UnitPrice) and Date need to be converted too.

REPLICATE(' ',12-LEN(ltrim(str(Quantity))))+
 ltrim(str(sum(UnitPrice))) + REPLICATE(' ',12-LEN (ltrim(str(UNITPRICE))))
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question