Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1997
  • Last Modified:

Error converting data type varchar to numeric

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
Star79
Asked:
Star79
  • 2
1 Solution
 
alpmoonCommented:
I am guessing that one of these columns in KeyIdentifiers table is numeric (maybe more than one):

GPIDrugGroup
GPIDrugClass
GPIDrugSubClass
GPIDrugNameCd
0
 
Star79Author Commented:
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
 
alpmoonCommented:
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now