Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Error converting data type varchar to numeric

Posted on 2013-07-01
3
Medium Priority
?
1,976 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

688 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