Solved

Error converting data type varchar to numeric

Posted on 2013-07-01
3
1,865 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now