?
Solved

SQL 2005 Error - Conversion failed when converting the varchar value '100.81' to data type int.

Posted on 2009-04-20
13
Medium Priority
?
1,120 Views
Last Modified: 2012-05-06
PLEASE NOTE, the SQL is handled dynamically by the server - alot of this query wont make sense or look right to you --- mostly in the WHERE clauses. Please, please, please dont ask my why NULL=NULL and other related questions about my WHERE clause statements ... again, its handled dynamically.

My issue has to do with this error:

Msg 245, Level 16, State 1, Line 195
Conversion failed when converting the varchar value '100.81' to data type int.

I tried:

CONVERT(VARCHAR(20), ISNULL(vd.DiagnosisCode , ''))AS Diagnosis

and I still get this issue. What am I missing?

SET NOCOUNT ON
 
CREATE TABLE #Summary 
	(
	DoctorID int,
	DoctorName varchar(60),
	FacilityID int,
	FacilityName varchar(60),
	CompanyID int,
	CompanyName varchar(60),
	FinancialClassMID int,
	FinancialClass varchar(60),
	DepartmentMID int,
	Department varchar(60),
	InsuranceCarriersID int,
	InsuranceCarrier varchar(60),
	InsAllocation numeric(10,2),
	PatAllocation numeric(10,2),
	PatBalance numeric(10,2),
	InsBalance numeric(10,2),
	InsPayment numeric(10,2),
	PatPayment numeric(10,2),
	InsAdjustment numeric(10,2),
	PatAdjustment numeric(10,2),
	Flag varchar(60),
	CPTCode varchar(10),
	ProceduresID int,
	TicketNumber varchar(35),
                Entry datetime,
	Resource varchar(60),
	ResourceID int,
	PatientVisitID int,
	DateofServiceFrom datetime,
	BatchId int
	)
 
-- Create a copy
SELECT * INTO #Summary1 FROM #Summary
 
SELECT #Summary.*, 0 AS Diagnosis INTO #Summary2 FROM #Summary
 
DECLARE @db varchar(255)
SELECT @db=db_name()
 
IF len('51')<2500 
BEGIN
 
-- Insert Charges 
IF 0 <> 1
BEGIN
	INSERT INTO #Summary1
	EXEC master..chcxp_CHC @db,3,NULL,NULL,'2','Entry','None',NULL,NULL
 
	INSERT INTO #Summary
	SELECT * FROM #summary1 
	WHERE
	--Filter on doctor
	(
	(NULL IS NOT NULL AND DoctorID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on CPTCode
	(
	(NULL IS NOT NULL AND ProceduresID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on facility
	(
	(NULL IS NOT NULL AND FacilityID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on company
	(
	(NULL IS NOT NULL AND CompanyID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on insurance carrier
	(
	(NULL IS NOT NULL AND InsuranceCarriersId IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on batches
	(
	(NULL IS NOT NULL AND BatchId IN (NULL)) OR
	(NULL IS NULL)
	)
	
	TRUNCATE TABLE #Summary1
END
 
-- Next Import Payments and Adjustments
INSERT INTO #Summary1
exec master..chcxp_CHC @db,2,NULL,NULL,'2','Entry','None',NULL,NULL,0
 
END -- FQHCPROCS.ITEMDATA.U check
 
INSERT INTO #Summary
SELECT * FROM #summary1 
WHERE  --Filter on doctor
	(
	(NULL IS NOT NULL AND DoctorID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on CPTCode
	(
	(NULL IS NOT NULL AND ProceduresID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on facility
	(
	(NULL IS NOT NULL AND FacilityID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on company
	(
	(NULL IS NOT NULL AND CompanyID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on insurance carrier
	(
	(NULL IS NOT NULL AND InsuranceCarriersId IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on batches
	(
	(NULL IS NOT NULL AND BatchId IN (NULL)) OR
	(NULL IS NULL)
	)
	AND 
	(
	(0 = 0) OR
	(0 = 1 AND DateofServiceFrom IS NULL)
	)
 
IF 2 = 1
BEGIN
-- Now group the items together for a total
INSERT INTO #Summary2
 
SELECT  
	s.DoctorId, 
	DoctorName,
	s.FacilityId, 
	FacilityName,
	s.CompanyId, 
	CompanyName,
	s.FinancialClassMID,
	FinancialClass,
	DepartmentMID,
	Department,
	InsuranceCarriersID,
	InsuranceCarrier,
	SUM(InsAllocation) AS InsAllocation,
	SUM(PatAllocation) AS PatAllocation,
	SUM(PatBalance) AS PatBalance,
	SUM(InsBalance) AS InsBalance,
	SUM(InsPayment) AS InsPayment,
	SUM(PatPayment) AS PatPayment,
	SUM(InsAdjustment) AS InsAdjustment,
	SUM(PatAdjustment) AS PatAdjustment,
	Flag,
	CPTCode,
	ProceduresID,
	s.TicketNumber,
                Entry,
	ISNULL(d.ListName,'None') AS Resource,
	ISNULL(pvr.ResourceID,0) AS ResourceID,
	s.PatientVisitID,
	DateofServiceFrom,
	BatchID, 
	ISNULL(vd.DiagnosisCode , '') AS Diagnosis 
	
 
FROM 	#Summary s
	JOIN PatientVisitResource pvr ON s.PatientVisitID = pvr.PatientVisitID
	JOIN DoctorFacility d ON pvr.ResourceID = d.DoctorFacilityID
	JOIN PatientVisit pv ON s.PatientVisitId = pv.PatientVisitId
	LEFT OUTER JOIN uvVisitDiagnosis vd ON pv.PatientVisitID = vd.PatientVisitID AND vd.ListOrder = 1
	
WHERE 
	(
	(NULL IS NOT NULL AND pvr.ResourceID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on diagnosis
	(
	(NULL IS NOT NULL AND vd.DiagnosisId IN (NULL)) OR
	(NULL IS NULL)
	)
 
END
ELSE
BEGIN
 
INSERT INTO #Summary2
 
SELECT  s.DoctorId, 
	DoctorName,
	s.FacilityId, 
	FacilityName,
	s.CompanyId, 
	CompanyName,
	s.FinancialClassMID,
	FinancialClass,
	DepartmentMID,
	Department,
	InsuranceCarriersID,
	InsuranceCarrier,
	InsAllocation,
	PatAllocation,
	PatBalance,
	InsBalance,
	InsPayment,
	PatPayment,
	InsAdjustment,
	PatAdjustment,
	Flag,
	CPTCode,
	ProceduresID,
	s.TicketNumber,
                Entry,
	Resource,
	ResourceID,
	s.PatientVisitID,
	DateofServiceFrom,
	BatchID, 
	ISNULL(vd.DiagnosisCode , '') AS Diagnosis 
 
FROM 	#Summary s
	JOIN PatientVisit pv ON s.PatientVisitId = pv.PatientVisitId
	JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
	LEFT OUTER JOIN uvVisitDiagnosis vd ON pv.PatientVisitID = vd.PatientVisitID AND vd.ListOrder = 1	
 
WHERE	---Filter on diagnosis
	(
	(NULL IS NOT NULL AND vd.DiagnosisId IN (NULL)) OR
	(NULL IS NULL)
	)
END
 
SELECT  DoctorId, 
	DoctorName,
	FacilityId, 
	FacilityName,
	CompanyId, 
	CompanyName,
	FinancialClassMID,
	FinancialClass,
	DepartmentMID,
	Department,
	InsuranceCarriersID,
	InsuranceCarrier,
	SUM(InsAllocation) AS InsAllocation,
	SUM(PatAllocation) AS PatAllocation,
	SUM(PatBalance) AS PatBalance,
	SUM(InsBalance) AS InsBalance,
	SUM(InsPayment) AS InsPayment,
	SUM(PatPayment) AS PatPayment,
	SUM(InsAdjustment) AS InsAdjustment,
	SUM(PatAdjustment) AS PatAdjustment,
	Flag,
	CPTCode,
	ProceduresID,
	TicketNumber,
                Entry,
	Resource,
	ResourceID,
	DateofServiceFrom,
	Diagnosis
 
FROM 	#Summary2
 
GROUP BY 
	DoctorId, 
	DoctorName,
	FacilityId, 
	FacilityName,
	CompanyId, 
	CompanyName,
	FinancialClassMID,
	FinancialClass,
	DepartmentMID,
	Department,
	InsuranceCarriersID,
	InsuranceCarrier,
	Flag,
	CPTCode,
	ProceduresID,
	TicketNumber,
                Entry,
	Resource,
	ResourceID,
	DateofServiceFrom,
	Diagnosis
 
ORDER BY 
	CompanyName,
	FacilityName,
	CPTCode,
	TicketNumber
 
DROP TABLE #Summary
DROP TABLE #Summary1
DROP TABLE #Summary2

Open in new window

0
Comment
Question by:Jeff S
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 15

Accepted Solution

by:
MNelson831 earned 800 total points
ID: 24185790
Somewhere you have '100.81' stored as varchar data and you are either trying to insert it into a field with datatype INT or else comparing it (via a link or where clause) to a field with Datatype INT, but '100.81' is not a valid INT value.
0
 
LVL 8

Assisted Solution

by:OBonio
OBonio earned 800 total points
ID: 24185833

select CAST(CAST('100.81' AS DECIMAL(10,2)) AS INT)

Open in new window

0
 
LVL 15

Expert Comment

by:MNelson831
ID: 24185843
Example:

Try running each of the statements below in query analyzer.  The first is a decimal value stored as a string.  The second looks for the decimal point in the string and truncates the string at the decimal point.  The third converts from a deciaml value rather than a string value:

Select convert(int,'100.81')
Select convert(int,Left('100.81',charindex('.','100.81')-1))
Select convert(int,100.81)
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 7

Author Comment

by:Jeff S
ID: 24185849
OBonio,
Where exactly am I placing "select CAST(CAST('100.81' AS DECIMAL(10,2)) AS INT)" ??
0
 
LVL 8

Expert Comment

by:OBonio
ID: 24186006
Well, it looks like you're trying to place vd.Diagnosis code into an INT somewhere, so if you use CAST(CAST(vd.DiagnosisCode AS DECIMAL(10,2)) AS INT) instead of the CONVERT(VARCHAR(20).......
0
 
LVL 7

Author Comment

by:Jeff S
ID: 24186015
MNelson831

Select convert(int,'100.81') returns:
 
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '100.81' to data type int.
Select convert(int,Left('100.81',charindex('.','100.81')-1)) returns:
100
Select convert(int,100.81) returns:
100
 
0
 
LVL 7

Author Comment

by:Jeff S
ID: 24186095
OBonio -
With your recommendation, I get this error now:

Msg 515, Level 16, State 2, Line 263
Cannot insert the value NULL into column 'Diagnosis', table 'tempdb.dbo.#Summary2___________________________________________________________________________________________________________00000000005B'; column does not allow nulls. INSERT fails.
The statement has been terminated.
 
0
 
LVL 15

Expert Comment

by:MNelson831
ID: 24186360
So Jeff.. what result are you wanting to have returned when you pass a string value of 100.81 to your Integer field?

If the answer is 100, then use the syntax from my second select statement.  The first statement demonstrates the error that you are receiving.  The other two demonstrate different ways to deal with the error:

Either truncate the number, pass it in as a decimal, or change the datatype for the receiving field to decimal.
0
 
LVL 8

Expert Comment

by:OBonio
ID: 24186410
You can use ISNULL to check for null values.
0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 400 total points
ID: 24188813
can you check this?
SET NOCOUNT ON
 
CREATE TABLE #Summary 
	(
	DoctorID int,
	DoctorName varchar(60),
	FacilityID int,
	FacilityName varchar(60),
	CompanyID int,
	CompanyName varchar(60),
	FinancialClassMID int,
	FinancialClass varchar(60),
	DepartmentMID int,
	Department varchar(60),
	InsuranceCarriersID int,
	InsuranceCarrier varchar(60),
	InsAllocation numeric(10,2),
	PatAllocation numeric(10,2),
	PatBalance numeric(10,2),
	InsBalance numeric(10,2),
	InsPayment numeric(10,2),
	PatPayment numeric(10,2),
	InsAdjustment numeric(10,2),
	PatAdjustment numeric(10,2),
	Flag varchar(60),
	CPTCode varchar(10),
	ProceduresID int,
	TicketNumber varchar(35),
                Entry datetime,
	Resource varchar(60),
	ResourceID int,
	PatientVisitID int,
	DateofServiceFrom datetime,
	BatchId int
	)
 
-- Create a copy
SELECT * INTO #Summary1 FROM #Summary
 
SELECT #Summary.*, 0 AS Diagnosis INTO #Summary2 FROM #Summary
 
DECLARE @db varchar(255)
SELECT @db=db_name()
 
IF len('51')<2500 
BEGIN
 
-- Insert Charges 
IF 0 <> 1
BEGIN
	INSERT INTO #Summary1
	EXEC master..chcxp_CHC @db,3,NULL,NULL,'2','Entry','None',NULL,NULL
 
	INSERT INTO #Summary
	SELECT * FROM #summary1 
	WHERE
	--Filter on doctor
	(
	(NULL IS NOT NULL AND DoctorID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on CPTCode
	(
	(NULL IS NOT NULL AND ProceduresID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on facility
	(
	(NULL IS NOT NULL AND FacilityID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on company
	(
	(NULL IS NOT NULL AND CompanyID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on insurance carrier
	(
	(NULL IS NOT NULL AND InsuranceCarriersId IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on batches
	(
	(NULL IS NOT NULL AND BatchId IN (NULL)) OR
	(NULL IS NULL)
	)
	
	TRUNCATE TABLE #Summary1
END
 
-- Next Import Payments and Adjustments
INSERT INTO #Summary1
exec master..chcxp_CHC @db,2,NULL,NULL,'2','Entry','None',NULL,NULL,0
 
END -- FQHCPROCS.ITEMDATA.U check
 
INSERT INTO #Summary
SELECT * FROM #summary1 
WHERE  --Filter on doctor
	(
	(NULL IS NOT NULL AND DoctorID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on CPTCode
	(
	(NULL IS NOT NULL AND ProceduresID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on facility
	(
	(NULL IS NOT NULL AND FacilityID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on company
	(
	(NULL IS NOT NULL AND CompanyID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on insurance carrier
	(
	(NULL IS NOT NULL AND InsuranceCarriersId IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on batches
	(
	(NULL IS NOT NULL AND BatchId IN (NULL)) OR
	(NULL IS NULL)
	)
	AND 
	(
	(0 = 0) OR
	(0 = 1 AND DateofServiceFrom IS NULL)
	)
 
IF 2 = 1
BEGIN
-- Now group the items together for a total
INSERT INTO #Summary2
 
SELECT  
	s.DoctorId, 
	DoctorName,
	s.FacilityId, 
	FacilityName,
	s.CompanyId, 
	CompanyName,
	s.FinancialClassMID,
	FinancialClass,
	DepartmentMID,
	Department,
	InsuranceCarriersID,
	InsuranceCarrier,
	SUM(InsAllocation) AS InsAllocation,
	SUM(PatAllocation) AS PatAllocation,
	SUM(PatBalance) AS PatBalance,
	SUM(InsBalance) AS InsBalance,
	SUM(InsPayment) AS InsPayment,
	SUM(PatPayment) AS PatPayment,
	SUM(InsAdjustment) AS InsAdjustment,
	SUM(PatAdjustment) AS PatAdjustment,
	Flag,
	CPTCode,
	ProceduresID,
	s.TicketNumber,
                Entry,
	ISNULL(d.ListName,'None') AS Resource,
	ISNULL(pvr.ResourceID,0) AS ResourceID,
	s.PatientVisitID,
	DateofServiceFrom,
	BatchID, 
	ISNULL(CAST(CAST(vd.DiagnosisCode AS DECIMAL(10,2)) AS INT) , '') AS Diagnosis 
	
 
FROM 	#Summary s
	JOIN PatientVisitResource pvr ON s.PatientVisitID = pvr.PatientVisitID
	JOIN DoctorFacility d ON pvr.ResourceID = d.DoctorFacilityID
	JOIN PatientVisit pv ON s.PatientVisitId = pv.PatientVisitId
	LEFT OUTER JOIN uvVisitDiagnosis vd ON pv.PatientVisitID = vd.PatientVisitID AND vd.ListOrder = 1
	
WHERE 
	(
	(NULL IS NOT NULL AND pvr.ResourceID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on diagnosis
	(
	(NULL IS NOT NULL AND vd.DiagnosisId IN (NULL)) OR
	(NULL IS NULL)
	)
 
END
ELSE
BEGIN
 
INSERT INTO #Summary2
 
SELECT  s.DoctorId, 
	DoctorName,
	s.FacilityId, 
	FacilityName,
	s.CompanyId, 
	CompanyName,
	s.FinancialClassMID,
	FinancialClass,
	DepartmentMID,
	Department,
	InsuranceCarriersID,
	InsuranceCarrier,
	InsAllocation,
	PatAllocation,
	PatBalance,
	InsBalance,
	InsPayment,
	PatPayment,
	InsAdjustment,
	PatAdjustment,
	Flag,
	CPTCode,
	ProceduresID,
	s.TicketNumber,
                Entry,
	Resource,
	ResourceID,
	s.PatientVisitID,
	DateofServiceFrom,
	BatchID, 
	ISNULL(CAST(CAST(vd.DiagnosisCode AS DECIMAL(10,2)) AS INT) , '') AS Diagnosis 
 
FROM 	#Summary s
	JOIN PatientVisit pv ON s.PatientVisitId = pv.PatientVisitId
	JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
	LEFT OUTER JOIN uvVisitDiagnosis vd ON pv.PatientVisitID = vd.PatientVisitID AND vd.ListOrder = 1	
 
WHERE	---Filter on diagnosis
	(
	(NULL IS NOT NULL AND vd.DiagnosisId IN (NULL)) OR
	(NULL IS NULL)
	)
END
 
SELECT  DoctorId, 
	DoctorName,
	FacilityId, 
	FacilityName,
	CompanyId, 
	CompanyName,
	FinancialClassMID,
	FinancialClass,
	DepartmentMID,
	Department,
	InsuranceCarriersID,
	InsuranceCarrier,
	SUM(InsAllocation) AS InsAllocation,
	SUM(PatAllocation) AS PatAllocation,
	SUM(PatBalance) AS PatBalance,
	SUM(InsBalance) AS InsBalance,
	SUM(InsPayment) AS InsPayment,
	SUM(PatPayment) AS PatPayment,
	SUM(InsAdjustment) AS InsAdjustment,
	SUM(PatAdjustment) AS PatAdjustment,
	Flag,
	CPTCode,
	ProceduresID,
	TicketNumber,
                Entry,
	Resource,
	ResourceID,
	DateofServiceFrom,
	Diagnosis
 
FROM 	#Summary2
 
GROUP BY 
	DoctorId, 
	DoctorName,
	FacilityId, 
	FacilityName,
	CompanyId, 
	CompanyName,
	FinancialClassMID,
	FinancialClass,
	DepartmentMID,
	Department,
	InsuranceCarriersID,
	InsuranceCarrier,
	Flag,
	CPTCode,
	ProceduresID,
	TicketNumber,
                Entry,
	Resource,
	ResourceID,
	DateofServiceFrom,
	Diagnosis
 
ORDER BY 
	CompanyName,
	FacilityName,
	CPTCode,
	TicketNumber
 
DROP TABLE #Summary
DROP TABLE #Summary1
DROP TABLE #Summary2
 

Open in new window

0
 
LVL 7

Author Comment

by:Jeff S
ID: 24195895
Sharath 123  -

Msg 8114, Level 16, State 5, Line 195
Error converting data type varchar to numeric.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24196701
could you run this and check how many non-numeric DiagnosisCodes you have?
select DiagnosisCode from uvVisitDiagnosis where ISNUMERIC(DiagnosisCode) = 0

Open in new window

0
 
LVL 7

Author Closing Comment

by:Jeff S
ID: 31572294
Split pts to be fair. Ill need to rethink this all. Thanks for trying.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

862 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